Understanding InnoDB "Pages flushed up to" and Its Relation to the Last Checkpoint
This article explains the meaning of the 'Pages flushed up to' value in MySQL's InnoDB engine, how it differs from the 'Last checkpoint' LSN, the underlying logic for its calculation, and provides test results demonstrating its behavior during writes and checkpoints.
LSN
In the output of show engine innodb status , several LSN-related fields appear: Log sequence number, Log flushed up to, Pages flushed up to, and Last checkpoint at.
Log sequence number is the highest redo‑log sequence number generated by data‑modifying operations.
Log flushed up to is the LSN of the redo log that has already been written to disk.
Pages flushed up to is the focus of this article; it is not simply the LSN of dirty pages flushed to disk.
Last checkpoint at marks the position of the most recent checkpoint.
Meaning of "Pages flushed up to"
According to source‑code analysis, InnoDB selects the maximum oldest modification LSN among all buffer‑pool instances’ flush lists; if that value is zero (no dirty pages), it falls back to the current system LSN (the Log sequence number).
LSN update logic
The value is used as the end point for the next checkpoint. In a running MySQL instance, the system LSN grows continuously with each write, while Pages flushed up to is updated at a lower frequency, usually lagging behind the system LSN. During each checkpoint the master thread writes a MLOG_CHECKPOINT record (9 bytes), which advances the system LSN by 9; after flushing all dirty pages the flush list becomes empty, so Pages flushed up to equals the system LSN and is therefore 9 greater than Last checkpoint at.
Test verification
By opening a transaction and inserting a row without committing, the following observations were made:
Log sequence number increased.
Log flushed up to remained unchanged (redo log not yet flushed).
Pages flushed up to remained unchanged because the value is not updated in real time.
Last checkpoint point remained unchanged.
After a few seconds, all values changed; the first three values became equal, and Last checkpoint point was 9 smaller, confirming the described behavior.
When continuous writes occur, Last checkpoint point can equal Pages flushed up to immediately after a checkpoint, before a new value is fetched.
Aikesheng Open Source Community
The Aikesheng Open Source Community provides stable, enterprise‑grade MySQL open‑source tools and services, releases a premium open‑source component each year (1024), and continuously operates and maintains them.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.