InnoDB Checkpoint Internals: Sync vs Fuzzy, Crash Recovery Detection, and Four Failure Scenarios

Purpose of Checkpoint

Checkpoint aims to shorten the amount of log that must be scanned during crash recovery. Without it, recovery would need to scan all logs, which is time‑consuming. A checkpoint LSN is produced such that all transaction logs with LSN < checkpoint LSN have their modifications already persisted (dirty pages flushed to disk), meaning those log records can be discarded.

During crash recovery, only redo log records after the checkpoint LSN need to be processed, because dirty pages before that point are already on disk.

Two Checkpoint Designs

Synchronous Checkpoint (sync checkpoint)

Stops all writes, flushes every dirty page in the buffer pool to disk synchronously, then takes the maximum page LSN among those dirty pages as the checkpoint LSN. This guarantees that all dirty pages with LSN < checkpoint LSN are on disk.

Fuzzy Checkpoint (fuzzy checkpoint)

Does not stop writes. The buffer pool maintains a Flush List where each dirty page is tagged with its page LSN (the LSN of the first redo log record that modified the page after it was loaded). The Flush List is kept in ascending order of page LSN. A dedicated page‑cleaner thread asynchronously writes dirty pages from the Flush List to disk. The checkpoint LSN is simply the page LSN of the first dirty page on the Flush List; because the list is ordered, all pages with LSN < that value are already flushed.

Pre‑MySQL 8.0 Fuzzy Checkpoint Implementation

The following code snippet (from InnoDB source) shows the fuzzy checkpoint routine:

log_checkpoint(
    ibool sync,          /*!< in: TRUE if synchronous operation is desired */
    ibool write_always)  /*!< in: force physical write to log files */
{
    ...
    // Traverse flush list, find the smallest oldest_modified_lsn
    oldest_lsn = log_buf_pool_get_oldest_modification();
    // Ensure all redo log records with LSN < oldest_lsn are written to disk (WAL)
    log_write_up_to(oldest_lsn, LOG_WAIT_ALL_GROUPS, TRUE);
    log_sys->next_checkpoint_lsn = oldest_lsn;
    // Persist checkpoint LSN to log file header (alternating between two offsets)
    log_groups_write_checkpoint_info();
}

Key points from the comments:

Dirty pages are gradually inserted into the flush list during transaction execution (steal policy, no‑force).

Each dirty page carries an oldest_modified_lsn — the earliest LSN that modified the page.

The checkpoint LSN is set to the minimum oldest_modified_lsn on the flush list. log_write_up_to guarantees that all redo log records up to that LSN are on disk.

If the flush list is empty, oldest_lsn becomes the current system LSN ( log_sys->lsn), which does not by itself guarantee that earlier logs are flushed; hence the explicit log_write_up_to call.

Checkpoint LSN is persisted alternately at two fixed offsets (LOG_CHECKPOINT_1 and LOG_CHECKPOINT_2) in the first log file, with a monotonically increasing LOG_CHECKPOINT_NO to identify the latest checkpoint.

Detecting Whether Crash Recovery Is Needed

On restart, InnoDB must decide if the previous shutdown was clean. Two principles are used, checked in order:

Principle 2: There are log records after the checkpoint LSN.

Principle 1: checkpoint LSN != Flushed LSN.

During a normal shutdown, a synchronous checkpoint is performed. The checkpoint LSN is written both to the redo log header (LOG_CHECKPOINT_1 or LOG_CHECKPOINT_2) and to the system tablespace's first page at offset FIL_PAGE_FILE_FLUSH_LSN (called Flushed LSN). Therefore, after a clean shutdown, checkpoint LSN == Flushed LSN.

After a crash, the two LSNs may be equal or not. For example, a clean shutdown followed by a restart, one checkpoint, then a crash yields checkpoint LSN != Flushed LSN. Conversely, a clean shutdown, restart, some log writes but no new checkpoint, then crash yields equal LSNs yet recovery is still required — hence Principle 2.

Four Post‑Restart Crash Scenarios

Assume a normal shutdown, then restart, then a crash. Four possibilities exist (illustrated in the article's diagram):

Scenario 1: No logs generated after restart before crash. No crash recovery needed.

Scenario 2: Some logs generated, no checkpoint before crash. Principle 2 detects the need for recovery.

Scenario 3: A checkpoint occurred, then more logs, then crash before the next checkpoint. Principle 2 detects the need for recovery.

Scenario 4: A checkpoint occurred, then no further logs before crash. Principle 1 detects the need for recovery (checkpoint LSN != Flushed LSN). Only the undo phase is required; no redo phase because all dirty pages were already flushed (flush list empty). However, an uncommitted transaction may have its dirty pages on disk, so those changes must be rolled back via undo logs.

The article includes a diagram (not reproduced here) that visualizes these four cases.