change buffer
A special data structure that records changes to pages in secondary indexes. These values could result from SQL INSERT, UPDATE, or DELETE statements (DML). The set of features involving the change buffer is known collectively as change buffering, consisting of insert buffering, delete buffering, and purge buffering.
Changes are only recorded in the change buffer when the relevant page from the secondary index is not in the buffer pool. When the relevant index page is brought into the buffer pool while associated changes are still in the change buffer, the changes for that page are applied in the buffer pool (merged) using the data from the change buffer. Periodically, the purge operation that runs during times when the system is mostly idle, or during a slow shutdown, writes the new index pages to disk. The purge operation can write the disk blocks for a series of index values more efficiently than if each value were written to disk immediately.
Physically, the change buffer is part of the system tablespace, so that the index changes remain buffered across database restarts. The changes are only applied (merged) when the pages are brought into the buffer pool due to some other read operation.
The amount and kind of data stored in the change buffer are governed by the innodb-change-buffer_max_size and innodb_change_buffering configuration options. To see information about the current data in the change buffer, issue the SHOW ENGINE INNODB STATUS command.
Formerly known as the insert buffer.
See also buffer pool.
See also change buffering.
See also delete buffering.
See also DML.
See also insert buffer.
See also insert buffering.
See also merge.
See also page.
See also purge.
See also purge buffering.
See also secondary index.
See also system tablespace.