We have a site with MySQL DB running properly for more than 2 years.
However, 2 weeks ago we noticed a sudden spike in load. We started the MySQL service and load normalized. We assumed the issue was one off and forgot about it.
But for last 1 week are seeing the MySQL load hitting high almost on a daily basis. Sometimes just a few minutes after restart. It has rendered the site almost unussable.
A check on the log does not show any query that would cause a spike. Majority of the queries (about 90%) take less than 10ms. A few (about 9%) takes between 10 and 20ms. There are still some that take between 20 and 30ms. But this are are negligible, accounting for about 1%. Apart from 2 long-running queries that runs when the app starts, I haven't seen any regular query that takes more than 30ms.
$ uname -a Linux 3.10.0-229.7.2.el7.x86_64 #1 SMP Tue Jun 23 22:06:11 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux $ mysql --version mysql Ver 14.14 Distrib 5.6.27, for Linux (x86_64) using EditLine wrapper
Any reason why MySQL would suddenly become unstable?
If, for some reason (s), we have just hit limitation for MySQL (probably an InnoDB issue) would it help if we moved to MariaDB or Persona Server (that uses XtraDB)?
Edit 1: Additional information
Something I have noticed is at the time when performance degrades there are several
SELECT queries. They seem not any big resource intensive queries (the longest takes about 4ms) but they occur in bursts, and probably this could be the problem.
The `'burst queries' looks valid, and not as a result of DOS attacks or XSS.
**Edit 2: MySQL conf*
$ cat /etc/my.cnf [mysqld] # Settings user and group are ignored when systemd is used. # If you need to run mysqld under different user or group, # customize your systemd unit file for mysqld datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock tmpdir=/var/mysqltmp # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 open_files_limit=50000 query_cache_size=2048M max_connections=100 max_user_connections=25 wait_timeout=20 tmp_table_size=6144M max_heap_table_size=6144M thread_cache_size=64 key_buffer_size=8192M max_allowed_packet=268435456 #table_cache=32768 table_open_cache=32768 table_definition_cache=32768 delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts myisam_sort_buffer_size=512M # can be increased per sessions if needed for alter tables (indexes, repair) query_cache_limit=32M # leave at default unless there is a good reason query_cache_type=1 join_buffer=32M # leave at default unless there is a good reason sort_buffer_size=32M # leave at default unless there is a good reason read_rnd_buffer_size=16M # leave at default unless there is a good reason read_buffer_size=32M # leave at default unless there is a good reason collation_server=utf8_unicode_ci character_set_server=utf8 general_log=0 log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10; sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES innodb_file_per_table=1 innodb_buffer_pool_size=34406M innodb_additional_mem_pool_size=7424M innodb_log_buffer_size=7424M innodb_thread_concurrency=8 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores innodb_change_buffering=all innodb_flush_log_at_trx_commit=0 innodb_support_xa=0 innodb_doublewrite = 0 default-storage-engine=MyISAM [mysqld_safe] #malloc-lib=/usr/lib64/libtcmalloc_minimal.so.4 malloc-lib=/usr/lib64/libtcmalloc_minimal.so.4 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
my.cnf shows default-storage-engine=MyISAM but all tables I have created are InnoDB.
How much RAM? 48GB
Using InnoDB? Yes
How many cores? 8