Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 34.7Mb
PDF (A4) - 34.7Mb
PDF (RPM) - 32.6Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.3Mb
HTML Download (RPM) - 7.1Mb
Man Pages (TGZ) - 129.9Kb
Man Pages (Zip) - 185.4Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb

MySQL 8.0 Reference Manual  /  ...  /  Configuring InnoDB Change Buffering

Pre-General Availability Draft: 2018-01-12

15.6.4 Configuring InnoDB Change Buffering

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. 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. 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, 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 InnoDB performs change buffering using the innodb_change_buffering 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 all.

Permitted innodb_change_buffering values include:

  • all

    The default value: buffer inserts, delete-marking operations, and purges.

  • none

    Do not buffer any operations.

  • inserts

    Buffer insert operations.

  • deletes

    Buffer delete-marking operations.

  • changes

    Buffer both inserts and delete-marking operations.

  • purges

    Buffer the physical deletion operations that happen in the background.

You can set the innodb_change_buffering parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SYSTEM_VARIABLES_ADMIN or 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, “Configuring the Change Buffer Maximum Size”.

User Comments
Sign Up Login You must be logged in to post a comment.