Study Notes: Master thread of InnoDB

This article is based on MySQL core technology: InnoDB Storage engine Chapter 2: Introduction.

I will not talk about the features of InnoDB here. The master thread process of InnoDB is summarized as follows:

(The Code referenced in this article is the source code package from mysql-5.1.55)

The following figure shows two small errors in http://ourmysql.com/archives/902because (1. two branches with a page ratio of less than 70%; 2. after refreshing 10 stolen pages at the bottom, it should still be transferred to the fuzzy Check Point). I modified it here.

 

 

The entire process mainly includes five sub-processes: (three operations: refresh the log cache to the disk srv_sync_log_buffer_in_background, merge and insert the cache ibuf_contract_for_n_pages, and refresh the page to the disk buf_flush_batch ))

1. operations per second:

• The log buffer is refreshed to the disk even if the transaction has not been committed (always ).
• Merge insert buffer (possible ).
• Refresh the dirty pages in the buffer pool of up to 100 InnoDB to the disk (possibly). If this operation is performed, it may have exceeded 1 s, so you don't have to sleep for 1 s in the next loop.
• Is adaptive_flush enabled? If yes, refresh the buf_flush_get_desired_flush_rate page to the disk.
• If no user activity exists, switch to the background loop (possibly ).

Even if a transaction has not been committed, the InnoDB Storage engine will refresh the content in the redo log buffer to the redo log file every second. This point must be known, which can be a good explanation of why a large transaction commit takes a very short time.

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.

Similarly, refreshing 100 dirty pages does not happen every second. The InnoDB Storage engine determines whether the ratio of dirty pages in the Current Buffer Pool (buf_get_modified_ratio_pct) exceeds the innodb_max _ dirty_pages_pct parameter in the configuration file (90 by default, indicating 90% ), if this threshold is exceeded, the InnoDB Storage engine considers that Disk Synchronization is required and writes 100 dirty pages to the disk.

 

2. Operations per 10 seconds:

• Refresh 100 dirty pages to the disk (possibly ).
• Merge up to five insert buffers (always ).
• Refresh the log buffer to the disk (always ).
• Delete useless undo pages (always ).
• Refresh 100 or 10 dirty pages to the disk (always ).
• Generate a checkpoint (always ).

 

In the above process, the InnoDB Storage engine first checks whether the disk Io 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 the 100 dirty pages to the disk. Then, the InnoDB Storage engine merges the insert buffer. Unlike the merge insert buffer operation that may occur every one second, this merge insert buffer operation will always be performed at this stage. Then, the InnoDB Storage engine will refresh the log buffer to the disk again, which is the same as the operations performed per second.

The InnoDB Storage engine then performs a full purge operation to delete useless undo pages. When performing update and delete operations on a table, the original rows are marked as deleted. However, the version information of these rows must be retained due to the consistency read relationship. However, during the full purge process, the InnoDB Storage engine determines whether the row that has been deleted in the current transaction system can be deleted. For example, some query operations may need to read the Undo information of the previous version, if yes, InnoDB will immediately delete it. From the source code, we can find that the InnoDB Storage engine can delete up to 20 undo pages at a time when operating full purge.

The InnoDB Storage engine then checks the ratio of dirty pages in the buffer pool (buf_get_modified_ratio_pct). If there are more than 70% dirty pages, it refreshes 100 dirty pages to the disk; if the proportion of dirty pages is less than 70%, you only need to refresh the 10% dirty pages to the disk.

Finally, the InnoDB Storage engine generates a checkpoint. The checkpoint of the InnoDB Storage engine is also called a fuzzy checkpoint ). The InnoDB Storage engine does not write all dirty pages in the buffer pool to the disk at checkpoint, because this may affect the performance, but only the oldest log serial number (oldest LSN) to the disk.

 

3. background_loop ):

If there is no user activity (when the database is idle) or the database is closed, the system switches to this loop. Otherwise, the system jumps to the main loop and starts from the beginning.

• Delete useless undo pages (always ).
• Merge 20 insert buffers (always ).

4. flush_loop:

If there is still no user activity after the backgroud loop (when the database is idle), it will enter this loop; otherwise, it will jump to the main loop and start again.

• Refresh the 100 pages until the condition (buf_get_modified_ratio_pct () <= srv_max_buf_pool_modified_pct) is met.
• Generate a checkpoint (always ).

5. suspend_thread:

If nothing can be done in the flush loop, the InnoDB Storage engine switches to suspend_loop and suspends the master thread to wait for the event to occur. If the InnoDB Storage engine is enabled, but no InnoDB Storage engine tables are used, the master thread is always suspended. Wait until an OS _event_wait event occurs.

 

The above five processes are the main processes of the master thread. At present, I only have a simple understanding of this function. I don't fully understand why this process is like this, and the specific implementation of the three operations in this process is still unclear. These are also the key points of my later study. You are welcome to give me some advice. Thank you!