Why does InnoDB flushing cause latency spikes when CPU is low?
Quick answer: The threads are waiting, and three different waits produce the same symptom: no clean page in the buffer pool, a checkpoint that has to advance before redo can be reused, and the redo fsync on your own commit. They have different evidence and different fixes, and the counter usually named for this,
Innodb_buffer_pool_wait_free, only sees the first of the three.
What is the problem?
Section titled “What is the problem?”InnoDB changes a page in memory and writes a record of the change to the redo log. The changed page is dirty: correct in the buffer pool, stale in the table file. A background thread, the page cleaner, writes dirty pages out later.
A commit does not wait for the table file. What it waits for depends on
innodb_flush_log_at_trx_commit, which is 1 by default, measured on stock
MySQL 8.0.46 and 8.4.11 containers on 2026-09-20. At 1 the commit waits for the
redo to be on disk. That is the first wait, and it is on the commit path.
The other two arrive later, when the deferred work catches up:
The buffer pool runs out of clean pages. A thread that needs a page has to put it somewhere, and it can only reuse a page that is clean. If none are, it waits for the cleaner.
The redo log has to be reusable. Redo can only be overwritten once the pages it describes are on disk. The distance between now and the last checkpoint is the checkpoint age, and as it grows InnoDB flushes harder to hold it down.
None of the three is visible as application CPU. The server’s own CPU is a
different matter: measured on a MySQL 8.4.11 container, 2026-09-20, docker stats read 186.64% at the peak of a stall on an 8 vCPU host, against 4.0% to
4.7% between runs. The waiting thread costs nothing; the flushing it is waiting
for is not free.
How do I confirm it?
Section titled “How do I confirm it?”Take five counters twice, a few seconds apart. The differences are what matter, along with the interval between samples:
SELECT VARIABLE_NAME, VARIABLE_VALUEFROM performance_schema.global_statusWHERE VARIABLE_NAME IN ( 'Innodb_buffer_pool_wait_free', 'Innodb_buffer_pool_pages_free', 'Innodb_buffer_pool_pages_dirty', 'Innodb_buffer_pool_pages_total', 'Innodb_log_waits');Innodb_buffer_pool_wait_free is commonly used for this, and the MySQL 8.4
manual defines it precisely: “When InnoDB needs to read or create a page and no
clean pages are available, InnoDB flushes some dirty pages first and waits for
that operation to finish. This counter counts instances of these waits”
(Server Status Variables,
read 2026-09-20). These are instances of waits, not threads. One session can
contribute many, so read the value as a rate and divide by the seconds between
your samples.
Innodb_buffer_pool_pages_free is the companion counter. A wait happens only
when there is no clean page to reuse, and free pages at zero with clean pages
available is ordinary. Measured on a MySQL 8.4.11 container, 2026-09-20, free
pages reached zero in six of nine runs and Innodb_buffer_pool_wait_free did
not move in any of the six. The one run where it did move is the run where free
pages did not reach zero: the page cleaner had been crippled, so the pages that
were free were not clean.
Which of the three you have
Section titled “Which of the three you have”| what you see | which wait | what changes it |
|---|---|---|
wait_free rising and pages_free near zero | no clean page to reuse | a larger buffer pool, or less page churn from scans and bulk writes |
wait_free flat, checkpoint age high against innodb_redo_log_capacity | checkpoint pressure | a larger redo capacity, or a lower sustained write rate |
wait_free flat, checkpoint age low, commit latency high | the redo fsync on the commit path | storage fsync latency, or a durability decision you make on purpose |
What it looks like, measured
Section titled “What it looks like, measured”On one MySQL 8.4.11 container, 2026-09-20, three concurrent sessions each ran 40 bulk inserts of 16,384 rows into a table with one secondary index, while a fourth session committed one row every 50 ms and timed itself. The workload was the same in every run. The runs were interleaved big, small, big, small, because this host drifts enough to create a difference between two blocks of trials.
| pool and redo | writers elapsed | commit latency, average | peak checkpoint age |
|---|---|---|---|
| 4 GiB pool, 4 GiB redo | 15.197 s, 15.141 s | 13.879 ms, 13.873 ms | 13.4%, 13.2% |
| 128 MiB pool, 100 MiB redo | 25.780 s, 24.176 s | 27.389 ms, 23.167 ms | 82.4%, 80.4% |
The worst single commit does not separate them. On that same MySQL 8.4.11 container, 2026-09-20, the worst was 836.448 ms in a small-pool run and the second worst 652.380 ms in a big-pool run. On this host, the maximum is noise and the average is the signal. Quote the average and the interval, because the maximum mostly reflects the host.
Innodb_buffer_pool_wait_free read 550 before the first of those four runs and
550 after the last.
The counter that did not move
Section titled “The counter that did not move”A zero reading means something only once you know what makes the counter move.
| run, all on a MySQL 8.4.11 container on 2026-09-20 | wait_free | peak dirty pages | worst commit |
|---|---|---|---|
| bulk load, stock settings | 0 | 7,318 of 8,192 | 463 ms |
| random updates, 8 sessions, stock | 0 | 49 | 235 ms |
bulk load, innodb_io_capacity 100 | 0 | 6,768 of 8,192 | 1,138 ms |
| random updates, 12 sessions, cleaner crippled | 0 | 49 | 108 ms |
| bulk load, cleaner crippled | 0 to 541 | 7,649 of 8,192 | 177 ms |
The last row is the control for the other four rows and for the four interleaved
runs in the table above. The same counter, read by the same sampler on the same
server, moved as soon as innodb_io_capacity, innodb_io_capacity_max and
innodb_lru_scan_depth were cut to 100 and early flushing was turned off. Its
flat reading in the other eight runs therefore means that the instrument did
not report the waits.
The worst stall in the set, 1.138 seconds on that MySQL 8.4.11 container on 2026-09-20, is in a row where the counter never moved. An alert based only on this counter will miss most of what this page describes.
Innodb_log_waits answers one narrow question: whether a session had to wait for
log buffer space before it could write redo. Zero rules that out and rules out
nothing else, including the commit fsync below.
Checkpoint age, from the status output
Section titled “Checkpoint age, from the status output”SHOW ENGINE INNODB STATUS prints a LOG section. Match the labels rather than
counting lines: on MySQL 8.4.11, 2026-09-20, Last checkpoint at is eight lines
below the LOG heading, and a script that read six lines missed it.
Log sequence number 1935950933Last checkpoint at 1848558723Checkpoint age is the first value minus the second, compared with
innodb_redo_log_capacity. Those two readings, from that MySQL 8.4.11
container on 2026-09-20, are 87,392,210 bytes of a 104,857,600 byte capacity,
which is 83.3%.
Across five runs on that MySQL 8.4.11 container, 2026-09-20, the age never went above 83.3% of capacity, and three consecutive samples in the hardest run read 87,391,707, 87,392,210 and 87,391,797, which is a ceiling rather than a climb. The server holds the age down, and what a client feels at that point is the flushing that holds it.
Do not alert on a fixed percentage. Alert on the age rising and staying up while
commit latency rises with it. The MySQL 8.4 manual’s Redo Log page describes the
mechanism without bands: “If the redo log files occupy more space than the
specified value, dirty pages are flushed more aggressively”
(Redo Log, read
2026-09-20). The manual does name two redo thresholds elsewhere:
innodb_adaptive_flushing_lwm, default 10, is the “percentage of redo log
capacity at which adaptive flushing is enabled”
(InnoDB options,
read 2026-10-02), and 75% redo log utilization is
“the hardcoded limit at which asynchronous flushing starts”
(buffer pool flushing,
read 2026-10-02).
The commit path, which none of this sees
Section titled “The commit path, which none of this sees”The buffer pool can be eight times your working set and the redo can be nearly empty, and writes can still be slow, because a durable commit waits for an fsync.
Measured on that MySQL 8.4.11 container, 2026-09-20, with a 4 GiB buffer pool, a 4 GiB redo and no bulk load running, one session committing a single row at a time, interleaved 1, 2, 1, 2:
innodb_flush_log_at_trx_commit | median commit | average |
|---|---|---|
| 1 | 6.540 ms, 6.516 ms | 6.777 ms, 6.801 ms |
| 2 | 4.499 ms, 4.624 ms | 4.794 ms, 5.560 ms |
About 2 ms of every commit was the redo fsync on that MySQL 8.4.11 container on
2026-09-20, roughly 45% of the =2 median. The floor of about 4 ms is the
measuring procedure’s own cost, so the difference is the measurement and the
absolute values are not. Innodb_buffer_pool_wait_free
read 550 before and after all four runs.
Setting innodb_flush_log_at_trx_commit to 2 is a decision about losing
committed transactions in a crash, not a tuning knob. It appears here because it
shows where the time went.
The settings, and what they are for
Section titled “The settings, and what they are for”SHOW VARIABLES WHERE Variable_name IN ( 'innodb_redo_log_capacity', 'innodb_buffer_pool_size', 'innodb_dedicated_server', 'innodb_io_capacity', 'innodb_io_capacity_max', 'innodb_max_dirty_pages_pct', 'innodb_max_dirty_pages_pct_lwm', 'innodb_flush_log_at_trx_commit', 'innodb_adaptive_flushing', 'innodb_flush_neighbors', 'innodb_page_cleaners', 'innodb_lru_scan_depth');Read them on the server you are fixing. Defaults differ by version and by one option, measured on stock MySQL 8.0.46 and MySQL 8.4.11 containers on 2026-09-20:
| variable | MySQL 8.0.46 | MySQL 8.4.11 | 8.4.11 with --innodb-dedicated-server=ON |
|---|---|---|---|
innodb_buffer_pool_size | 134217728 | 134217728 | 49928994816 |
innodb_redo_log_capacity | 104857600 | 104857600 | 4294967296 |
innodb_io_capacity | 200 | 10000 | 10000 |
innodb_io_capacity_max | 2000 | 20000 | 20000 |
innodb_page_cleaners | 1 | 1 | 2 |
innodb_buffer_pool_size read 134217728 on stock MySQL 8.0.46 and MySQL
8.4.11 containers on 2026-09-20, so 128 MiB is MySQL’s own default rather than a
container artefact. With
--innodb-dedicated-server=ON the server derives it instead, and on that
2026-09-20 host of 62 GB and 8 vCPU it read 49,928,994,816 bytes and a redo
capacity of 4,294,967,296, matching the documented rules of 0.75 of detected
memory and logical processors divided by two in GB
(dedicated server,
read 2026-09-20). The option is off unless you start the server with it, so a
managed instance and a container can be very different servers.
innodb_redo_log_capacity buys room to spread flushing out. It is dynamic:
on that MySQL 8.4.11 container, 2026-09-20, SET GLOBAL on it and on the buffer
pool size together returned in 0.348 seconds with no restart. The cost is disk
space, a longer crash recovery, and a larger dirty working set that is allowed to
accumulate before anything forces it out.
innodb_io_capacity and innodb_io_capacity_max are the values InnoDB uses
to plan background writes. They are not a measurement of your device and setting
them does not make it faster. Too low and the cleaner falls behind; too high and
the queue moves into the storage layer where this page’s counters cannot see it.
innodb_max_dirty_pages_pct and innodb_max_dirty_pages_pct_lwm are
flushing triggers, 90 and 10 by default. The manual says InnoDB “aggressively
flushes buffer pool pages if the percentage of dirty pages in the buffer pool
reaches” the first, and that a low water mark of 0 “disables this early flushing
behaviour”
(buffer pool flushing,
read 2026-09-20). Neither caps the rate, so a dirty percentage above 90 tells you
the trigger fired and tells you nothing about whether the cleaner is at its
limit.
innodb_flush_neighbors writes physically adjacent pages together, and read
0 on stock MySQL 8.0.46 and MySQL 8.4.11 containers on 2026-09-20, because flash
storage does not care about adjacency.
What actually fixes the spikes
Section titled “What actually fixes the spikes”Read the three counters first and decide which wait you have. The table above contains the diagnosis, and it is cheaper than any of the changes below.
Size the buffer pool to the working set and the redo to the burst, together. On that MySQL 8.4.11 container, 2026-09-20, moving from 128 MiB and 100 MiB to 4 GiB and 4 GiB took the same write workload from 25.780 and 24.176 seconds to 15.197 and 15.141, with average commit latency roughly halved. Those runs change both settings at once and do not say which one paid, so treat it as one change and measure your own before and after.
Set innodb_io_capacity from what your storage sustains. Benchmark it or
take the provisioned figure. Do not copy a number from an article about somebody
else’s hardware, including this one.
Spread the writes on the application side. A nightly job that rewrites a large table in one burst can cause this pattern. Batching it into smaller commits with a pause between them costs nothing.
Check the storage, not only the server. Device latency, queue depth, cloud volume limits and container I/O quotas produce every symptom on this page, and none of the counters here can tell you about them.
Why does AI-generated code cause this?
Section titled “Why does AI-generated code cause this?”The bulk write is the pattern. Asked to backfill a column, import a dataset or rebuild a table, the generated answer is often one statement that does all of it as fast as possible. It dirties pages faster than the storage can absorb them, so the statement finishes and everything else on the server is slow for the next minute.
The second is generated configuration. A my.cnf produced from a prompt tends to
carry numbers that were right for a particular machine years ago,
innodb_io_capacity among them, and nothing in the file records which machine
that was.
How do I stop it coming back?
Section titled “How do I stop it coming back?”Chart Innodb_buffer_pool_pages_free beside
Innodb_buffer_pool_wait_free. The counter alone is too narrow to alert on,
and the pair tells you whether a wait was even possible.
Alert on checkpoint age rising and staying up, against your own
innodb_redo_log_capacity and correlated with commit latency, not on a fixed
percentage. The documented thresholds are where flushing starts, not a cliff:
10% of redo capacity turns on adaptive flushing and 75% starts asynchronous
flushing. That MySQL 8.4.11 container on 2026-09-20
would not let the age past 83.3% anyway.
Record commit latency. It is the only one of the three paths that shows up without a database counter, and it is the one your users have.
Give bulk jobs a commit size and a pause. Ten thousand rows and a short sleep spreads the flushing out, and it also fixes replication lag, which has the same cause.
Re-check the I/O settings after a version upgrade. innodb_io_capacity
measured 200 on a stock MySQL 8.0.46 container and 10000 on a stock MySQL 8.4.11
container on 2026-09-20, so a server that relies on defaults is a different
server afterwards.
Why is MySQL slow while the CPU is idle?
Because the threads are blocked rather than working, and three different blocks look the same from outside: waiting for a clean page in the buffer pool, waiting for InnoDB to advance a checkpoint so redo can be reused, and waiting for the redo fsync on your own commit. The CPU is not idle while that happens. Measured on a MySQL 8.4.11 container, 2026-09-20, the container read 186.64% of one core at the peak of a stall against 4.0% idle, on an 8 vCPU host.
Is Innodb_buffer_pool_wait_free the counter to watch?
It is one of three, and it is the narrowest. The MySQL 8.4 manual defines it as counting instances where InnoDB needed a page, found no clean one, and waited for a flush. Measured on a MySQL 8.4.11 container, 2026-09-20, it stayed flat through eight of nine runs, including one whose worst commit took 1.138 seconds, and moved only in the ninth, where the page cleaner was deliberately crippled. A flat wait_free does not mean flushing is not your problem.
What is checkpoint age?
The current log sequence number minus the last checkpoint, which is how much redo InnoDB has written but not yet flushed the corresponding pages for. It is not a number you can let run away: measured on a MySQL 8.4.11 container, 2026-09-20, across five runs it never exceeded 83.3% of innodb_redo_log_capacity, because the server forces flushing to hold it there. What you feel near that point is the forced flushing, not a crossing.
Should I raise innodb_io_capacity?
Only to a number your storage sustains, and on MySQL 8.4 it is already high. Measured on 2026-09-20, the default on a stock MySQL 8.4.11 container is 10000 against 200 on MySQL 8.0.46, and the maximum 20000 against 2000. They are what InnoDB plans its background writes with, not a measurement of your device, so setting them above what the disk delivers moves the queue into the storage layer.
What actually fixes the spikes?
Measured on a MySQL 8.4.11 container, 2026-09-20, raising the buffer pool from 128 MiB to 4 GiB and the redo from 100 MiB to 4 GiB together cut the same write workload from 25.780 and 24.176 seconds to 15.197 and 15.141, and average commit latency from 27.389 and 23.167 ms to 13.879 and 13.873. The runs were interleaved and they do not separate the two settings, so treat it as one change.