Anatomy of an InnoDB Write Stall: Checkpoint Age Thresholds and Synchronous Flush Storms

During high-throughput ingestion spikes, database architectures that rely on Write-Ahead Logging (WAL) can exhibit a deceptive failure mode. For minutes, transactional commits succeed with sub-millisecond latencies as mutations append to in-memory buffers and sequential log files. Then, without an apparent increase in query volume, the engine hits a performance cliff. Latency spikes by three orders of magnitude, connection pools max out, and active database worker threads stop executing SQL to battle for I/O locks. As highlighted in a production teardown by the JusDB engineering team regarding redo log sizing under burst traffic, this cliff is rarely caused by CPU saturation or table-level locks. Instead, it is the classic signature of an InnoDB write stall: the storage engine's checkpoint age has breached its hard synchronous threshold, forcing client connection threads to stop accepting transactions and immediately flush dirty pages from the buffer pool to disk.

The Circular Ring Buffer and LSN Mechanics

To decouple transaction durability from the high cost of random disk I/O, InnoDB logs state changes sequentially to its redo log before modifying the primary data pages residing in the innodb_buffer_pool. Every byte written to this log advances a monotonically increasing 64-bit integer: the Log Sequence Number (LSN). At any point in time, InnoDB tracks three vital offsets along this sequence:

log_sys->lsn: The latest mutation byte produced in memory.
log_sys->flushed_to_disk_lsn: The point to which the redo log itself has been fsynced into durable storage.
log_sys->last_checkpoint_lsn: The point in the log sequence before which all modified data pages in the buffer pool have been completely written and flushed to their respective .ibd tablespace files.

The difference between the current LSN and the last checkpoint LSN is the checkpoint age: Checkpoint Age = Current LSN - Last Checkpoint LSN Because the redo log has a fixed total capacity—historically split across static files via innodb_log_file_size * innodb_log_files_in_group, and managed dynamically in MySQL 8.0.30+ as innodb_redo_log_capacity—it acts as a circular ring buffer. As long as Checkpoint Age stays comfortably below the total log capacity, the ring buffer can overwrite historical log segments during crash recovery. But when Checkpoint Age approaches the physical limit of the log space, InnoDB is mechanically prohibited from appending new log entries. Overwriting an uncheckpointed LSN would destroy crash recovery guarantees for unwritten dirty pages.

The Watermark Cliff: From Async Scrubbing to Worker Conscription

InnoDB regulates dirty page flushing through two primary thresholds calculated against log capacity:

The Async Flush Target (Soft Watermark, ~75% Capacity): At this boundary, InnoDB's background page cleaner threads (srv_page_cleaner) accelerate page flushing. The engine attempts adaptive flushing based on the dirty page ratio and the current LSN generation rate, aiming to advance last_checkpoint_lsn before foreground throughput is impacted.
The Sync Flush Boundary (Hard Watermark, ~85-90% Capacity): If incoming write bandwidth exceeds the writeback rate of the storage subsystem, the async cleaners fall behind. Once the checkpoint age crosses the synchronous flush threshold, background flushing is deemed insufficient.

At this moment, the engine triggers log_preflush_pool_until_checkpoint(). Rather than allowing background page cleaner threads to handle the backlog, MySQL halts client worker threads trying to commit transactions and conscripts them into synchronous flush workers. Every frontend application query thread that attempts to modify data is forced to scan the buffer pool flush lists, acquire buffer pool page mutexes, issue synchronous page writes to NVMe storage, and wait for the I/O completion. Transactional concurrency evaporates instantly. Applications configured with high connection limits experience thread pool deadlocks as every worker becomes trapped waiting on device queue completions.

SQL

-- Measuring real-time checkpoint pressure via Performance Schema
SELECT 
  variable_value INTO @cur_lsn 
FROM performance_schema.global_status 
WHERE variable_name = 'Innodb_redo_log_current_lsn';

SELECT 
  variable_value INTO @chk_lsn 
FROM performance_schema.global_status 
WHERE variable_name = 'Innodb_checkpoint_age';

-- Inspect the ratio of log consumption against total capacity
SELECT 
  @chk_lsn AS checkpoint_age_bytes,
  ROUND((@chk_lsn / @@innodb_redo_log_capacity) * 100, 2) AS checkpoint_capacity_pct;

Sizing Headroom for Peak Write Velocity

Historically, default MySQL installations shipped with an innodb_redo_log_capacity (or combined log file size) of just 100MB—an artifact of hardware constraints from two decades ago. In modern cloud and on-premises environments, a single bulk load or burst of write events can generate 100MB of log data in less than two seconds. A resilient production rule of thumb is to provision innodb_redo_log_capacity to hold between 60 and 90 minutes of your worst-case peak write throughput. You can calculate your delta write speed by polling Innodb_redo_log_current_lsn across a 60-second window during peak activity: Peak Redo Rate (MB/s) = (LSN_t60 - LSN_t0) / (60 * 1024 * 1024) If your systems generate 60 MB/s of redo log traffic during high-load periods, sizing the redo log at 32GB guarantees that the background page cleaner threads have ample buffer margin—nearly 9 minutes of sustained write bursts without advancing the checkpoint—before the synchronous flush cliff is ever approached.

INI

# /etc/mysql/mysql.conf.d/innodb.cnf
[mysqld]
# Modern dynamic redo log allocation (MySQL 8.0.30+)
innodb_redo_log_capacity = 34359738368 # 32GB

# Dynamic flushing behavior matching enterprise NVMe capabilities
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_flush_neighbors = 0
innodb_max_dirty_pages_pct_lwm = 50.0
innodb_max_dirty_pages_pct = 75.0

Mitigating the Double-Buffering Trap

Expanding innodb_redo_log_capacity absorbs transient spikes, but sustained writes eventually require matching physical disk write bandwidth. If background cleaners cannot drain dirty pages at the rate they are dirtied, stalls will recur. Several tuning parameters dictate how cleanly InnoDB writes back pages without stalling query execution:

Disable Neighbor Flushing on Solid-State Storage: Ensure innodb_flush_neighbors = 0. This legacy setting bundled contiguous dirty pages together for spinning disks, but on random-access SSDs, it introduces severe write amplification and ties up I/O queues unnecessarily.
Align innodb_io_capacity with Real Device Capabilities: Setting innodb_io_capacity too low (the default is often 200) prevents the page cleaner threads from using available controller queues, even when the NVMe drive can easily handle 50,000 random write IOPS.
Adjust Low Watermarks for Early Wakeup: Setting innodb_max_dirty_pages_pct_lwm to 50% forces page cleaners to begin gradual background flushes earlier, smoothing out write distributions instead of oscillating between periods of zero flushing and frantic sync storms.

By treating the redo log not just as an on-disk journal but as an I/O shock absorber, you keep client threads focused exclusively on query execution, isolating frontend latency from underlying storage write amplification.