Re: query to find gaps in sequential indexes causes CPU spikes

[Rick James]

> thread_cache_size could be increased to maybe 20, it's now 0.
Yes, non-zero will improve how fast a new connection can get started.

> uptime is 879166 and the opened_files is 13533
So, you are not opening files very fast. No need to change table_open_cache.

> set the table_open_cache (currently 400) to # of concurrent connection * max # joins in any query
A pessimistic formula, especially since
> no other connections going on

> Is there an optimum size for a buffer pool?
70% of available RAM, which is about what you have on your 34GB box (but not for 15GB):
> innodb_buffer_pool_size;27525120000

> max_connections is currently 2500 to handle periods of peak load
If thousands of connections are trying to do work, they will stumble over each other, and none of them will be efficient.

> Are the issues you're referring to, query optimization and/or performance tuning?
Mostly query optimization, so that each connection gets finished with its task(s) faster.

> avg row length 3065
Sounds like you have some bulky columns in the table. How many columns? Are some of them BLOB or TEXT? To you access all the columns all the time? Or can we split the table "vertically"? That is, move some of the bulky columns off into another table, so the lengthy queries are not stumbling over the unnecessary columns.

I would like to see SHOW CREATE TABLE CustomerData.

Did you try the NOT EXISTS version?

How many rows have column1 = 10 ?
The explain suggest that there may be 1250268.
If so, that is a lot of data to scan over. Are all the connections doing that same query? Or similar queries? How frequently does the data change? Can the results be cached for some period of time -- that is, would that be "good enough"?

Does part of the table stay constant? That is, are all the new rows added at the "end" of the table? I am fishing for whether the information can be pre-calculated and saved, with updates periodically as the data comes in.