Secondary indexes are usually non-unique, and DML operations on 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 change is made to a non-unique 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 storage engine is shut down, it may contain changes for tables in DYNAMIC or 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 SET GLOBAL innodb_fast_shutdown=0 before initiating the shutdown of the InnoDB storage engine.

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 Forcing InnoDB Recovery.

In the InnoDB storage engine 1.0.3 and later, you can disable the buffering of new operations by setting the parameter innodb_change_buffering. See Section 7.4, “Controlling InnoDB Change Buffering” for details.