The change buffer is a special data structure that caches
changes to secondary
index pages when affected pages are not in the
buffer pool. The cached
changes, which may result from
DELETE operations (DML), are
applied later when the pages are loaded into the buffer pool by
other read operations.
Unlike clustered indexes, secondary indexes are usually non-unique, and inserts into secondary indexes happen in a relatively random order. Similarly, deletes and updates can affect secondary index pages that are not adjacently located in an index tree. Applying cached changes at a later time, when affected pages are read into the buffer pool by other operations, avoids substantial random access I/O that would be required to read-in secondary index pages from disk.
When an affected page is read into the buffer pool, cached changes are applied. Periodically, the purge operation that runs when the system is mostly idle, or during a slow shutdown, writes the updated index pages to disk. The purge operation can write disk blocks for a series of index values more efficiently than if each value were written to disk immediately. It does so by merging several changes into the same index page at once.
Change buffer merging may take several hours when there are numerous secondary indexes to update and many affected rows. During this time, disk I/O is increased, which can cause a significant slowdown for disk-bound queries. Change buffer merging may also continue to occur after a transaction is committed. In fact, change buffer merging may continue to occur after a server shutdown and restart (see Section 14.20.2, “Forcing InnoDB Recovery” for more information).
In memory, the change buffer occupies part of the
InnoDB buffer pool. On disk, the change
buffer is part of the system tablespace, so that index changes
remain buffered across database restarts.
The type of data cached in the change buffer is governed by the
configuration option. For more information see,
Section 14.6.4, “Configuring InnoDB Change Buffering”.