Master Thread of InnoDB Storage engine, innodbmaster

Source: Internet
Author: User

Master Thread of InnoDB Storage engine, innodbmaster
The main work of the InnoDB Storage engine is completed in a separate background Thread Master Thread.
1. Master Thread before InnoDB 1.0.x

Master threads have the highest priority. It consists of multiple loops: Main loop, backgroup loop, flush loop, and suspend loop ). The Master Thread switches between the above four states based on the database running status. Loop is called the main Loop, because most operations are in this Loop, there are two major operations: operations per second and operations per 10 seconds. The pseudocode is as follows:


We can see that the loop is implemented through thread sleep, which means that the so-called operation once per second or every 10 seconds is not accurate. There may be latency when the load is high. It can only be said at this frequency. Of course, InnoDB source code also uses other methods to ensure this frequency as much as possible.
Operations per second:
A. The log buffer is refreshed to the disk, even if the transaction has not been committed (always );
Even if I have not submitted a redo log, the InnoDB Storage engine will refresh the content in the redo log buffer to the redo log file every second. This can explain why another large transaction commit takes a short time.
B. Merge insert buffering (possible );
Insert Buffer does not occur every second. The InnoDB Storage engine determines whether the number of I/O operations in the current second is less than 5. If the number is less than 5, InnoDB considers that the current I/O pressure is low and can merge and insert buffering operations.
C. Refresh dirty pages in the buffer pool of up to 100 InnoDB to the disk (possibly );
The InnoDB Storage engine determines whether the ratio of dirty pages (buf_get_modified_ratio_pct) in the Current Buffer Pool exceeds the value of innodb_max_dirty_pages_pct in the configuration file. If the value exceeds this threshold, the InnoDB Storage engine considers that Disk Synchronization is required and writes 100 dirty pages to the disk.
D. If no user activity exists, switch to background loop (possibly );
Operations per 10 seconds:
A. Refresh 100 dirty pages to the disk (possibly );
B. Merge up to five insert buffers (always );
C. Refresh the log buffer to the disk (always );
D. Delete useless Undo pages (always );
E. Refresh 100 or 10 dirty pages to the disk (always );
In the above process, the InnoDB Storage engine first checks whether the disk I/O operations in the past 10 seconds are less than 200 times. If yes, the InnoDB Storage engine considers that there is sufficient disk I/O operation capability, so it refreshes 100 dirty pages to the disk. Next, the InnoDB Storage engine merges the insert buffer, which is different from the merge insert buffer operation that may occur once an operation per second. This merge insert buffer will always be performed. After that, the InnoDB Storage engine will refresh the log to the disk once. This is the same as the operation that occurs once per second. In the next step, the InnoDB Storage engine will perform the full purge operation to delete useless Undo pages. During the full purge process, the InnoDB Storage engine determines whether the row deleted in the current transaction system can be deleted. If yes, InnoDB immediately deletes the row. At most 20 undo pages can be recycled at a time. The InnoDB Storage engine then determines that if there are more than 70% dirty pages in the dirty pages of the buffer pool, 100 dirty pages will be refreshed to the disk. If the proportion is less than 70%, you only need to refresh the 10% dirty pages to the disk.
If there is no user activity (when the huge amount of money is idle) or the database is shut down, it will switch to this loop. The background loop performs the following operations:
A. Delete useless Undo pages (always );
B. Merge 20 insert buffers (always );
C. jump back to the main loop (always );
D. Refresh 100 pages until they meet the conditions (possibly, jump to flush loop );
If there is nothing to do in the flush loop, the InnoDB Storage engine switches to the suspend loop and suspends the Master Thread to wait for the time to occur. If you enable the InnoDB Storage engine without using any InnoDB Storage engine tables, the Master Thread is always suspended.
2. Master Thread before InnoDB 1.2.x
Before InnoDB1.0.x, the InnoDB Storage engine was actually limited to I/O. hard coding was actually implemented when the buffer pool wanted to refresh the disk ). After the emergence of SSD, this provision severely limits the performance of the InnoDB Storage engine on disk I/O, especially the write performance. According to the previous introduction, at any time, the InnoDB Storage engine will refresh up to 100 pages to the disk and merge 20 insert buffers. If it is in a write-intensive reference program, more than 100 dirty pages may be generated per second. If it is to generate more than 20 insert buffers, the Master Thread always seems "too busy ". Even if the disk can process the write operations on 100 additional pages and the merge of 20 insert buffers within one second, however, due to hard coding, the Master Thread will only refresh 100 dirty pages and merge 20 insert buffers. At the same time, recovery is required when a crash occurs, because a lot of data has not been refreshed back to the disk, it may take a long time to return to the person for recovery. To address this problem, InnoDB Plugin (beginning with InnoDB1.0.x) provides the innodb_io_catacity parameter to indicate the disk IO throughput. The default value is 200. The number of refreshes to the disk page is controlled according to the percentage of innodb_io_capacity. The rules are as follows:
A. The number of merged insert buffers is 50% of innodb_io_capacity;
B. When refreshing dirty pages from the buffer zone, the number of dirty pages refreshed is innodb_io_capacity;
If you use an SSD disk or RAID several disks. When the storage device has a higher I/O speed, the innodb_io_capacity value can be capitalized to know the disk I/O throughput. Another parameter in InnoDB1.0.x is innodb_adaptive_flushing (auto-Refresh), which affects the number of dirty pages refreshed per second. The original refresh rule is: when the proportion of dirty pages in the cache is less than innodb_max_dirty_pages_pct, the dirty pages are not refreshed. If the ratio is greater than innodb_max_dirty_pages_pct, 100 dirty pages are refreshed. 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 maximum number of dirty pages to be refreshed. The general approach is to determine the speed at which redo logs are generated to determine the most appropriate number of dirty pages to be refreshed. Therefore, when the ratio of dirty pages is less than innodb_max_dirty_pages_pct, a certain amount of dirty pages will also be refreshed.

Another change is that during each previous full purge operation, up to 20 Undo pages can be recycled. The innodb_purge_batch_size parameter is introduced from the InnoDB1.0.x version. This parameter can control the number of Undo pages collected by full purge each time. The default value is 20. It can be dynamically modified. Run the show engine innodb status Command to view the status information of the Master Thread in the specified period. For example:


As shown in the figure, the main loop is performed 1154432 times, the sleep operation is performed 1154432 times per second, the activity is performed 106465 times per second, and the background loop is performed 90829 times, flush loop performed 90829 times.
3. InnoDB 1.2.x Master Thread
In InnoDB 1.2.x, the pseudo code of the Master Thread is as follows:

Srv_master_do_idle_tasks () is the operation every 10 seconds in the previous version. srv_master_do_active_tasks () processes the operation in the previous second. For the dirty Page refresh operation, the Master Thread is separated to a separate Page Cleaner Thread, which reduces the work of the Master Thread and further improves the concurrency of the system.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.