The worst number I ever got from a bulk import was not a disk-full alert or a lock wait. It was a wall clock: 4 hours and 20 minutes for a nightly pipeline that had benchmarked at 90 minutes on a clean box. The job pushed about 380 million rows of rating records into a single InnoDB table with five secondary indexes, starting at 01:00 and expected to finish before the 05:30 reporting run. Halfway through, iostat showed the data volume doing heavy random reads during a job that only wrote. Writes do not read — unless something else is reading. The something else was InnoDB itself, merging the change buffer: applying millions of buffered secondary-index updates to pages the import kept dragging back into the buffer pool. The merge was consuming the import window from the inside.
The fix was one dynamic variable, and throughput went from roughly 34,000 rows per second to about 74,000 — a little more than double — with the import landing in under two hours. But the same change on the wrong workload would have made things worse, because the change buffer exists for a good reason. Here is what it actually defers, why unique indexes are excluded by design, how the two controlling knobs interact, what the merge looks like in SHOW ENGINE INNODB STATUS, and the honest cost sheet for turning it off.
What does the InnoDB change buffer actually defer?
It defers writes to non-unique secondary index pages that are not currently in the buffer pool. Every INSERT or DELETE on a table must also update that table's secondary indexes, and those index entries land on pages scattered across the tablespace. If the target page is already in the buffer pool, InnoDB updates it in place and moves on. If it is not, the naive path is a random disk read to fetch the page just to modify one entry — brutal on a large table with several indexes. Instead, InnoDB records the pending change in the change buffer: an in-memory structure carved out of the buffer pool, with a persistent copy in the system tablespace, ibdata1. The row write completes immediately, and the index page is patched later, when it is next read into the pool for any reason. Random I/O becomes deferred, batchable merge work.
Two consequences follow. First, the buffered changes survive restarts — the persistent copy in ibdata1 is why the change buffer can also contribute to ibdata1 growth on write-heavy systems, and why a restart mid-import does not lose the debt, it just postpones it. Second, the cost is moved, not removed. Whoever reads a page with buffered changes pays the merge at read time, which is why the first report queries after our import used to spike in latency: they were merging the overnight backlog page by page. The change buffer covers three operation classes — inserts, delete-mark operations, and the physical removals done by purge — and it never applies to the clustered index, because the row itself has to be written there anyway. It shares its status section with the adaptive hash index, which is unrelated machinery; do not let the combined heading confuse you.
Why do unique indexes never benefit from the change buffer?
Because a uniqueness check cannot be deferred. Before InnoDB inserts an entry into a unique secondary index, it must verify that the key does not already exist, and that verification requires reading the actual index page — which pulls the page into the buffer pool and eliminates everything the change buffer would have saved. The same logic excludes the primary key: its uniqueness is enforced on every insert. So on a table whose only secondary indexes are UNIQUE, the change buffer has nothing to buffer, and on a table with a mix, only the non-unique indexes participate. This is worth checking before you tune anything, because it decides whether the knob will do anything at all.
Our import table was the worst-case profile for the change buffer: five secondary indexes, all non-unique, all composite keys whose leading columns were effectively random from InnoDB's point of view — feed identifiers and hashed session keys rather than anything ascending. Random keys mean the insertion point for each index entry is a random page of a table far too large to fit in memory, which is exactly the situation the change buffer was built to optimize, and exactly the situation where its merge backlog grows without bound when writes never stop. If your secondary indexes are mostly UNIQUE, or mostly built on ascending keys like timestamps that insert at the right edge of the index, most of this article is academic for that table — the pages being touched are already hot in the pool, and hot pages never get buffered in the first place.
How do innodb_change_buffering and innodb_change_buffer_max_size work?
The first variable selects which operation classes may be buffered; the second caps how much of the buffer pool the change buffer may occupy, as a percentage. innodb_change_buffering defaults to all and accepts none, inserts, deletes, and purges — where deletes means the delete-marking phase of a DELETE and purges means the physical removal that happens when purge threads later clean up marked records. innodb_change_buffer_max_size defaults to 25, meaning up to a quarter of the buffer pool, and can be raised to 50. Both are dynamic, so you can flip them without a restart:
-- current configuration
SELECT @@innodb_change_buffering,
@@innodb_change_buffer_max_size;
-- cumulative merge work since startup (graph this, not the raw value)
SHOW GLOBAL STATUS LIKE 'Innodb_ibuf_merge';
-- disable buffering entirely, e.g. before a bulk-load window
SET GLOBAL innodb_change_buffering = none;
-- or keep buffering but shrink its memory claim to 10% of the pool
SET GLOBAL innodb_change_buffer_max_size = 10;
-- the full picture, in its own section near the top of the output
SHOW ENGINE INNODB STATUS\GThree details that matter operationally. Setting innodb_change_buffering=none does not discard what is already buffered — existing entries drain through the normal merge process over minutes or hours, so flip the flag before the load window, not after the problem starts. Raising innodb_change_buffer_max_size buys a deeper buffer but makes each merge backlog bigger and steals memory from the actual data cache; on most systems it treats a symptom. And shrinking the pool without shrinking the cap shrinks the change buffer proportionally, which is one more interaction to keep in mind when you apply the reasoning in buffer pool sizing. One version note worth knowing: MariaDB removed the change buffer entirely starting with 10.5, so this whole tuning surface is MySQL-only territory.
How does the change buffer show up in SHOW ENGINE INNODB STATUS?
In its own section near the top, labeled INSERT BUFFER AND ADAPTIVE HASH INDEX, with page counts for the ibuf tree and cumulative merge counters split by operation type. The output from the import host at 02:40 that night looked like this:
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 6231, free list len 0, seg size 6233, 18944217 merged operations:
insert 2710433, delete mark 14822017, delete 1411767Read it this way: size is how many pages of the ibuf tree hold buffered records, free list len is how many allocated pages are empty, and seg size is the total allocated. A free list pinned at zero while seg size climbs means the buffer is saturated and merges are racing just to keep it below the max_size ceiling — which is precisely the read pressure iostat was showing. The merged operations line is cumulative since startup, so its value is the rate, not the number; same for the Innodb_ibuf_merge status counter, which is the one to graph because your monitoring can rate it directly. Merges fire in three situations: when a page with buffered changes is read into the pool, as background work when the server has spare cycles, and during a slow shutdown — innodb_fast_shutdown=0 forces a complete merge before the server stops, while the default fast shutdown leaves the buffered changes in ibdata1 to be merged after the next startup. Crash recovery preserves them too; the change buffer is durable by design. A steady merge rate on an OLTP system is normal and healthy. A merge rate that tracks your write throughput one-for-one during a load is the tell that the buffer has stopped helping and started taxing you.
Which workloads should keep the change buffer, and which should turn it off?
Keep it for OLTP with many non-unique secondary indexes on a working set larger than the pool; disable it for bulk loads into tables with random-keyed secondary indexes; and for append-mostly workloads it barely matters either way. The OLTP case is what the feature is for: user-facing writes scattered across cold index pages would otherwise pay a random read per secondary index per statement, and the change buffer converts that into deferred background work — a real latency and IOPS win you should not casually give up. The append-mostly case — an auto-increment primary key with secondary indexes on timestamps or other ascending columns — rarely touches cold pages at all, because inserts land on right-edge pages that stay hot in the pool, so the buffer stays nearly empty and the setting is a non-decision. Bulk loads are the trap. A sustained insert stream into random-keyed secondary indexes generates buffered entries faster than merges can drain them; once the ibuf tree runs at its ceiling, InnoDB is doing merge reads and merge writes in competition with the load itself, plus holding a quarter of the pool hostage for a structure that can never catch up. That was our 01:00 pipeline in exact detail.
The cost sheet for innodb_change_buffering=none is honest: every secondary index update on a page not in memory becomes an immediate random read and write instead of a deferred one. On a one-shot bulk load, that is usually still a win — you pay the random I/O once, at full speed, with no merge debt waiting for the morning's queries, and doubling our throughput agreed with that. On always-on OLTP it is a tax you pay forever, so the fix there is not the flag but fewer secondary indexes, keys that insert in ascending order, or a pool large enough to hold the hot index set. Two adjacent notes: dropping secondary indexes before a huge load and re-adding them after is often better than either buffering choice, since a sorted index build beats any row-at-a-time maintenance, and none of this helps unique indexes at all — they were never buffered, so they keep paying their random reads under every setting. If your load also pushes redo hard, the checkpoint side of the story is in redo log capacity and checkpoint stalls; the two limits like to arrive together.
Where MonPG stands on MySQL
I build MonPG, so plainly: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this piece — the ibuf seg size climbing against a zero free list, the merged-operations rate, Innodb_ibuf_merge graphed per second, and read IOPS spiking during a job that only writes — are exactly what the MySQL work is designed to surface as one timeline, so a saturated change buffer shows up as evidence rather than a mysteriously slow import. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first approach runs on the PostgreSQL side, and the rest of these MySQL field notes live on the blog.