Why Our Database Froze for 42 Seconds: Diagnosing InnoDB Furious Flushing and Redo Log Starvation

At 03:14 UTC during a heavy settlement reconciliation run, our primary database instance stopped processing writes. Applications timed out across three availability zones, connection pools saturated within six seconds, and application threads stacked up on connection handshakes. The hardware was nowhere near CPU exhaustion, and our PCIe 5.0 enterprise NVMe array reported negligible read wait times. Yet for 42 seconds, MySQL refused to commit a single transaction. The culprit was not a deadlocked query, a split-brain failover, or network partition. The storage engine had hit the InnoDB synchronous redo log checkpoint boundary, triggering an engine-wide emergency halt known historically as 'furious flushing'. Here is how we debugged the incident, the math behind InnoDB checkpoint lag, and the low-level Linux and database engine settings required to prevent write-ahead log exhaustion under sustained high-throughput workloads.

The Incident: Symptom and Triage

Our ingest pipeline was processing a burst of roughly 85,000 write IOPS consisting of large multi-row INSERT ... ON DUPLICATE KEY UPDATE statements across several partitioned accounting tables. Metrics graphs painted a severe picture:

Active database client connections jumped from 140 to the hard limit of 5,000 in under 8 seconds.
Query throughput (com_commit and queries/sec) flatlined to zero.
Virtual memory and CPU usage dropped significantly, indicating all active worker threads were suspended in wait states.
Linux kernel disk utilization (iostat -xz 1) reported disk writes hitting 1.8 GB/s with await spiking over 210ms on modern U.2 NVMe drives.

Running SHOW PROCESSLIST yielded hundreds of threads stalled in state Waiting for buffer pool mutex or updating. Capturing a stack trace with gdb and checking performance_schema wait events pointed directly to the internal log coordinator:

SQL

SELECT event_name, count_star, sum_timer_wait/1000000000000 AS total_wait_sec
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE '%innodb/log%'
ORDER BY total_wait_sec DESC LIMIT 5;

The output confirmed that threads were waiting on wait/synch/mutex/innodb/log_free_check_mutex and wait/synch/mutex/innodb/buf_pool_mutex. The storage engine had suspended client threads because it had run out of space in the circular redo log to record incoming changes.

Anatomy of the InnoDB Redo Log and Checkpointing

To understand why MySQL stopped serving traffic, we have to look at how InnoDB manages the Log Sequence Number (LSN). Every byte written to the write-ahead log increments the LSN monotonically. Modifications exist in three places:

Log Buffer (Memory): Incoming changes written as redo log records.
Redo Log Files (Disk): Flushed sequentially to guarantee ACID durability before committing.
Buffer Pool Pages (Memory): Data pages updated in memory, now marked 'dirty' and queued for eventual write-back to the actual tablespace .ibd files.

The critical metric is Checkpoint Age, which is the distance between the current LSN and the Last Checkpoint LSN (the point up to which all dirty pages modified before that LSN have been completely synced to disk): $$\text{Checkpoint Age} = \text{Log Sequence Number} - \text{Last Checkpoint at}$$ InnoDB maintains a circular ring buffer inside its redo log files (managed via innodb_redo_log_capacity in MySQL 8.0.30+ and 8.4 LTS). Because space is finite, InnoDB cannot overwrite older redo log records until the corresponding dirty buffer pool pages are safely written and fsynced to tablespace files on disk.

The Flushing Thresholds

InnoDB classifies flush aggression based on the percentage of total redo log capacity consumed by the Checkpoint Age:

Threshold State	Capacity Percent	Engine Behavior	Impact on Latency
Idle / Normal	under 50%	Background `page_cleaner` flushes dirty pages smoothly based on `innodb_io_capacity`.	None. Write throughput is uninhibited.
Adaptive Flush	70% to 75%	InnoDB ramps up page cleaner threads dynamically based on write velocity (`innodb_adaptive_flushing`).	Negligible P95 increase. Background I/O climbs.
Async Flush	75% to 85%	Page cleaners flush aggressively to hold the line, prioritizing checkpoint progression over background tasks.	P99 latency degrades significantly.
Sync Flush (Panic)	85% to 87.5%+	Engine-wide stall. Client threads attempting to write are forced into sleep loops while all I/O capacity is seized to flush dirty pages.	Complete freeze (P99/P999 spikes into tens of seconds).

In our environment, the incoming write velocity was pushing 450 MB/s of raw binary transaction delta. The background page cleaners were incapable of flushing dirty pages to the fragmented tablespaces fast enough to advance the Last Checkpoint at LSN. The checkpoint age crossed the 85% hard ceiling, and the engine hit the synchronous emergency brake.

Dissecting the Evidence via Engine Status

During the freeze, executing SHOW ENGINE INNODB STATUS\G revealed the checkpoint gap:

TEXT

--- 
LOG
---
Log sequence number          18940283419024
Log flushed up to            18940282901402
Pages flushed up to          18938135892110
Last checkpoint at           18932400192840
0 pending log flushes, 0 pending chkp writes
1481023 log i/o's done, 184.20 log i/o's/second

Let us compute the checkpoint age manually from these numbers:

Log Sequence Number: 18,940,283,419,024
Last Checkpoint at: 18,932,400,192,840
Delta: 7,883,226,184 bytes (~7.34 GB)

At the time, innodb_redo_log_capacity was set to its default of 8589934592 (8 GB). $$\frac{7.34\text{ GB}}{8.00\text{ GB}} = 91.75%$$ The checkpoint age was consuming 91.75% of the total configured capacity. The server was pinned deep inside the synchronous flush zone. Page cleaners were desperately writing random 16KB dirty pages across dozens of tablespace files to allow the checkpoint pointer to advance, while every query worker was stuck in a retry spin-lock inside log_free_check().

The Block Layer Bottleneck: Linux I/O Misalignment

Why were the page cleaners falling behind if we were running fast NVMe hardware capable of 800,000 random write IOPS? We hooked bpftrace into the block layer to observe block dispatch latency:

BASH

bpftrace -e 'kprobe:blk_account_io_done { @[kstack] = count(); }'

We discovered three underlying structural bottlenecks:

1. The Doublewrite Buffer Contention

To guard against partial page writes (torn pages), InnoDB writes dirty pages to the Doublewrite Buffer (dblwr) before writing them to the actual data files. While innodb_doublewrite_pages was batching sequentially, the target tablespace writes were completely randomized across our 2TB dataset. This created severe contention inside the Linux block queue.

2. Under-Configured I/O Capacity

Our MySQL configuration had inherited conservative settings:

INI

innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

Because innodb_io_capacity_max was clamped to 4,000 IOPS, the InnoDB adaptive flushing algorithm capped the number of dirty pages the page cleaner threads were allowed to dispatch per second, even though our hardware was capable of handling 50,000+ random writes comfortably. InnoDB artificially starved its own background threads until it was forced into panic mode.

3. Linux Kernel Dirty Page Writeback Conflict

The Linux kernel page cache was also buffering writes and triggering its own internal background writeback at irregular intervals. When vm.dirty_ratio was crossed, kernel threads (kworker/flush) took over the disk queues, elevating block-layer latency (await) and starving MySQL asynchronous I/O (io_submit) requests.

Remediation and Low-Level Tuning

To eliminate synchronous checkpoint halts, we adjusted the database architecture across three tiers: log capacity sizing, engine flushing behavior, and Linux kernel block scheduling.

1. Scaling Redo Log Capacity

In modern MySQL, dynamic redo log resizing can be performed without a restart:

SQL

-- Expand redo log capacity to 64GB to allow deep write buffers during bursts
SET GLOBAL innodb_redo_log_capacity = 68719476736;

A 64GB log capacity provides a 48GB safe zone before adaptive flushing shifts to async mode, allowing the engine to absorb high-velocity ingest bursts for up to 15 minutes without stalling client threads.

2. Calibrating Page Cleaners and I/O Limits

We matched innodb_io_capacity to the actual capabilities of the underlying NVMe storage, and increased the cleaner thread pool so that individual threads do not become bottlenecks on busy buffer pool instances:

INI

# my.cnf
innodb_buffer_pool_size = 137438953472      # 128 GB
innodb_buffer_pool_instances = 16
innodb_page_cleaners = 16                  # Match 1:1 with buffer pool instances

# Unlock NVMe throughput
innodb_io_capacity = 40000
innodb_io_capacity_max = 80000

# Modern storage does not benefit from neighbor flushing (which groups contiguous pages)
innodb_flush_neighbors = 0

# Adjust checkpoint triggers
innodb_adaptive_flushing = ON
innodb_adaptive_flushing_lwm = 65.0        # Start gentle flushing at 65% capacity
innodb_max_dirty_pages_pct = 70.0          # Target ceiling for dirty pages
innodb_max_dirty_pages_pct_lwm = 50.0      # Start proactive flushing early

Setting innodb_flush_neighbors = 0 is critical for solid-state storage. On spinning disks, flushing contiguous pages reduces head movement. On NVMe drives, this creates unnecessary write amplification and stalls the cleaner threads.

3. Linux Kernel and Storage Layer Optimization

To prevent the OS page cache from competing with the database engine, we configured direct I/O and tuned kernel writeback policies in /etc/sysctl.d/99-database.conf:

TEXT

# Force proactive background flushing by kernel workers
vm.dirty_background_ratio = 3
vm.dirty_ratio = 10

# Avoid page cache swapping pressure
vm.swappiness = 1

# Ensure maximum direct I/O concurrency
fs.aio-max-nr = 1048576

Apply the changes immediately:

BASH

sysctl -p /etc/sysctl.d/99-database.conf

Next, ensure that the storage block device uses an appropriate I/O scheduler. On fast multi-queue NVMe drives, the Linux none (noop) scheduler bypasses kernel queuing overhead, allowing the device controller to manage parallelism directly:

BASH

# Verify current scheduler
cat /sys/block/nvme0n1/queue/scheduler

# Set scheduler to none
echo 'none' > /sys/block/nvme0n1/queue/scheduler

Finally, verify that MySQL uses O_DIRECT to write data files, bypassing the double-caching overhead of the OS page cache entirely:

INI

innodb_flush_method = O_DIRECT

Verification and Continuous Monitoring

To ensure our new headroom was functioning as intended, we simulated the settlement pipeline workload in our staging environment at 120,000 IOPS and ran this continuous telemetry monitor:

BASH

#!/usr/bin/env bash
# innodb_checkpoint_monitor.sh

mysql -u root -e "
SELECT 
  VARIABLE_NAME, 
  VARIABLE_VALUE 
FROM performance_schema.global_status 
WHERE VARIABLE_NAME IN (
  'Innodb_buffer_pool_pages_dirty',
  'Innodb_buffer_pool_pages_total',
  'Innodb_os_log_written'
);" -N

We also calculated Checkpoint Age in real-time via SQL using the performance_schema tables:

SQL

SELECT 
  NOW() AS sample_time,
  lsn.variable_value AS current_lsn,
  chk.variable_value AS checkpoint_lsn,
  ROUND((lsn.variable_value - chk.variable_value) / 1024 / 1024, 2) AS checkpoint_age_mb,
  ROUND(((lsn.variable_value - chk.variable_value) / @@innodb_redo_log_capacity) * 100, 2) AS capacity_pct
FROM 
  performance_schema.global_status lsn
JOIN 
  performance_schema.global_status chk 
    ON chk.variable_name = 'Innodb_log_checkpoint_lsn'
WHERE 
  lsn.variable_name = 'Innodb_log_sequence_number';

Under the tuned configuration, our maximum checkpoint age peaked at 38.4% of redo log capacity during the heaviest phases of ingest. Adaptive flushing kicked in smoothly without causing lock contention, and the 42-second connection drop-offs were eliminated.

Summary of Operational Takeaways

Redo Log Capacity is an I/O Dam: Sizing the redo log too small converts high-throughput asynchronous writes into blocking synchronous operations. Allocate enough capacity to absorb your longest write bursts.
Align Engine Capacities to Real Hardware: Running default innodb_io_capacity values on NVMe devices causes self-inflicted engine throttles. Push limits to reflect real array throughput.
Decouple Page Cleaners: Match innodb_page_cleaners to innodb_buffer_pool_instances so that parallel pool segments do not block each other during flush phases.
Turn Off Flush Neighbors on SSD/NVMe: Setting innodb_flush_neighbors = 0 eliminates write amplification and prevents cleaner threads from doing extra work for no benefit.