The change buffer is the InnoDB feature I see misjudged in both directions. Half the teams I meet have never looked at it and are leaving write throughput on the table; the other half read a tuning post from the spinning-rust era and expect it to rescue a workload it cannot help. My own lesson came from an ingest service writing millions of rows a day into a table with five secondary indexes on an IOPS-capped cloud volume. Each write should have cost several random page reads; instead writes sailed, because the change buffer was deferring and batching index maintenance we could not afford to do eagerly. Years later I watched the same feature contribute nothing measurable on an all-NVMe box whose indexes fit comfortably in memory. Both outcomes were correct. This is the mechanism, the knobs, and how I decide what to do with it now.
What the change buffer actually buffers
When an INSERT, UPDATE, or DELETE needs to modify a secondary index leaf page that is not currently in the buffer pool, InnoDB has a choice. It can read the page in from disk right now, apply the change, and move on, which is a random read you did not want to pay. Or it can record the change as an entry in the change buffer and apply it later, when the page is read into the pool for any reason, or when background merge activity gets to it. The change buffer handles three classes of operation: inserts, delete-marks (the flag a DELETE or an indexed-column UPDATE leaves behind), and purges (the physical removal of delete-marked records). Two exclusions matter. The clustered index never uses it, and unique secondary indexes cannot buffer inserts, because uniqueness can only be checked against the real page. The structure itself lives in the system tablespace and consumes buffer pool space up to a configurable ceiling.
Why it exists: the economics of random I/O
A single-row write to a table with k secondary indexes touches one clustered page plus up to k secondary pages. If those secondary pages are cold, every touch is a random read, and random reads are the scarcest resource on spinning disks and on cloud volumes with IOPS ceilings. The change buffer converts N immediate cold-page reads into deferred merges, and the merging is where the win compounds: many buffered changes aimed at the same page apply in a single read. The canonical beneficiary is an ingest or analytics table much larger than RAM, carrying several non-unique secondary indexes, written hard and read mostly over recent rows that stay hot anyway. In that shape the feature is not a micro-optimization; it can be the difference between a write pipeline that keeps up and one that queues itself to death.
There is also a write-amplification angle people miss. Without buffering, every cold secondary page touched by a write is read in, dirtied, and eventually flushed, one page churned per row change. With buffering, that page might absorb dozens of row changes during a single stay in the pool before it is flushed once. You have not just deferred the I/O, you have divided it. This is why the feature punches far above its weight on batch loads into indexed tables, and why disabling it to save memory is usually a bad trade on exactly the workloads that load the most data.
The knobs: innodb_change_buffering and innodb_change_buffer_max_size
innodb_change_buffering selects which operation classes get buffered. It takes a single value from the enum none, inserts, deletes, changes, purges, or all, not a mix-and-match list: deletes covers the delete-mark operations, changes is inserts and deletes together, and purges is the physical cleanup that follows. The default is all on 8.0; 8.4 changes it to none, which is the project declaring that on modern storage the feature should justify itself before it is switched on. Either way the variable is dynamic, so experiments are cheap. innodb_change_buffer_max_size caps the change buffer as a percentage of the buffer pool, 25 by default; on write-heavy servers whose secondary indexes dwarf memory I have taken it to 40 or 50 with good results. Two operational footnotes. Turning buffering off does not flush what is already buffered; existing entries merge out gradually as pages are read, so the ibuf drains on its own schedule, not yours. And a slow shutdown, innodb_fast_shutdown set to 0, performs a full merge and purge before stopping, which on a large change buffer turns a restart into a long, deliberate flush; know that before you pick a maintenance window.
Watching merge activity
The INSERT BUFFER AND ADAPTIVE HASH INDEX section of SHOW ENGINE INNODB STATUS reports the change buffer's size, free list length, segment size, and a running count of merged operations by type. For trendable counters, the change_buffer subsystem in information_schema.innodb_metrics is the better source; enable the module once and then read the counters directly:
SET GLOBAL innodb_monitor_enable = 'module_ibuf';
SELECT name, count
FROM information_schema.innodb_metrics
WHERE subsystem = 'change_buffer'
AND status = 'enabled'
ORDER BY name;The healthy picture: ibuf size oscillating comfortably below the ceiling, merge counts ticking up steadily with write traffic. The unhealthy picture is ibuf size pinned at the innodb_change_buffer_max_size ceiling while merges fall behind, because at that point InnoDB stops buffering and goes back to reading pages synchronously. The feature has silently disengaged, and writes get expensive again with no error anywhere. If you see that, either raise the ceiling or accept that the workload has outgrown the optimization and the write rate itself needs attention.
The NVMe question
Here is the honest modern wrinkle. The change buffer only engages on buffer pool misses; a page already in memory is modified directly. On a server with a generously sized buffer pool and NVMe storage, two things happen at once: far fewer misses occur in the first place, and the misses that do occur cost tens of microseconds instead of milliseconds. Both halves of the feature's value proposition shrink. On several all-flash systems I have profiled, the change buffer was essentially idle, not because it was misconfigured but because the workload never handed it a cold page to work with. My current policy: set innodb_change_buffering to all deliberately, which on 8.4 means explicitly overriding the new none default, because where it does nothing it costs almost nothing. On read-mostly boxes with big pools, lower the max size to hand memory back to caching. Reserve real attention for the workloads that still fit the original shape: secondary indexes far larger than RAM, heavy sustained writes, storage with an IOPS ceiling. There the change buffer is still doing what it was built to do, and I would not run that workload without it.
The signal I would graph
The number I want trended from this article is ibuf size against its ceiling over weeks, not any single reading. I help build MonPG, which monitors PostgreSQL today; MySQL support is on the way, and change-buffer health sits on the MySQL signal list precisely because a disengaged ibuf looks exactly like a healthy one in any single snapshot. The MySQL monitoring (coming soon) page is where that work shows up first. Everything above also assumes your pool is sized sanely in the first place, which is the subject of the buffer pool field guide. And if your fleet also runs PostgreSQL, where the nearest cousin of this story is how checkpointing trades memory for I/O, the PostgreSQL monitoring side is live today, with more engine internals on the blog.