The main work of the InnoDB storage engine is done in a separate background thread, master thread.
The Master Thread before the 1.InnoDB 1.0.x version
The Master thread has the highest thread priority level. The interior consists of multiple loops: the main loop (loop), the background loop (Backgroup loop), the refresh loop (flush loop), and the pause loop (suspend loop). The Master thread switches between the 4 states according to the state of the database running. Loop is called the main loop, because most operations are in this loop, where there are two major operations: operations per second and operations every 10 seconds. The pseudo code is as follows:
As you can see, the loop loop is implemented by thread sleep, which means that the so-called one or 10-second operations per second are imprecise. In high-load situations there may be delays, which can only be said around this frequency. Of course InnoDB source code also through other methods to try to ensure that the frequency.
Operations once per second:
A. Log buffers are flushed to disk, even though the transaction has not yet been committed (always);
Even if one is I haven't committed yet, the InnoDB storage engine will still flush the contents of the redo log buffer to the redo log file every second. This can explain why the large transaction commit time is also very short.
B. Combined insert buffer (possible);
The merge Insert buffer (insert buffer) does not occur every second. The InnoDB storage engine will determine if the number of IO occurrences in the current second is less than 5, and if it is less than 5, InnoDB believes that the current IO pressure is small and can perform a merge insert buffer operation.
C. Refresh the dirty pages in the buffer pool of up to 100 InnoDB to disk (possible);
The InnoDB storage engine determines whether the ratio of dirty pages in the current buffer pool (buf_get_modified_ratio_pct) exceeds the value of the innodb_max_dirty_pages_pct parameter in the configuration file, and if this threshold is exceeded, The InnoDB storage engine believes that a disk synchronization operation is required, and 100 dirty pages are written to disk.
D. If there is currently no user activity, switch to background loop (possible);
Operation every 10 seconds:
A. Refresh 100 dirty pages to disk (possible);
B. Merge up to 5 insert buffers (always);
C. Flush the log buffers to disk (always);
D. Delete the useless undo page (always);
E. Refresh 100 or 10 dirty pages to disk (always);
In the above process, the InnoDB storage engine will first determine if the disk's IO operation is less than 200 times in the last 10 seconds, and if so, the InnoDB storage engine believes that there is currently enough disk IO operational capability, so 100 dirty pages are flushed to disk. Next, the InnoDB storage engine merges the insert buffer, which is different from the merge insert buffer that may occur once per second, and this time the merge insert buffer will always be in place. The InnoDB storage engine then flushes the logs to disk at one time. This is the same operation that occurs once per second. The next InnoDB storage engine performs the full purge operation, which is to delete the useless undo page. During full purge, the InnoDB storage engine determines whether deleted rows in the current transaction system can be deleted, and if so, InnoDB deletes them immediately. Try to reclaim up to 20 undo pages at a time. The InnoDB storage engine then determines whether the dirty pages in the buffer pool have more than 70% dirty pages, and refreshes 100 dirty pages to disk. If the scale is less than 70%, you only need to refresh the 10% dirty pages to disk.
If there is currently no user activity (when the money is idle) or the database is shut down (shutdown), it will switch to this cycle. Background Loop performs the following actions:
A. Delete the useless undo page (always);
B. Merge 20 insert buffers (always);
C. Jump back to the main loop (always);
D. Continuously refresh 100 pages until eligible (possible, jump to flush loop to complete);
If nothing can be done in the flush loop, the InnoDB storage engine switches to the suspend loop, suspends the master thread, and waits for the time to occur. If the user enables (enable) the InnoDB storage engine, but does not use any tables of the InnoDB storage engine, the master thread is always in a suspended state.
The Master Thread before the 2.InnoDB 1.2.x version
Prior to the innodb1.0.x version, the InnoDB storage engine was limited to IO, and the buffer pool was actually hard-coded when it was trying to refresh the disk coding. After the advent of SSDs, this provision largely limits the performance of the InnoDB storage engine to disk IO, especially write performance. From the previous introduction, the InnoDB storage engine will only flush 100 pages to disk at any one time, merging 20 insert buffers. If you are writing a dense reference program that can produce more than 100 dirty pages per second, the Master thread will always appear to be "busy" if it produces more than 20 insert buffers. Even though the disk can handle more than 100 pages of write and 20 insert buffer merges in 1 seconds, the hard coding,master thread will only choose to refresh 100 dirty pages and merge 20 insert buffers. At the same time, when the outage needs to be restored, since a lot of data has not been refreshed back to the disk, it will take a long time to return to the person recovery. For this issue InnoDB Plugin (starting with the innodb1.0.x version) provides the parameter innodb_io_catacity, which is used to represent the throughput of disk IO, by default 200. The number of pages flushed to disk is controlled according to the percentage of innodb_io_capacity. The rules are as follows:
A. When merging the insert buffers, the number of merge insert buffers is 50% of the innodb_io_capacity value;
B. When a dirty page is refreshed from the buffer, the number of dirty pages refreshed is innodb_io_capacity;
If the user is using an SSD-like disk, or raid a few disks. When the storage device has a higher IO speed, you can capitalize the value of innodb_io_capacity and know the throughput to match disk IO. Another parameter of the innodb1.0.x version innodb_adaptive_flushing (adaptive refresh), which affects the number of dirty pages refreshed per second. The original refresh rule is: dirty pages when the proportion of cache is less than innodb_max_dirty_pages_pct, do not refresh dirty pages, larger than innodb_max_dirty_pages_pct, refresh 100 dirty pages. With the introduction of the Innodb_adaptive_flushing parameter, the INNODB storage engine uses a buf_flush_get_desired_flush_rate function to determine the most appropriate number of dirty pages to be refreshed. The general approach is to determine the most appropriate number of dirty pages to refresh by judging the speed at which the redo logs are generated. Therefore, when the dirty page Villejau innodb_max_dirty_pages_pct, a certain amount of dirty pages will also be refreshed.
Another change is that you recycle up to 20 undo pages each time you perform a full purge operation. Starting with the innodb1.0.x version, the parameter innodb_purge_batch_size is introduced, which controls the number of undo pages per full purge collection, which defaults to 20. It can be modified dynamically. The status information of the schedule master thread can be viewed by command show engine InnoDB status. Such as:
It is shown that the main loop has been carried out 1,154,432 times, the operation of suspending (sleep) per second has been performed 1,154,432 times, the activity of 10 seconds has been 106,465 times, the background loop has been 90,829 times, and the flush loop has been performed 90,829 times.
3.InnoDB 1.2.x version of Master Thread
In the InnoDB 1.2.x release, the pseudo code for Master thread is as follows:
where Srv_master_do_idle_tasks () is a 10-second operation in the previous version, Srv_master_do_active_tasks () handles the previous operations per second. For operations that flush dirty pages, detach the master thread from the thread to a separate page Cleaner thread, reducing the work of the master thread and further improving the concurrency of the system.
InnoDB Storage Engine's master Thread
