InnoDB Checkpoint Age and Redo Log Sizing
The InnoDB checkpoint age — the difference between the current Log Sequence Number (LSN) and the last checkpoint LSN — measures how much redo log data would need to be replayed if MySQL crashed right now. When the checkpoint age approaches the total redo log capacity, InnoDB triggers a synchronous "sharp checkpoint" that stalls all writes while dirty pages are flushed to disk. This stall can last hundreds of milliseconds, causing sudden latency spikes visible as periodic slowdowns even when average throughput looks healthy. The LOG section of SHOW ENGINE INNODB STATUS reports the log sequence number, last checkpoint at, and log flushed up to, giving you everything needed to calculate checkpoint age as a percentage of redo capacity. This guide explains how to size redo logs correctly for your workload and tune adaptive flushing to prevent checkpoint stalls.
Understanding LSN and Checkpoint Mechanics
Log Sequence Number: What It Is and How It Advances
The Log Sequence Number (LSN) is a monotonically increasing 64-bit integer that identifies every byte position in the InnoDB redo log stream. Each write operation advances the LSN by the number of bytes written to the redo log. The LSN advance rate — computed as (LSN_t2 - LSN_t1) / (t2 - t1) in bytes per second — is a direct measure of your MySQL server's write throughput. A server writing 100 MB/s of redo log data will exhaust a 512 MB redo log file in about 5 seconds if the checkpoint does not keep pace. Use SELECT @@innodb_log_file_size * @@innodb_log_files_in_group AS total_redo_bytes to check your current redo log capacity, then compare it to the LSN advance rate to determine the maximum time between checkpoints.
Last Checkpoint At vs Log Flushed Up To: The Gap
The LOG section shows three LSN values: "Log sequence number" (current write position), "Log flushed up to" (data durably written to the redo log file on disk), and "Last checkpoint at" (the LSN up to which all dirty buffer pool pages have been flushed to their tablespace files). The gap between "Log sequence number" and "Last checkpoint at" is the checkpoint age. The gap between "Log sequence number" and "Log flushed up to" represents uncommitted redo data still in the redo log buffer — this gap should be small (< innodb_log_buffer_size) under normal operation. If "Log flushed up to" falls far behind "Log sequence number", the redo log fsync path is bottlenecked, typically indicating I/O saturation on the redo log device.
Sizing Redo Logs for Your Workload
innodb_log_file_size and innodb_log_files_in_group (MySQL 5.7)
In MySQL 5.7 and earlier, redo log capacity = innodb_log_file_size × innodb_log_files_in_group. The default 48 MB total (2 × 48 MB files) is wildly undersized for any production workload — most production servers should have 1–4 GB of total redo log capacity. Changing innodb_log_file_size requires a clean server shutdown, deleting the existing ib_logfile* files, and restarting — an online change is not supported in MySQL 5.7. A commonly recommended sizing formula is: target_redo_capacity = LSN_advance_rate_bytes_per_second × 3600 (covering one hour of writes). This ensures the checkpoint mechanism has a full hour to flush dirty pages before the log wraps, even under write bursts. Avoid making redo logs excessively large (> 8 GB per file) as it increases crash recovery time.
innodb_redo_log_capacity in MySQL 8.0.30+ and Automatic Sizing
MySQL 8.0.30+ introduces innodb_redo_log_capacity, replacing the innodb_log_file_size × innodb_log_files_in_group pair with a single capacity setting. More importantly, the redo log can now be resized online without a server restart: SET GLOBAL innodb_redo_log_capacity=4294967296 resizes the redo log to 4 GB atomically. InnoDB automatically manages the number and size of individual redo log files within the capacity limit, storing them in the #innodb_redo/ directory. The capacity can be increased or decreased online, though decreasing requires InnoDB to first advance the checkpoint to the new capacity boundary, which may stall writes briefly. Check the current redo log capacity and utilisation with SELECT * FROM performance_schema.innodb_redo_log_files.
Monitoring and Alerting on Checkpoint Age
Checkpoint Age as % of Redo Log Capacity
The checkpoint age percentage — (LSN - last_checkpoint_at) / total_redo_capacity × 100 — is the key metric for assessing redo log health. At 75% checkpoint age, InnoDB triggers fuzzy checkpointing, which continuously flushes dirty pages in the background. At 90%, adaptive flushing intensifies significantly. At approximately 95%, InnoDB triggers a synchronous sharp checkpoint, blocking all new writes until the checkpoint advances. Alert at 60% checkpoint age to give operations teams time to investigate before the 75% fuzzy checkpoint trigger; page on at 80%. Monitor this metric with SELECT (lsn - last_checkpoint) / (@@innodb_log_file_size * @@innodb_log_files_in_group) AS checkpoint_age_pct FROM (SELECT variable_value AS lsn FROM performance_schema.global_status WHERE variable_name='Innodb_lsn_current') a, (SELECT variable_value AS last_checkpoint FROM performance_schema.global_status WHERE variable_name='Innodb_lsn_last_checkpoint') b.
Adaptive Flushing and innodb_adaptive_flushing_lwm
Adaptive flushing, controlled by innodb_adaptive_flushing=ON (default), dynamically adjusts the dirty page flush rate based on the current redo log generation rate and the checkpoint age. The flush rate is calculated by the Wos (write-optimised strategy) algorithm to keep the checkpoint age below 75% of redo log capacity continuously. innodb_adaptive_flushing_lwm (low watermark, default 10%) sets the checkpoint age percentage below which adaptive flushing is inactive, relying on the standard background flush rate instead. innodb_io_capacity and innodb_io_capacity_max are the actual levers that cap how aggressively the page cleaner flushes: set innodb_io_capacity to 80% of your storage's measured random write IOPS, and innodb_io_capacity_max to 100%, allowing burst flushing during checkpoint pressure.