Skip to content

Why does InnoDB flushing cause latency spikes when CPU is low?

Last updated

View as Markdown

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.

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.

Take five counters twice, a few seconds apart. The differences are what matter, along with the interval between samples:

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE 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.

what you seewhich waitwhat changes it
wait_free rising and pages_free near zerono clean page to reusea larger buffer pool, or less page churn from scans and bulk writes
wait_free flat, checkpoint age high against innodb_redo_log_capacitycheckpoint pressurea larger redo capacity, or a lower sustained write rate
wait_free flat, checkpoint age low, commit latency highthe redo fsync on the commit pathstorage fsync latency, or a durability decision you make on purpose

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 redowriters elapsedcommit latency, averagepeak checkpoint age
4 GiB pool, 4 GiB redo15.197 s, 15.141 s13.879 ms, 13.873 ms13.4%, 13.2%
128 MiB pool, 100 MiB redo25.780 s, 24.176 s27.389 ms, 23.167 ms82.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.

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-20wait_freepeak dirty pagesworst commit
bulk load, stock settings07,318 of 8,192463 ms
random updates, 8 sessions, stock049235 ms
bulk load, innodb_io_capacity 10006,768 of 8,1921,138 ms
random updates, 12 sessions, cleaner crippled049108 ms
bulk load, cleaner crippled0 to 5417,649 of 8,192177 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.

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 1935950933
Last checkpoint at 1848558723

Checkpoint 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 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_commitmedian commitaverage
16.540 ms, 6.516 ms6.777 ms, 6.801 ms
24.499 ms, 4.624 ms4.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.

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:

variableMySQL 8.0.46MySQL 8.4.118.4.11 with --innodb-dedicated-server=ON
innodb_buffer_pool_size13421772813421772849928994816
innodb_redo_log_capacity1048576001048576004294967296
innodb_io_capacity2001000010000
innodb_io_capacity_max20002000020000
innodb_page_cleaners112

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.

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.

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.

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.