InnoDB dirty page refresh mechanism in MySQL checkpoint

We know that InnoDB uses the write Ahead log policy to prevent the loss of downtime data, that is, when a transaction commits, the redo log is written, and then the memory data page is modified, resulting in dirty pages. Now that you have redo logs to ensure data persistence, you can also fetch data directly from the buffer pool page when querying, why refresh the dirty pages to disk? If the redo log can grow infinitely and the buffer pool is large enough to cache all the data, it is not necessary to flush the dirty pages in the buffer pool to disk. However, there are usually several issues:

• Limited server memory, buffer pool not enough to cache all data
• Redo Log Unlimited increase cost requirements too high
• If redo all log recovery time is too long during downtime

In fact, when the database goes down, the database does not need to redo all the logs, only the logs after the last brush-in point are executed. This point is called Checkpoint, which solves the problem above:

• Reduce Database recovery time
• Flush a dirty page to disk when the buffer pool is not sufficient
• Refresh dirty pages When redo logs are not available

Redo logs are designed to be recyclable, and when a log file is full, the data in the redo log that has been flushed to the disk is no longer required to be overwritten and reused.

The InnoDB engine marks the version with the LSN (log Sequence number), which is the end point of each log in the log space, expressed as a byte offset. Each page has a Lsn,redo log and Lsn,checkpoint also has an LSN. can be observed by command show engine innodb status :

---LOG---LogSequence Number 11102619599LogFlushed up to   11102618636 LastCheckpointAt111026063190PendingLogWrites,0Pending CHKP writes15416290 LogI/O's done, 12.32 log I/O'S/Second

How many pages does the checkpoint mechanism refresh each time, where to fetch dirty pages, and when to trigger a refresh? These are all very complex. There are two kinds of checkpoint, respectively:

• Sharp Checkpoint
• Fuzzy Checkpoint

Sharp checkpoint occurs when the database is closed and all dirty pages are brushed back to disk. A partial dirty page refresh is performed at run time using the fuzzy checkpoint. Some dirty pages are refreshed in the following ways:

• Master Thread Checkpoint
• Flush_lru_list Checkpoint
• Async/sync Flush Checkpoint
• Dirty Page Too much Checkpoint

Master Thread Checkpoint

The Master thread refreshes a percentage of the page-back disk from the dirty pages list of the buffer pool at a rate of every second or every 10 seconds. This process is asynchronous and does not block the query thread.

Flush_lru_list Checkpoint

InnoDB to ensure that there are around 100 free pages available in the LRU list. Before the innodb1.1.x version, to check whether there are enough pages in the LRU for the user query operation thread, if not, the end of the LRU list will be retired, if there is a dirty page in the obsolete page, will be forced to perform checkpoint brush back dirty page data to disk, obviously this will block the user query thread. Starting with the innodb1.2.x version, this check is placed in a separate page Cleaner thread, and the user can innodb_lru_scan_depth control the number of pages available in the LRU list by default of 1024.

Async/sync Flush Checkpoint

When redo log files are not available, you need to force some pages in the dirty pages list to be flushed back to disk. This ensures that redo log files can be reused. Before the innodb1.2.x version, Async flush checkpoint blocks the user query thread that found the problem, and Sync flush checkpoint blocks all query threads. Innodb1.2.x is then placed on a separate page Cleaner Thread.

Dirty Page Too much Checkpoint

With too many dirty pages, the InnoDB engine enforces checkpoint. The purpose is also to ensure that there are enough free pages available in the buffer pool. It can be set by parameters innodb_max_dirty_pages_pct :

Mysql>Show variables like 'innodb_max_dirty_pages_pct';+----------------------------+-------+|Variable_name|Value|+----------------------------+-------+|innodb_max_dirty_pages_pct|  -    |+----------------------------+-------+

InnoDB dirty page refresh mechanism in MySQL checkpoint