When INSERT, UPDATE, and
DELETE operations are done to a table, often
the values of indexed columns (particularly the values of
secondary keys) are not in sorted order, requiring substantial I/O
to bring secondary indexes up to date. InnoDB has an
insert buffer that
caches changes to secondary index entries when the relevant
page is not in the buffer pool,
thus avoiding I/O operations by not reading in the page from the
disk. The buffered changes are merged when the page is loaded to
the buffer pool, and the updated page is later flushed to disk
using the normal mechanism. The InnoDB main thread merges buffered
changes when the server is nearly idle, and during a
slow shutdown.
Because it can result in fewer disk reads and writes, this 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 insert 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 insert buffering. If the working set entirely fits in the buffer pool, insert buffering does not impose any extra overhead, because it only applies to pages that are not in the buffer pool.
Beginning with InnoDB 1.0.3, you can control whether InnoDB
performs insert buffering with the system configuration parameter
innodb_change_buffering.
Beginning with InnoDB 1.1 with MySQL 5.5, the buffering support is
expanded to include delete operations (when index records are
initially marked for deletion) and purge operations (when index
records are physically deleted). InnoDB 1.1 also changes the
default value from inserts to
all.
The allowed values of
innodb_change_buffering
are:
allThe default value: buffer inserts, delete-marking operations, and purges.
noneDo not buffer any operations.
insertsBuffer insert operations.
deletesBuffer delete-marking operations.
changesBuffer both inserts and delete-marking.
purgesBuffer the physical deletion operations that happen in the background.
You can set the value of this parameter in the MySQL option file
(my.cnf or my.ini) or change
it dynamically with the SET GLOBAL command,
which requires the SUPER privilege. Changing
the setting affects the buffering of new operations; the merging
of already buffered entries is not affected.
For more information about speeding up INSERT,
UPDATE, and DELETE
statements, see Section 7.2.2, “Optimizing DML Statements”.