Re: query to find gaps in sequential indexes causes CPU spikes
Posted by: Chris Nevin
Date: November 28, 2011 04:58PM
Thanks for your continued responses, it's really helpful.
The storage engine is innodb and there is a compound index on column1, column0.
Table Status - 15924427 rows, select count actually returns 38025098, avg row length 3065, data length 48811212800, index length 1577058304, data free 629145600
I also checked the cache variables:
We were running with 15GB when the cpu spiked over 50%. We recently upgraded it to 34 GB. I just re-ran the queries again and the cpu usage hit 36%. This is not a high traffic time for us.
I checked out the link you posted. The query cache is on, but the size is 0, so from the mysql manual, the query is off. The thread_cache_size could be increased to maybe 20, it's now 0. I find the table_open_cache and its effect to be a little confusing. The uptime is 879166 and the opened_files is 13533. The link suggests that the table_open_cache could be decreased. What's confusing is the mysql manual says to set the table_open_cache (currently 400) to # of concurrent connection * max # joins in any query. This suggests that the table_open_cache should be increased. I have a snapshot the DB that I'm working with, so no other connections going on (in fact just 2), and without issuing any sql commands, I the opened_files variable increased.
The server is also configured with a single large buffer pool. Is there an optimum size for a buffer pool? Also, that post says if the max_connections is greater than 1000, there are other issues to address. Our max_connections is currently 2500 to handle periods of peak load. Are the issues you're referring to, query optimization and/or performance tuning?
Thanks again for the help.