DELETE operations are performed on
a table, the values of indexed columns (particularly the values of
secondary keys) are often in an unsorted order, requiring
substantial I/O to bring secondary indexes up to date.
InnoDB has a
change buffer that
caches changes to secondary index entries when the relevant
page is not in the
buffer pool, thus avoiding
expensive I/O operations by not immediately reading in the page
from disk. The buffered changes are merged when the page is loaded
to the buffer pool, and the updated page is later flushed to disk.
InnoDB main thread merges buffered changes
when the server is nearly idle, and during a
Because it can result in fewer disk reads and writes, the change buffer feature is most valuable for workloads that are I/O-bound, for example applications with a high volume of DML operations such as bulk inserts.
However, the change buffer occupies a part of the buffer pool, reducing the memory available to cache data pages. If the working set almost fits in the buffer pool, or if your tables have relatively few secondary indexes, it may be useful to disable change buffering. If the working set fits entirely within the buffer, change buffering does not impose extra overhead, because it only applies to pages that are not in the buffer pool.
You can control the extent to which
performs change buffering using the
configuration parameter. You can enable or disable buffering for
inserts, delete operations (when index records are initially
marked for deletion) and purge operations (when index records are
physically deleted). An update operation is a combination of an
insert and a delete. The default
innodb_change_buffering value is
The default value: buffer inserts, delete-marking operations, and purges.
Do not buffer any operations.
Buffer insert operations.
Buffer delete-marking operations.
Buffer both inserts and delete-marking operations.
Buffer the physical deletion operations that happen in the background.
You can set the
in the MySQL option file (
my.ini) or change it dynamically with the
command, which requires the
SUPER privilege. Changing the setting affects
the buffering of new operations; the merging of existing buffered
entries is not affected.
Change buffering is not supported for a secondary index if the index contains a descending index column or if the primary key includes a descending index column.
For related information, see Section 15.4.2, “Change Buffer”. For information about configuring change buffer size, see Section 188.8.131.52, “Configuring the Change Buffer Maximum Size”.