Re: Selects grinding to a halt with 16 inserts/min
Posted by:
Rick James
Date: December 03, 2014 11:29AM
There is no single answer to your question, so let's dig deeper. Please provide
SHOW VARIABLES;
SHOW GLOBAL STATUS;
(It may take two postings, or use of a pastebin site.)
How much RAM?
I will do an analysis of those to see if some settings could/should be changed, plus get a feel for various things.
Meanwhile, set long_query_time=1 and make sure the slowlog is turned on. After a day or two, run pt_query_digest to summarize the slowlog. This will provide some information about the types of queries that are slowed down, leading to investigation of the details of how they fight with each other over resources. Show us the first few queries that it lists, together with the SHOWs and EXPLAINs that it recommends.
> performance of the selects slowly degrades and consumes more CPU
The slowlog will probably point out which queries this applies to. If you happen to know of one, please show us the query, together with
EXPLAIN SELECT ...
For each table in the SELECT:
SHOW TABLE STATUS LIKE 'tablename' -- to see the size
SHOW CREATE TABLE 'tablename' -- need to see the indexes, datatypes, etc.
> the table never gets a chance to rebuild it's index
Indexes are incrementally updated; they are not "rebuilt". However, index updates are gathered up and applied later. Once the buffer_pool is full of pending updates ("dirty" blocks), there could be a lot of writing. Do you know if you are IO-bound? If so, is the machine IO-bound only later in the 8 hours?
Do you have single-column indexes on 'flags'? Generally useless.
Do you have only single-column indexes (no "compound" indexes)? Generally less efficient.
MySQL almost never uses two indexes in a query.
Seeing the SELECTs, I can advise on the optimal INDEX to have.
> 16 inserts/min
That is a very low insert rate. But you have pointed out that it is enough to cause a lot of trouble. So, please show us a typical INSERT.
Even if you have 100 indexes on the table, I can't imagine how 16/min would cause this much trouble. Still, decreasing the number of indexes could help some.