1

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?

Improve this question
3
  • Possibly related: dba.stackexchange.com/questions/75439/…. You can take a look at what has been done for diagnosis so you could use this in your own setup even if the premise of full-text search indexes does not apply to your setup.
    – syneticon-dj
    Oct 2, 2014 at 18:58
  • It just occurred to me that we recently role swapped, and the slave which became master is running an older version of MySQL and InnoDB. It's running Percona Server (GPL), Release rel24.1, Revision 217, InnoDB version 1.1.8). Could this have anything to do with the performance degradation?
    – Eric R.
    Oct 2, 2014 at 19:39
  • The older release might have bugs and performance-related issues which have been fixed in the newer release and thus were not visible prior to the role-swap. As replication is asymmetric and slave-initiated, an older slave is not going to affect the master performance-wise.
    – syneticon-dj
    Oct 2, 2014 at 21:16

1 Answer 1

Reset to default
0

During that period can you get a list of the sql processes running?

show full processlist;

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, free for 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:

tcpdump -i any -s0 -A -n -nn port 3306

or

tcpdump -i any -s0 -A -n -nn port 3306 -w /tmp/mysql.pcap

to output to a file and then view with a UI tool like Wireshark. Then you can see the actual queries running.

Improve this answer
1
  • Yes I can. The problem is, it triggers quite randomly in the evening. I'm currently setting up pt-stalk to monitor for the degradation conditions.
    – Eric R.
    Oct 2, 2014 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.