The idea: don't read a page just to change it
A table has one clustered index (PRIMARY) and usually some secondary indexes. Rows arrive in primary key order, so the PRIMARY page they go into is hot and in the buffer pool. But the secondary index entries land all over their index: the city of the next row can be anything. Changing a secondary index leaf page that is not in the buffer pool would cost a random read first. The change buffer skips that read: InnoDB writes down the change and applies it later, when the page is read anyway.
The page has a table users(id PRIMARY KEY, city, email) with UNIQUE KEY uk_email (email) and KEY idx_city (city). Every statement changes PRIMARY, then uk_email, then idx_city. The buffer pool has 5 frames: PRIMARY, the idx_city page p10 and the uk_email page p20 are cached, the rest is only on disk.
What can be buffered
Only changes to leaf pages of secondary, non-unique indexes, and only when the page is not in the buffer pool. If the page is cached, changing it in memory is cheaper than anything else (Demo: page in the pool, direct change). If it is not, the change becomes an entry in the change buffer (Demo: page not in the pool, change buffered). innodb_change_buffering names the kinds of change it may hold:
| Value | Buffers |
|---|---|
inserts | new index entries (INSERT, the new value of an UPDATE) |
deletes | delete-marking an entry (DELETE, the old value of an UPDATE) |
purges | physically removing delete-marked entries (the purge thread) |
changes / all | inserts + deletes / all three (all was the default up to MySQL 8.3) |
none | nothing: the default since MySQL 8.4 |
The clustered index is never buffered: InnoDB needs the PRIMARY page to place the row and check the key.
The ibuf bitmap and the merge
The change buffer is a B-tree keyed by (tablespace, page number), so all entries for one page sit together. Each tablespace also has ibuf bitmap pages with a few bits per page; one of them says "this page has buffered changes". Whenever InnoDB reads a page into the buffer pool, it checks that bit. If it is set, the buffered entries are applied to the page (the merge) before any query can see it, and the bit is cleared. So a query always sees every change. Three changes to page p12 cost one read in the end, not three (Demo: 3 changes, merged with one read).
Merges happen in three ways:
- when a query (or read-ahead) reads the page;
- in the background: the master thread merges some pages about once a second, more when the server is idle (Demo: crash safety, then background merge);
- at a slow shutdown (
innodb_fast_shutdown = 0), which merges everything, for example before an upgrade.
Unique indexes need the page
An insert into a unique index must make sure the value isn't there already. The only way to know is to look at the page, so the page is read and the change buffer can't help (Demo: unique index needs a read). In that demo the same INSERT reads uk_email's page p21 but buffers its idx_city change. That's why the change buffer matters most for tables with several non-unique secondary indexes.
Size and settings
innodb_change_buffer_max_size: the most of the buffer pool the change buffer may take, in percent (default 25, max 50). When it is full, new changes read their page like with buffering off. The page uses 6 entries.innodb_change_buffering: see the table above. Withnoneeach change to a missing page is a random read (Demo: innodb_change_buffering = none: 4 reads and a write for 3 inserts).SHOW ENGINE INNODB STATUS, section INSERT BUFFER AND ADAPTIVE HASH INDEX: size, merges and merged operations, like the counters on the canvas.
Crash safety
The change buffer is not a side file. Its B-tree lives in the system tablespace (ibdata1), its pages are cached in the buffer pool like any page, and every change to them is written to the redo log. After a crash, redo recovery rebuilds the change buffer and the bitmap together with every other page, and the buffered changes are merged later as usual (Demo: crash safety, then background merge).
When it helps, and why 8.4 turned it off
It helps a write-heavy workload on a table with secondary indexes much bigger than the buffer pool, on disks where random reads are slow. It does little when the indexes fit in memory (there is nothing to save), and it can hurt: buffered entries take buffer pool space, a big change buffer means a long merge later, and pages read for a query pay for the merge. With SSDs and big buffer pools the saving is small, so MySQL 8.4 made none the default. Turn it back on with innodb_change_buffering = all if a benchmark shows it helps.
See also How MySQL Runs a Query, the InnoDB buffer pool, the 16 KB InnoDB page, the redo log, crash recovery, PostgreSQL shared buffers (PostgreSQL has no change buffer) and LSM vs B-tree, where buffering writes is the whole design.
What the page leaves out
The bitmap also keeps the free space of each page, and InnoDB refuses to buffer a change that could split or empty a page. Purge operations, the ibuf free list, the merge when the change buffer gets too big, read-ahead triggering merges, the exact master thread pacing, and space IDs are skipped. The PRIMARY page is drawn as always cached and its disk copy is not shown. Real redo records are physical changes to pages, not the short text on the canvas.