Master thread in the background thread of the InnoDB Storage Engine

The main work of the InnoDB Storage engine is completed in a separate background thread master thread.
The main thread of the master thread mainly includes the following cycles:
Main loop
Background loop
Refresh loop flush loop
Pause loop suspend loop

Void master_thread (){
Loop:
For (int I = 0; I <10; I ++)
{
Sleep 1 second if necessary
Do things once per second // 1. 1 operation per second
If (no user activity) // if there is no user activity currently, switch to the background Loop
Goto background loop;
}
Do things once per ten seconds // 2. operations every 10 seconds
Goto loop;
Background loop:
Do something // 3. operations when the database is idle or when the database is closed
If (not idle) // switch from non-idle to master Loop
Goto loop;
Else // switch to refresh cycle when idle
Goto flush loop;
Flush loop:
Do buffer pool flush 100 dirty page // refresh 100 dirty pages to disk
// Proportion of dirty pages in the cache pool> threshold value. The default value is 90%. 100 dirty pages are continuously refreshed to the disk.
If (buf_get_modified_ratio_pct> innodb_max_dirty_pages_pct)
Goto flush loop;
Goto suspend loop; // switch to the pause loop and suspend the master thread
Suspend loop:
Suspend_thread ()
Waiting event
Goto loop;
}

1. 1 operation per second
(1) Redo the log buffer and refresh it to the disk, even if the transaction has not been committed (this can explain why the time of the large transaction commit is very fast );
(2) determine whether or not I/O is <5 times in the current 1 second. If yes, the current I/O pressure is low and the insert buffer can be merged;
(3) determine the ratio of dirty pages in the Current Buffer Pool buf_get_modified_ratio_pct> innodb_max_dirty_pages_pct. If this threshold is exceeded, Disk Synchronization is required to write 100 dirty pages to the disk;
(4) If no user activity exists, switch to the background loop of the background loop.
2. operations every 10 seconds
(1) If I/O is <200 in the past 10 seconds, I think there is sufficient disk I/O capability to refresh 100 dirty pages to the disk;
(2) Merge up to five insert buffers;
(3) refresh the log buffer to the disk;
(4) perform a full purge operation to delete useless undo pages (up to 20 undo pages can be deleted at a time).-> function: Perform update and delete operations on tables, the original row is marked as deleted, but the row version information needs to be retained for consistent read. However, during the full purge operation, the system determines whether the deleted rows in the current transaction system can be deleted (for example, some query operations may need to read the undo information of previous versions ), if yes, InnoDB will immediately delete it.
(5) Refresh 100 or 10 dirty pages to the disk (if the ratio of dirty pages is greater than 70%, refresh 100; if the ratio of dirty pages is greater than 70%, refresh 10 );
(6) generate a checkpoint, which is a fuzzy checkpoint. The InnoDB Storage engine does not write all dirty pages in the buffer pool to the disk during checkpoint, because this may affect the performance, write the pages with the oldest LSN's oldest log serial number to the disk.
3. operations when the database is idle or when the database is closed
(1) Delete useless undo pages;
(2) Merge 20 insert buffers;
(3) jump back to the main loop;
(4) Refresh 100 dirty pages to the disk until they meet the conditions (may jump to the flush loop to complete ).


Potential problems in master thread: hard-coded hard coding
(1) A maximum of 100 dirty pages can be refreshed and 5 inserted buffers can be merged;
Problem: "Too busy" is slow during intensive writing, and recovery is required in case of downtime, because a lot of data has not been refreshed to the disk, it may take a long time to recover.
Fix: Refer to google patch and provide the disk I/O throughput parameter innodb_io_capacity. The default value is 200. Refresh the 100% dirty page and merge the 5% insert buffer.
(2) Dirty page proportion innodb_max_dirty_pages_pct: 90% by default
Problem: The value is "too big". If there is a lot of memory or database server pressure, the speed of refreshing dirty pages may be reduced. At the same time, it may take more time to recover the database.

Corrected: innodb_max_dirty_pages_pct defaults to 90%-> default 75%, and innodb_adaptive_flushing is used to automatically refresh the number of dirty pages per second. (The most appropriate number of dirty pages to be refreshed is determined by using the batch function, the buf_flush_get_desired_flush_rate function determines the most suitable number of dirty pages to be refreshed by determining the speed at which redo logs are generated ).

Reference: <MySQL technology insider InnoDB Storage engine>