The most expensive tuning change I ever reviewed looked free. A fast NVMe box, a well-meaning engineer, and one line: innodb_io_capacity_max=20000, because the disk can do it. Latency was fine for weeks. Then a batch window arrived, the page cleaner finally had a reason to spend its new budget, and the buffer pool hit ratio slid a couple of points while read IOPS climbed. Nothing was broken. InnoDB was simply flushing and evicting far more aggressively than the workload needed, pushing warm pages out of memory to make room for write work the redo log never demanded. The disk could do 20000. The workload needed about 1500.
The mental model that prevents this: innodb_io_capacity is a budget for background maintenance, not a performance dial. Here is what it actually feeds, what adaptive flushing modulates on its own, where the checkpoint-age panic cliff lives, why oversizing the budget backfires even on fast storage, and the counters that tell you which side of the line you are on.
What does innodb_io_capacity actually control?
innodb_io_capacity tells InnoDB how many I/O operations per second it may spend on background work: flushing dirty pages from the buffer pool and merging the change buffer. innodb_io_capacity_max is the burst ceiling for moments when flushing falls behind and needs to catch up. Neither touches the foreground directly: raising them never makes a commit or a read faster, it only permits InnoDB to spend more of the device's attention on housekeeping. The defaults, 200 and 2000, date from the era when a server disk was a stack of spinning platters good for a couple of hundred IOPS. Left at defaults on modern hardware, InnoDB chronically under-spends and dirty pages pile up until something forces the issue; cranked to device maximum, it over-spends and you get the story above. The useful range is narrower than the allowed range, and it is set by your workload's redo generation, not by your storage vendor's spec sheet.
What does adaptive flushing actually modulate?
With innodb_adaptive_flushing=ON, the default, the page cleaner does not flush at a fixed rate. It tracks the smoothed rate at which redo is being generated and aims to flush dirty pages fast enough that the oldest dirty page never falls dangerously far behind the current log position. The dirty-page percentage tempers that target: innodb_max_dirty_pages_pct defaults to 90 as the hard ceiling, and innodb_max_dirty_pages_pct_lwm defaults to 10 as the soft floor where gentle preflushing begins, so the steady-state dirty ratio is kept comfortably below the cap. One more low-water mark, innodb_adaptive_flushing_lwm at 10 percent of redo capacity, keeps adaptive flushing quiet entirely when the log is barely being used. The design goal is worth remembering: flush ahead of the redo wave, a little all the time, instead of behind it in a panic.
Where is the checkpoint-age panic cliff?
Checkpoint age is the distance between the current log sequence number and the LSN of the oldest page modification not yet flushed, and it is bounded by your total redo capacity, innodb_redo_log_capacity since MySQL 8.0.30, default 100 MB, which replaced the old log file size arithmetic. InnoDB cannot let that age reach the limit, because redo must always be replayable from the oldest dirty page. So it escalates in stages: adaptive flushing keeps the age small in normal operation, and as the age climbs past roughly three quarters of capacity InnoDB flushes aggressively, and near seven eighths it switches to synchronous preflush, where user threads effectively wait for flushing before they may generate more redo. That is the stall everyone describes as the database freezing for a few seconds under write bursts. A healthy system lives far to the left of that cliff at all times; a system that touches it regularly has a redo sizing problem first and a tuning problem second. Sizing the log itself is covered in redo log capacity and checkpoint stalls.
Why can cranking io_capacity on NVMe backfire?
The mechanism is the buffer pool LRU, not the device. When the cleaner is allowed to flush hard, it scans the LRU tail, innodb_lru_scan_depth pages deep per buffer pool instance, and whatever sits near the tail is a candidate for eviction, dirty pages flushed and clean pages simply dropped. A budget far above the workload's redo-driven need means the cleaner keeps spending because it can: pages get flushed early, before they accumulate more changes, then get dirtied again and written again, and every one of those writes is doubled by the doublewrite buffer described in the doublewrite and torn pages note. Meanwhile the evicted clean pages were not cold, they were merely less hot, and your read hit ratio pays for their absence. On a cloud volume, the flush traffic also shares the device's IOPS and throughput budget with your foreground reads, so 20000 pages per second of flushing is roughly 320 MB per second of write traffic competing with queries. NVMe makes the number affordable and the waste invisible, which is exactly why the trap springs there. The buffer pool side of this trade, how much memory the working set actually needs, is the subject of the buffer pool sizing field guide.
Which counters show flushing health?
Start with checkpoint age, computed from performance_schema.log_status, whose STORAGE_ENGINES column carries InnoDB's current and checkpoint LSNs as JSON:
SELECT JSON_EXTRACT(storage_engines, '$.InnoDB.LSN') AS current_lsn,
JSON_EXTRACT(storage_engines, '$.InnoDB.LSN_checkpoint') AS checkpoint_lsn
FROM performance_schema.log_status;Subtract, divide by @@innodb_redo_log_capacity, and you have the single most important flushing number on the server: the fraction of the panic cliff you are currently consuming. Next, the dirty-page ratio from Innodb_buffer_pool_pages_dirty over Innodb_buffer_pool_pages_total in performance_schema.global_status, which should idle well under the 90 percent cap. For the fine detail, INNODB_METRICS carries the buffer_flush family; enable the adaptive module and read the counters:
SET GLOBAL innodb_monitor_enable = 'buffer_flush_adaptive';
SELECT name, count, status
FROM information_schema.innodb_metrics
WHERE name IN ('buffer_flush_adaptive_total_pages',
'buffer_flush_avg_page_rate',
'buffer_flush_n_to_flush_by_age',
'buffer_flush_sync_pages');The names to internalize: buffer_flush_avg_page_rate is the server's own realized flush rate, with buffer_flush_adaptive_total_pages as the cumulative workhorse you diff between scrapes; buffer_flush_n_to_flush_by_age is how many pages the redo age is currently demanding; and the sync-flush counters are the smoking gun, because sync flushes only happen at the cliff — buffer_flush_sync_pages counts the pages of the latest batch, and buffer_flush_sync_total_pages keeps the running evidence. If those move, no io_capacity value will save you; the redo capacity or the write burst is the real problem.
What are sane values per storage class?
Positions, from fleets I have run, not from documentation. On a cloud volume provisioned around 3000 IOPS, start at innodb_io_capacity=800 to 1200 with max near 2000, because flushing shares that budget with foreground reads and the bill for overspending is latency, not just throughput. On local SATA SSDs, 1000 with a max of 3000 is a comfortable default. On local NVMe, 2000 to 4000 with a max of 6000 to 8000 covers most write-heavy workloads; I have rarely seen a redo generation rate that justifies more, and I have never seen one that justified the 20000 from my opening story. Keep the max at two to four times the base, and after any change, re-measure: watch buffer_flush_adaptive_total_pages and the checkpoint-age fraction for a week, then adjust. And one adjacent setting worth checking while you are here: innodb_flush_neighbors defaults to 0, which is correct for SSD and NVMe, so if a legacy config sets it to 1, remove that before judging flush behavior.
Where MonPG stands on MySQL
Full disclosure, since I build it: MonPG monitors PostgreSQL today, and MySQL support is still in active development, coming soon rather than shipping. The flushing pipeline in this note is exactly the kind of system the MySQL work is meant to graph end to end: checkpoint-age fraction, dirty-page ratio, realized flush rate, and sync-flush events side by side, so a mis-set budget shows up as a picture instead of a postmortem. The MySQL monitoring (coming soon) page is where that work lands as it ships. Until then, the same buffer-pool and WAL-side philosophy already runs on the PostgreSQL side, and the rest of these MySQL field notes are on the blog.