For the past couple of days, Ive been observing periods of huge performance degradation. CPU usage on our master MySQL server (Percona Server (GPL), Release rel24.1, Revision 217, InnoDB version 1.1.8) will shoot up to over 70% and one group of queries will start becoming very slow. This will last for 4-5 minutes and then disappear, only to come back the next night with a new set of queries becoming slow. The following stats were captured during a recent occurrence of this happening:
Com_delete = 5.86666666666667 / sec
Com_create_table = 0 / sec
Threads_created = 1.61666666666667 / sec
Key_read_requests = 78.7666666666667 / sec
Bytes_received = 4766588.83333333 / sec
Qcache_not_cached = 0 / sec
Created_tmp_disk_tables = 224.45 / sec
Com_replace = 0 / sec
Innodb_buffer_pool_write_requests = 1537.2 / sec
Created_tmp_tables = 590.916666666667 / sec
Com_update = 82.0666666666667 / sec
Com_stmt_execute = 1053.75 / sec
Key_writes = 0 / sec
Innodb_row_lock_time = 0 / sec
Com_select = 38798.35 / sec
Connections = 3.55 / sec
Select_range = 1492.38333333333 / sec
Innodb_data_read = 24389768.5333333 / sec
Com_insert = 61.8666666666667 / sec
Table_locks_waited = 0 / sec
Bytes_sent = 66554017.7666667 / sec
Key_reads = 0 / sec
Sort_rows = 25265.05 / sec
Select_full_join = 0 / sec
Select_scan = 182.583333333333 / sec
Slow_queries = 1.06666666666667 / sec
Innodb_data_written = 3407197.86666667 / sec
Key_write_requests = 17.05 / sec
Innodb_buffer_pool_read_requests = 3687732.7 / sec
Questions = 41779.7166666667 / sec
Qcache_hits = 0 / sec
Threads_connected =
Threads_running =
Innodb_row_lock_time_avg =
Qcache_queries_in_cache =
Qcache_free_memory =
We can compare this to right now:
Com_delete = 3.88333333333333 / sec
Com_create_table = 0 / sec
Threads_created = 0 / sec
Key_read_requests = 1 / sec
Bytes_received = 3421724.13333333 / sec
Qcache_not_cached = 0 / sec
Created_tmp_disk_tables = 159.45 / sec
Com_replace = 0 / sec
Innodb_buffer_pool_write_requests = 989.65 / sec
Created_tmp_tables = 428.3 / sec
Com_update = 50.4166666666667 / sec
Com_stmt_execute = 728.4 / sec
Key_writes = 0 / sec
Innodb_row_lock_time = 0 / sec
Com_select = 28194.15 / sec
Connections = 0.866666666666667 / sec
Select_range = 1107.58333333333 / sec
Innodb_data_read = 19804706.1333333 / sec
Com_insert = 38.95 / sec
Table_locks_waited = 0 / sec
Bytes_sent = 48218433.8666667 / sec
Key_reads = 0 / sec
Sort_rows = 46135.2833333333 / sec
Select_full_join = 0 / sec
Select_scan = 125.633333333333 / sec
Slow_queries = 0.533333333333333 / sec
Innodb_data_written = 3794304 / sec
Key_write_requests = 0.6 / sec
Innodb_buffer_pool_read_requests = 2814013.88333333 / sec
Questions = 30203.2833333333 / sec
Qcache_hits = 0 / sec
Threads_connected =
Threads_running =
Innodb_row_lock_time_avg =
Qcache_queries_in_cache =
Qcache_free_memory =
Aside from the fact that the stats all generally seem higher, could there be some clue here as to what's going on? Besides running this stats script, are there are any other tools I can use to monitor what might be causing this instability?
Best Answer
During that period can you get a list of the sql processes running?
Is this a server where you don't have root access to the actual OS like in AWS?
If you do have root access and this is something you can research it might be unrelated to MySQL, i.e., some other process on the server is affecting performance. This might be out of scope of the question but use
iostat,top,freefor utilities to use in that regard.For MySQL specific strategies you can also enable the query log which will log every query. Careful with this because it'll fill up disk very quickly depending on query traffic. Also requires a restart. If you can't do that then you probably want to watch traffic on the wire:
or
to output to a file and then view with a UI tool like Wireshark. Then you can see the actual queries running.