Saturday, 23 May 2015

List of background operations performed by MySQL InnoDB Main thread.

The main InnoDB thread is a special thread that just runs in a loop to do various kinds of work. You can get the state of main thread from the output of show engine InnoDB status, under “ROW OPERATIONS” section.

As per MySQL 5.6.24 source code the following are the list of background operations performed by InnoDB main thread.

checking free log space
doing background drop tables
doing insert buffer merge
enforcing dict cache limit
flushing log
making checkpoint
waiting for server activity

Let us try to understand more about each of these states and what will happen internally in the background during each of these states.

checking free log space : During this state MySQL checks the redo logs usage and makes sure that redo logs having enough space to flush the committed transactions.

doing background drop tables : This state is still not clear but, from the source code we can see the following comment.

/* ALTER TABLE in MySQL requires on Unix that the table handler can drop tables lazily after there no longer are SELECT queries to them. */

If you drop a table where there still queries are running, then InnoDB delays the drop table until the queries have ended, and only after that drops the table.
When dropping the table, InnoDB has to remove also any locks there on the table.

So my understanding is that during this state, it is performing some background operations related to either drop table or alter table. I will have to dig more on this and probable write another following up post on this, Any help on this is appreciable.

More Info : 1)

doing insert buffer merge : First let us understand what is insert buffer and which kind of data it consists. Actually when ever inserts happens to the table which is having secondary indexes created, those inserts data first will be stored in the insert buffer, since these are new rows and has to be stored in the disk in the indexed order because index has been created on one of the columns.

Let us consider an example, suppose if we have data in the indexed column A,B,C,F,G,H etc. and all this data is present in the disk. Now a new record with indexed column value “D” has been inserted, initially this row will be inserted into the insert buffer in the memory, and later when ever the data A,B,C etc.. has been accessed, that time these newly inserted data will be merged with the actual data, and will be placed together in the same page or sequence of pages based on the indexed column order and changes will be flush to the disk later accordingly.

Here the process of merging the pages data from insert buffer and the actual pages in the buffer pool (retrieved from the disk) to form the new pages with the indexed column order is called the insert buffer merge.

enforcing dict cache limit : During this state MySQL will check for the usage of dictionary cache memory usage and cleans up the dictionary cache if required.
Keeping track / calculation of dictionary cache size will be depends on number of tables and file handlers opened and what is the value of table_cache and other parameters.
For more information go through

flushing log : During this state MySQL flushes the log file content from the log buffer to the log files. you can check the following comment from the source code.

/* The master thread is tasked to ensure that flush of log file happens once every second in the background. This is to ensure that not more than one second of trxs are lost in case of crash when InnoDB_flush_logs_at_trx_commit != 1 */

making checkpoint : Checkpointing is the concept associated with the flushing the in memory changes to the disk which includes flushing dirty pages from the buffer pool to disk. each time when a flush is happened it has to keep track of all the changes flushed and to which LSN Log sequence number the changes have been flushed and repeat the same process to flush other non committed changes, this process is called checkpoint.
  For more information go through

sleeping :  During this state MySQL is ideal and nothing much is happening in the background.

suspending : This is the state before sleeping, during this state nothing will happen in the background.

waiting for server activity : I didn’t understand much from the source code about this state, but the following is the comment from “Miguel Angel Nieto” on this.

When master thread has no tasks to do, it enters the "sleeping" mode. On that mode it waits one second and then checks again if there is something to do. Then, go back to "sleeping". I guess from that code that you won't see "waiting for server activity". from