The fastest shortcut in the world is to keep your feet on the ground. Focus on the sharing place.

The purpose of the daily knowledge point series is to make a general summary of a certain knowledge point, which can be completed in one minute.

This is not a detailed interpretation of the original, only as a kind of introduction.

True understanding must be the knowledge you gain from your own research and exploration, joining the organization to take you forward and grow together.

In a nutshell, Checkpoint technology is the operation of flushing dirty pages from the cache pool to disk at a certain point in time

Problems encountered?

We all know that buffer pools are created to address the gap between CPU and disk speed, so that we don’t have to do disk I/O operations while reading or writing to the database. With a buffer pool, all page operations are done in the buffer pool first.

For example, a DML statement that performs a data update or delete operation that changes the record in the buffer pool page is called a dirty page because the data on the buffer pool page is newer than that on the disk.

In any case, the memory page data needs to be flushed back to disk, which involves several issues:

  • If every time a page changes, the version of the new page is flushed to disk, this is very expensive
  • If hotspot data is concentrated in a few pages, the performance of the database becomes very poor
  • If an outage occurs while flushing a new version of a page from the buffer pool to disk, the data cannot be recovered

Write Ahead Log

The WAL policy, which addresses the problem of data loss due to downtime when refreshing page data to disk, is a set of techniques used in relational database systems to provide atomicity and persistence (two of the ACID properties).

The core of WAL’s strategy is this

The redo log is written to the redo log every time a transaction is committed, and the buffer pool data page is modified so that the system can resume operations after a restart in the event of a power failure, for example

The WAL policy mechanism works

InnoDB maintains a redo log to ensure data is not lost. The WAL policy is to log the changes to the redo log before the redo log is logged.

When a failure occurs and memory data is lost, InnoDB restores the buffer pool data page to its pre-crash state by replaying the redo log upon restart.

Checkpoint

With WAL, we should be safe. The problem appears in the redo log:

  • Redo logs cannot be infinite, storing our data forever waiting to be flushed to disk together
  • If the redo log is too large during database idle recovery, the cost of recovery can be very high

Therefore, in order to solve the problem of dirty page refresh performance, we use Checkpoint technology when and under what conditions dirty pages should be refreshed.

The purpose of:

1. Shorten the database recovery time

When the database is recovered, you do not need to redo all the log information. Because the data page before Checkpoint has been flushed back to disk. All you need is the redo log after Checkpoint.

2. Flush dirty pages to disk when the buffer pool is insufficient

When the buffer pool space is insufficient, the LRU algorithm overflows the least recently used pages. If the pages are dirty, Checkpoint is performed to flush the dirty pages, that is, the new version of the pages back to the disk.

Refresh dirty pages when the redo log is not available

The redo log is not available because the current database is designed to recycle it, so its space is not infinite.

When the redo log is full, all update statements are blocked because the system cannot accept updates.

In this case, Checkpoint must be generated to advance write POS, and all dirty pages within the advance range must be flushed to disks

The types of Checkpoint

Checkpoint timing, conditions, and dirty page selection are all complex.

How many dirty pages does Checkpoint flush to disk each time?

Where does Checkpoint pick up dirty pages each time?

When is Checkpoint triggered?

To address the above problems, InnoDB storage engine provides two types of Checkpoint:

  • Sharp Checkpoint

    Flush all dirty pages back to disk when the database is shutdown. This is the default way to work with innodb_fast_shutdown=1

  • Fuzzy Checkpoint

    InnoDB storage engine uses this mode internally and only flushes some dirty pages instead of flushing all dirty pages back to disk

What happens at FuzzyCheckpoint

  • Master Thread Checkpoint

    Flush a percentage of the buffer pool’s dirty page list back to disk at a rate of about a second or ten seconds.

    This process is asynchronous, meaning that the InnoDB storage engine can do other operations at this time, and the user query thread is not blocked

  • FLUSH_LRU_LIST Checkpoint

    Because the LRU list requires a certain number of free pages to be available, pages are removed from the tail if not enough, and this Checkpoint is performed if the removed pages have dirty pages.

    After 5.6, this Checkpoint is done in a separate Page Cleaner thread, and the user can control the number of available pages in the LRU list with the innodb_lru_scan_depth parameter, which defaults to 1024

  • Async/Sync Flush Checkpoint

    When the redo log file is unavailable and some pages are forcibly flushed back to disk, the dirty pages are selected from the dirty page list

    User queries will not be blocked after version 5.6

  • The number of Dirty pages is too much Checkpoint, causing InnoDB storage engine to force Checkpoint.

    The overall goal is to ensure that there are enough pages available in the buffer pool.

    It can be controlled by innodb_max_dirty_pages_pct. For example, if innodb_max_dirty_pages_pct is 75, CheckPoint is forced when 75% of dirty pages are in the buffer pool

conclusion

  • Buffer pool data pages are created to speed up database DML operations because of the gap between CPU and disk

  • WAL policies (at the heart of the redo log) occur because of the inconsistency between the buffer pool data page and the disk data.

  • Checkpoint technology arose because of the flushing performance of dirty pages in the buffer pool

InnoDB does not interact with disk for persistence for every DML operation to improve execution efficiency. Instead, a Write Ahead Log policy is used to Write redo logs to keep things persistent.

Dirty pages modified in the buffer pool are flushed asynchronously, while free pages and redo logs are available via Checkpoint technology.

The fastest shortcut in the world is to keep your feet on the ground. Focus on the sharing place.