InnoDB Redo Log Visualization | mtr, LSN & Checkpoint Age

The idea: make the log durable, write the pages later

InnoDB does not write a changed 16 KB page when a transaction commits. It writes a short description of the change, a redo record, to the redo log, and makes only the log durable. The pages stay dirty in the buffer pool and are written later by the page cleaner. After a crash, the redo log is replayed to bring the pages up to date. The redo log has a fixed size, so the pages must be written fast enough that the log never needs redo it has not yet made unnecessary.

A user thread runs an mtr that changes page 4 in the buffer pool and copies its redo into the log buffer at an LSN range. log_writer writes the log buffer into the OS page cache, log_flusher fsyncs it to the redo file. Separately, page 4 sits on the flush list and the page cleaner writes it to accounts.ibd later; the checkpoint LSN is the oldest dirty page's oldest_modification. — Two paths to disk: redo goes log buffer → OS page cache → redo file at commit time; dirty pages go to the tablespace later, oldest first, and that moves the checkpoint.

Mini-transactions and LSNs

Every change to a page happens inside a mini-transaction (mtr). The thread starts the mtr, latches the pages it needs (an x-latch for a page it changes), changes them in the buffer pool and collects the redo records in the mtr's private buffer: for an UPDATE, for example, MLOG_REC_UPDATE_IN_PLACE for the row and a record for the undo log entry. Nothing is shared yet.

At mtr commit the records are copied into the shared log buffer (innodb_log_buffer_size, 16 MB by default). The mtr reserves a range of the log, [start, end). The position in the log is the LSN (log sequence number): a byte count that only grows. Then the page gets newest_modification = end. If the page was clean, it also gets oldest_modification = start and joins the flush list, the list of dirty pages ordered by that LSN. Then the latches are released. An mtr is atomic: after a crash either all of its records are replayed or none. Demo: UPDATE and COMMIT.

A COMMIT is one more small mtr: it marks the transaction's undo log as committed. Its redo is what makes the transaction committed after a crash.

The log writer and the log flusher

Since MySQL 8.0 dedicated threads move the redo to disk. log_writer write()s the log buffer from write_lsn up to the current LSN into the redo files; log_flusher fsync()s them and advances flushed_to_disk_lsn. A user thread that commits only waits: log_flush_notifier (or log_write_notifier) wakes it when the LSN it needs is reached. Many commits share one write and one fsync, which is InnoDB's group commit. In this model the writer only runs when a commit or the one-second timer asks for it; the real one writes as soon as there is something to write.

Since MySQL 8.0.30 the redo log is 32 files in #innodb_redo/ named #ib_redoN, sized by innodb_redo_log_capacity (100 MB by default, 8 MB at least). Older versions used ib_logfile0 and ib_logfile1 (innodb_log_file_size × innodb_log_files_in_group). The files are reused in a circle: a file whose redo is all below the checkpoint can be recycled.

innodb_flush_log_at_trx_commit

This setting decides what COMMIT waits for. The write puts the redo into the OS page cache, so it survives if only mysqld dies; the fsync puts it on the disk, so it survives a power failure too.

Value	At COMMIT	Once a second	mysqld crash loses	OS crash / power failure loses
`1` (default)	write + fsync, then OK	—	nothing ✓	nothing ✓
`2`	write, then OK	fsync	nothing ✓	up to ~1 s of commits ✗
`0`	nothing, OK at once	write + fsync	up to ~1 s of commits ✗	up to ~1 s of commits ✗

The second is innodb_flush_log_at_timeout (1 by default). Losing a commit never corrupts the database: after recovery it is as if those transactions never ran. But a client was told OK for them. With the binary log on, sync_binlog = 1 and innodb_flush_log_at_trx_commit = 1 together are the "fully durable" setting (see Binlog Group Commit). Demo: =1, power failure loses no commit; Demo: =2, mysqld crash vs OS crash; Demo: =0, mysqld crash loses the last second.

Three rows, one per setting. Each shows a commit's redo in three places: log buffer, OS page cache, disk. With 1 the commit returns after the disk; with 2 after the OS page cache; with 0 while it is still in the log buffer. A mysqld crash wipes the log buffer, an OS crash wipes the log buffer and the OS page cache. — Where the redo is when COMMIT returns decides which crash can lose it: a mysqld crash wipes the log buffer, an OS crash also the page cache.

The flush list and the checkpoint

Redo is only needed for changes that are not yet in the tablespace. The oldest such change is the smallest oldest_modification on the flush list. That LSN is the checkpoint LSN: recovery may start there, and all redo below it can be overwritten. The page cleaner threads (innodb_page_cleaners) write dirty pages from the tail of the flush list, the oldest end, through the doublewrite buffer (see the doublewrite buffer). The log_checkpointer thread then moves the checkpoint and writes it into the redo log header. This is a fuzzy checkpoint: nothing stops, and pages keep getting dirty while it moves.

One rule ties the two paths: a page may be written only when the redo up to its newest_modification is on disk (write-ahead logging). If it is not, the redo is flushed first. Demo: page cleaner moves the checkpoint flushes page 4, then page 5 whose change is not yet fsynced, so the log is flushed first. When the flush list is empty the checkpoint can go up to the current LSN.

Checkpoint age and adaptive flushing

The checkpoint age is the current LSN minus the checkpoint LSN: the redo that a crash still needs. It must never reach innodb_redo_log_capacity, or InnoDB would overwrite redo it still needs. If writes come in faster than the page cleaner flushes, the age grows:

Above innodb_adaptive_flushing_lwm (10 % of the capacity) adaptive flushing starts: the page cleaner flushes more pages per second the larger the age and the redo rate, up to innodb_io_capacity_max.
At about 7/8 of the capacity the page cleaner does an async flush: as many pages as needed, as fast as it can. Writers continue.
At about 15/16 every thread that starts an mtr stops in log_free_check and waits while a sync flush brings the age down. This is the stall that shows as a sudden drop of throughput to near zero.

A bar from the checkpoint LSN to the end of the redo capacity. The filled part up to the current LSN is the checkpoint age. Marks at 10 percent (adaptive flushing starts), 7/8 (async flush) and 15/16 (sync flush: writers stall). Flushing the oldest dirty page moves the checkpoint LSN to the right and shortens the age. — The age grows with every mtr and shrinks only when the oldest dirty pages are flushed; at 15/16 of the capacity writers wait.

The cure for regular stalls is a larger innodb_redo_log_capacity (it can be changed online since 8.0.30) or more flushing capacity (innodb_io_capacity, innodb_io_capacity_max, faster disks). The page uses the minimum, 8 MB, so a few bulk loads fill it. Demo: checkpoint age near capacity: each bulk load adds 2 MB, adaptive flushing removes 0.5 MB, and the fifth one stalls.

Run SHOW ENGINE INNODB STATUS to see the same numbers: in the LOG section, Log sequence number, Log flushed up to, Pages flushed up to and Last checkpoint at.

Compared with PostgreSQL

PostgreSQL has the same design with other names: WAL records instead of redo records, WAL buffers instead of the log buffer, pd_lsn on each page instead of FIL_PAGE_LSN (see PostgreSQL WAL). The checkpoint works differently (see PostgreSQL Checkpoints).

	InnoDB	PostgreSQL
Log size	fixed circle, `innodb_redo_log_capacity`	segments in `pg_wal`, soft limit `max_wal_size`
Checkpoint	fuzzy, continuous: oldest dirty page's LSN	periodic: REDO point, then write every dirty page (spread over `checkpoint_completion_target`)
When the log fills	sync flush, writers stall	an earlier checkpoint; WAL can grow past `max_wal_size`
Durability knob	`innodb_flush_log_at_trx_commit` 0 / 1 / 2	`synchronous_commit` off / local / on
Torn pages	doublewrite buffer	full-page images in the WAL

Crash recovery, briefly

After a crash InnoDB reads the checkpoint LSN from the redo log header, scans the redo forward from there and applies each record to its page if the page's FIL_PAGE_LSN is older than the record. Then transactions with no commit record are rolled back from their undo logs, and the rollback writes redo of its own. The page shows this in a few steps; InnoDB Crash Recovery shows it in detail.

What the page leaves out

The 512-byte log blocks with headers and checksums, the recent_written and recent_closed buffers that let mtrs copy into the log buffer in parallel and the "dirty pages may be added slightly out of order" lag they cause; separate undo-log mtrs; spinning and waiting tuning (innodb_log_spin_cpu_abs_lwm, innodb_log_wait_for_flush_spin_hwm); the log buffer filling up and forcing a write; multiple buffer pool instances with a flush list each; LRU flushing; redo log archiving and encryption, ALTER INSTANCE DISABLE INNODB REDO_LOG; the binlog two-phase commit. Here a row lives in its own page and a bulk load is one block of 2 MB.