Change Buffer When  INSERT ,  UPDATE , and  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. The change buffer 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 into the buffer pool, and the updated page is later flushed to disk. 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, change buffering is most valuable for workloads that are I/O-bound; for example, applications with a high volume of DML operations such as bulk inserts benefit from change buffering. 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 data set fits entirely within the buffer pool, change buffering does not impose extra overhead, because it only applies to pages that are not in the buffer pool. The  innodb_change_buffering  variable controls the extent to which  InnoDB  performs change buffering. 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 all . Permitted  innodb_change_buffering  values include: 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  innodb_change_buffering  variable in the MySQL option file ( my.cnf  or  my.ini ) or change it dynamically with the  SET GLOBAL  statement, which requires privileges sufficient to set global system variables. See Section 5.1.9.1, “System Variable Privileges” . Changing the setting affects the buffering of new operations; the merging of existing buffered entries is not affected. • all • none • inserts • deletes • changes • purges 3013