Secondary indexes are usually nonunique, and insertions into secondary indexes happen in a relatively random order. This would cause a lot of random disk I/O operations without a special mechanism used in InnoDB called the insert buffer.
When a record is inserted into a nonunique secondary index page that is not in the buffer pool, InnoDB inserts the record into a special B-tree: the insert buffer. Periodically, the insert buffer is merged into the secondary index trees in the database. A merge also occurs whenever a secondary index page is loaded to the buffer pool.
A “normal” shutdown does not clear the
insert buffer. A normal shutdown occurs when
innodb_fast_shutdown=1, the default. If the
insert buffer is not empty when the InnoDB Plugin is shut
down, it may contain changes for tables in
COMPRESSED format. Thus, starting the built-in InnoDB in MySQL on the data
files may lead into a crash if the insert buffer is not empty.
A “slow” shutdown merges all changes from
the insert buffer. To initiate a slow shutdown,
execute the command
innodb_fast_shutdown=0 before initiating the shutdown
of the InnoDB Plugin.
To disable insert buffer merges, you may set
innodb_force_recovery=4 so that you can back up the
uncompressed tables with the built-in InnoDB in MySQL. Be sure not to use any
WHERE conditions that would require access to
secondary indexes. For more information, see the MySQL manual on
In the InnoDB Plugin 1.0.3 and later, you can disable the
buffering of new operations by setting the parameter
Section 7.4, “Controlling InnoDB Insert Buffering” for