MySQLCheckpoint mechanism _ MySQL

What checkpoint does: refresh the dirty pages in the buffer pool back to the disk. The difference is that the number of dirty pages Retrieved each time to the disk, and when to trigger the checkpoint. Checkpoint: 1. shorten the database recovery time (what checkpoint does when the database goes down: refresh the dirty pages in the buffer pool back to the disk, the difference is how many dirty pages are retrieved from each refresh to the disk, and when the checkpoint is triggered. Checkpoint: 1. shorten the database recovery time (when the database is down, you do not need to redo all the logs, because the pages before the checkpoint have been refreshed back to the disk) 2. when the buffer pool is insufficient, refresh the dirty pages to the disk (when the buffer pool is insufficient, the least recently used pages will overflow based on LRU. if this page is dirty, you need to force the checkpoint to fl the dirty data back to the disk.) 3. when the redo log is unavailable, refresh the dirty page (which is recycled and not infinitely increased. When the redo log needs to be used again, the checkpoint must be forcibly executed to refresh the dirty page back to the disk.) checkpoint Category: 1. sharp Checkpoint fl all dirty pages back to the disk when the database is closed. this is the default value. Set the parameter innodb_fast_shutdown = 1. 2. Fuzzy Checkpoint uses Fuzzy Checkpoint in the InnoDB storage engine to refresh pages, that is, only a part of dirty pages are refreshed, rather than all. It can be roughly divided into the following situations:. the Master Thread Checkpoint is similar to the dirty page List (Flush list) of the buffer pool every second or every 10 seconds. this is an asynchronous operation. The InnoDB storage engine can perform other operations without blocking. B. The FLUSH_LUR_LIST Checkpoint InnoDB storage engine must ensure that there are almost 100 idle pages in the LRU list for use. Before InnoDB1.1.X, check whether there is sufficient available space in the LRU list in the user query thread. This will obviously block the user's query operations. If there are no 100 idle pages, the InnoDB storage engine will remove the pages at the end of the LRU list. if these pages contain dirty pages, you need to perform Checkpoint, these are called FLUSH_LRU_LIST Checkpoint from the LRU list. However, this check is placed in a separate Page Cleaner Thread after MySQL. the innodb_lru_scan_depth parameter is used to set the number of available pages. C. Async/Sync Flush Checkpoint when redo log files are unavailable, you need to refresh some of them back to the disk, and the operation takes place on the Flush list. If you record the LSN that has been written to the redo log as redo_lsn and refresh the latest LSN to the disk as checkpoint_lsn, you can define: checkpoint_age = redo_lsn-checkpoint_lsn in the defined 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 1 GB, two redo logs are defined as 2 GB in total. Then async_water_mark = 1.5G, sync_water_mark = 1.8G. When checkpoint_agesync_water_mark is used, this rarely happens unless the set redo log file is too small and the bulk insret operation similar to load data is performed. In this case, Sync Flush is triggered to refresh enough dirty pages from the Flush list to make the disk meet the requirements of checkpoint_age after refreshing.