Skip navigation links

MySQL Forums :: Performance :: query to find gaps in sequential indexes causes CPU spikes


Advanced Search

Re: query to find gaps in sequential indexes causes CPU spikes
Posted by: Chris Nevin ()
Date: November 28, 2011 04:58PM

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.

Options: ReplyQuote


Subject Views Written By Posted
query to find gaps in sequential indexes causes CPU spikes 1401 Chris Nevin 11/22/2011 12:50PM
Re: query to find gaps in sequential indexes causes CPU spikes 636 Rick James 11/23/2011 11:01PM
Re: query to find gaps in sequential indexes causes CPU spikes 701 Chris Nevin 11/25/2011 05:19PM
Re: query to find gaps in sequential indexes causes CPU spikes 635 Rick James 11/26/2011 06:02PM
Re: query to find gaps in sequential indexes causes CPU spikes 815 Chris Nevin 11/28/2011 04:58PM
Re: query to find gaps in sequential indexes causes CPU spikes 727 Rick James 11/29/2011 09:23PM
Re: query to find gaps in sequential indexes causes CPU spikes 475 Chris Nevin 11/30/2011 10:41AM
Re: query to find gaps in sequential indexes causes CPU spikes 734 Rick James 12/01/2011 10:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.