Re: query to find gaps in sequential indexes causes CPU spikes
Rick -
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
bulk_insert_buffer_size;8388608
innodb_buffer_pool_instances;1
innodb_buffer_pool_size;27525120000
innodb_change_buffering;all
innodb_log_buffer_size;8388608
join_buffer_size;131072
key_buffer_size;16777216
myisam_sort_buffer_size;8388608
net_buffer_length;16384
preload_buffer_size;32768
read_buffer_size;262144
read_rnd_buffer_size;524288
sort_buffer_size;2097152
sql_buffer_result;OFF
I also checked the cache variables:
binlog_cache_size,32768
binlog_stmt_cache_size,32768
have_query_cache,YES
key_cache_age_threshold,300
key_cache_block_size,1024
key_cache_division_limit,100
max_binlog_cache_size,18446744073709547520
max_binlog_stmt_cache_size,18446744073709547520
query_cache_limit,1048576
query_cache_min_res_unit,4096
query_cache_size,0
query_cache_type,ON
query_cache_wlock_invalidate,OFF
table_definition_cache,400
table_open_cache,400
thread_cache_size,0
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.