MySQL checkpoint mechanism

checkpoint dry things:

The dirty pages in the buffer pool are flushed back to disk, except for how many dirty pages are flushed to disk each time, and when checkpoint is triggered.

Checkpoint solved the problem:
1. Shorten the database recovery time (database down, do not need to redo all the logs, because the page before checkpoint has been flushed back to disk)
2. When the buffer pool is not enough, flush the dirty pages to disk (buffer pool is not enough, according to the LRU calculation will overflow the least recently used page, if this page is dirty page, need to enforce checkpoint will dirty also brush back to disk)
3. When redo logs are not available, the dirty pages are refreshed (using recycled, not infinitely larger.) When re-use, the redo log at this time also needs to be forced to execute checkpoint to brush the dirty page back to disk)

Checkpoint Classification:
1.Sharp Checkpoint
Occurs when the database is closed and all dirty pages are brushed back to disk, which is the default. Set by the parameter innodb_fast_shutdown=1.
2.Fuzzy Checkpoint
Use fuzzy checkpoint inside the InnoDB storage engine to refresh the page, which refreshes only a portion of the dirty page, not all. It is broadly divided into the following situations:
A.master Thread Checkpoint
Almost every second or every 10 seconds from the list of dirty pages in the buffer pool (flush list), this is an asynchronous operation, and the InnoDB storage engine can do other parts of the operation without clogging.
B.flush_lur_list Checkpoint
The InnoDB storage engine needs to ensure that there are almost 100 free pages available in the LRU list. Before the innodb1.1.x version, it is necessary to check that there are enough free space operations in the LRU list to occur in the user query thread, which obviously blocks the user's query operations. If there are no 100 free pages, then the INNODB storage engine removes the pages from the end of the LRU list, and if they have dirty pages, they need to be Checkpoint, and those from the LRU list are called Flush_lru_list Checkpoint. However, after the MySQL5.6 version, this check was placed in a separate page Cleaner thread, with parameter innodb_lru_scan_depth to set the number of pages available.
C.async/sync Flush Checkpoint
In the case where the redo log file is not available, some will need to be flushed back to the disk, and the operation takes place on the flush list. If the LSN that has been written to the redo log is recorded as REDO_LSN, the most recent LSN that has been flushed back to the disk is recorded as CHECKPOINT_LSN, you can define: Checkpoint_age = Redo_lsn-checkpoint_ The LSN defines the variable async_water_mark=75%*total_redo_log_file_size, sync_water_mark=90%*total_redo_file_size. If the size of each redo log is 1G and two redo logs are defined with a total of 2G. So async_water_mark=1.5g,sync_water_mark=1.8g.
When Checkpoint_age<async_water_mark, there is no need to flush any dirty data to disk;
When Async_water_mark<checkpoint_age<sync_water_mark is triggered, async flush is flushed from the flush list to flush enough dirty pages to disk so that the refresh satisfies checkpoint_age <async_water_mark;
This rarely occurs when checkpoint_age>sync_water_mark, unless the redo log file is set too small, and a bulk Insret operation similar to load data is performed. This time trigger sync flush to flush enough dirty pages from the Flush list to disk so that the refresh satisfies checkpoint_age<async_water_mark;
Async Flush checkpoint Blocks the user query thread that finds the problem, and Sync flush checkpoint blocks all user query threads and waits for dirty page refreshes to complete. However, starting with the MySQL5.6 version, this part of the operation is placed in a separate page Cleaner thread, no longer clogging the user query thread.
D.dirty Page Too much Checkpoint
Too many dirty pages cause the InnoDB storage engine to force checkpoint to ensure that enough pages are available in the buffer pool. Can be set by parameter innodb_max_dirty_pages_pct.

Note: The checkpoint mechanism brushes the dirty page back to the disk not directly to the disk, but through Doublewrite (two write). That is, the dirty page is copied to the in-memory Doublewrite buffer first through the memcpy function. The shared tablespace is then written to the physical disk by Doublewrite two times (1MB each) sequentially, and then the Fsync function is called to synchronize the disk. How it is implemented will be shared later in the blog.

MySQL checkpoint mechanism