Understanding the InnoDB Insert Buffer (Change Buffer)
The InnoDB Change Buffer (formerly Insert Buffer, still referred to as IBUF in SHOW ENGINE INNODB STATUS) is an in-memory and on-disk structure that defers writes to secondary index pages when those pages are not currently cached in the buffer pool. Instead of performing a random I/O to load a non-cached secondary index page just to insert or delete a single entry, InnoDB writes the change to the change buffer in the system tablespace and merges it lazily when the page is next loaded into the buffer pool for other reasons. This optimisation is critical for write-heavy workloads with large secondary indexes that exceed buffer pool capacity, potentially reducing write I/O by 30–80%. The INSERT BUFFER AND ADAPTIVE HASH INDEX section of SHOW ENGINE INNODB STATUS exposes all change buffer statistics.
Change Buffer Architecture and Purpose
Why InnoDB Buffers Secondary Index Changes
InnoDB secondary indexes are maintained separately from the clustered (primary key) index. When a row is inserted, all secondary index entries must be updated — but those secondary index pages are often not in the buffer pool if the table is large and the workload is write-sequential. Without the change buffer, each secondary index write would require loading the index page from disk (a random I/O), modifying the entry, and dirtying the page. For a table with 5 secondary indexes and a 100,000 IOPS NVMe drive, this limits insert throughput to 100,000 / 5 = 20,000 inserts/second. The change buffer amortises these random writes into bulk sequential merges, allowing the 5-index table to sustain insert rates far closer to the raw disk throughput.
Change Buffer Operations: Insert, Delete-Mark, Purge
The change buffer handles three types of operations on secondary index entries: INSERT (new entries from row inserts), DELETE-MARK (marking entries as deleted during row deletes — the first step of a two-phase delete), and PURGE (physically removing delete-marked entries — the second step triggered by the purge thread). The SHOW ENGINE INNODB STATUS line "merged operations: insert N, delete mark M, delete P" reports cumulative merge counts for each operation type. A high delete-mark-to-purge ratio indicates the purge thread is lagging behind row deletions, which correlates directly with a growing History List Length. Monitoring the relative rates of these three operations provides insight into both the write workload composition and the InnoDB purge subsystem health.
Reading IBUF Stats in INNODB STATUS
Ibuf Size, Free List Len, Seg Size, and Merges
The SHOW ENGINE INNODB STATUS INSERT BUFFER section shows: "Ibuf: size N, free list len M, seg size P" followed by merge statistics. "size N" is the number of change buffer pages currently occupied in the system tablespace — each page is 16 KB, so size × 16,384 = change buffer disk usage in bytes. "free list len M" shows unused pages available for new change buffer entries. "seg size P" is the total allocated change buffer segment size (occupied + free). A growing seg size without a corresponding increase in buffer pool size indicates the change buffer is consuming increasing disk space; verify that innodb_change_buffer_max_size is not set too high and that merges are keeping pace with inserts by monitoring the merged-per-second rate against inserts-per-second.
Merged Operations: Inserts, Delete Marks, and Deletes
Change buffer merges occur when a buffered secondary index page is loaded into the buffer pool for any reason — a read query, a page cleaner background scan, or a crash recovery merge. The SHOW ENGINE INNODB STATUS line "N inserts, M merged recs, P merges" shows cumulative change buffer activity. The merge ratio — merged_recs / merges — shows how many buffered changes are applied per page load event; a high ratio (> 10) indicates efficient batching, meaning many changes accumulated before the page was loaded. A merge ratio near 1 means pages are loaded and merged almost immediately, suggesting the buffer pool is large enough to cache most secondary index pages — in which case the change buffer provides minimal benefit and innodb_change_buffering=none might simplify the system without performance loss.
Tuning the Change Buffer
innodb_change_buffer_max_size and Workload Type
innodb_change_buffer_max_size (default 25%) limits the change buffer to a maximum percentage of the buffer pool size. For write-heavy workloads with large secondary indexes and a buffer pool that cannot hold the entire index working set, increasing this to 50% can significantly reduce random write I/O. However, the change buffer consumes buffer pool memory that could otherwise cache data pages, reducing read hit rates. The optimal setting depends on your workload's read-to-write ratio: a write-dominant (> 80% writes) workload benefits from innodb_change_buffer_max_size=50; a read-dominant workload (> 80% reads) should keep it at 25% or lower to maximise data page cache space. Monitor Innodb_ibuf_size relative to the buffer pool size to ensure the change buffer does not dominate buffer pool consumption.
When to Disable the Change Buffer (innodb_change_buffering=none)
innodb_change_buffering controls which operations are buffered: "all" (default), "inserts", "deletes", "changes" (inserts + delete-marks), "purges", or "none". Setting innodb_change_buffering=none disables the change buffer entirely, which is beneficial when: (1) the buffer pool is large enough to cache all secondary index pages (hit rate > 99%), making buffering unnecessary; (2) the workload is read-dominant and the change buffer consumes buffer pool space without providing write benefits; or (3) troubleshooting change buffer corruption in a damaged instance. On SSDs where random write latency is < 50 microseconds, the per-operation cost of loading a non-cached secondary index page may be low enough that the change buffer overhead exceeds its benefit — benchmark both settings for your hardware.