preface

To improve transaction efficiency, data in the buffer pool is not written to disk immediately. Therefore, data in the memory may be inconsistent with data on disk.

If the buffer pool fails, data cannot be persisted, causing data inconsistency between disks and the buffer pool.

In this case, modified data in the memory cannot be persisted because it fails to be written to disks. This can be done through redo log first. Redo logs can be “redone” after a failed restart, ensuring persistence. However, the redo log space cannot be expanded indefinitely. Data that is modified in memory and not committed to disk, called dirty pages, also need to be written to disk.

It is checkpoint’s job to process dirty pages in memory, and at certain points, the dirty pages are put to disk.

Checkpoint addresses the following issues:

  • Shorten the database recovery time.
  • Flush dirty pages to disk when the buffer pool is insufficient.
  • Flush dirty pages when redo logs are not available.
  • During fault recovery, only redo logs after checkpoint are recovered, shortening the recovery time. When the buffer is insufficient, the LRU algorithm is used to flush some dirty pages into the disk.

Classification:

There are two types of checkpoint:

  • Sharp checkpoint: Flushes all dirty pages in the buffer pool to disks when the database is shut down.
  • Fuzzy checkpoint: When the database is running properly, the dirty pages are written to the disk at different times. The dirty pages are flushed to the disk one by one without causing performance problems.

Master thread checkpoint Master threads asynchronously flush dirty pages from the memory to the disk once per second or every 10 seconds.

flush_lru_list checkpoint

Flush_lru_list checkpoint is executed in a separate Page Cleaner thread. The LRU list is the LRU list of the buffer pool. The LRU free list reserves a certain number of free pages to ensure that the buffer pool has enough space to handle new database requests.

When the idle list is insufficient, the flush_lru_list checkpoint occurs. The idle threshold can be configured.

async/sync flush checkpoint

Async /sync Flush checkpoint is performed in a separate Page Cleaner thread. Flush a portion of the dirty data in the buffer pool to disk when the redo log is not available. You can configure a threshold to refresh the redo log space when it reaches a specified threshold.

dirty page too much checkpoint

The number of dirty pages too much checkpoint is set to once per second in the master thread. If the number of dirty pages in the buffer pool is too much, checkpoint dirty pages are flushed to ensure that there are enough available pages in the buffer pool.

conclusion

In order to improve the efficiency of transaction execution, mysql does not interact with disks for persistence every time. The log-first policy ensures the persistence of transactions. If dirty pages are flushed asynchronously during transaction modification, checkpoint is used to obtain available memory and redo log space.