Question

A.15.1.

What types of operations modify secondary indexes and result in change buffering?

Answer 1

INSERT, UPDATE, and DELETE operations can modify secondary indexes. If an affected index page is not in the buffer pool, the changes can be buffered in the change buffer.

Answer 2

Buffering secondary index changes when secondary index pages are not in the buffer pool avoids expensive random access I/O operations that would be required to immediately read in affected index pages from disk. Buffered changes can be applied later, in batches, as pages are read into the buffer pool by other read operations.

Answer 3

No. The change buffer only supports secondary indexes. Clustered indexes, full-text indexes, and spatial indexes are not supported. Full-text indexes have their own caching mechanism.

Answer 4

Prior to the introduction of the innodb_change_buffer_max_size configuration option in MySQL 5.6, the maximum size of the on-disk change buffer in the system tablespace was 1/3 of the InnoDB buffer pool size.

In MySQL 5.6 and later, the innodb_change_buffer_max_size configuration option defines the maximum size of the change buffer as a percentage of the total buffer pool size. By default, innodb_change_buffer_max_size is set to 25. The maximum setting is 50.

InnoDB does not buffer an operation if it would cause the on-disk change buffer to exceed the defined limit.

Change buffer pages are not required to persist in the buffer pool and may be evicted by LRU operations.

Answer 5

The current size of the change buffer is reported by SHOW ENGINE INNODB STATUS \G, under the INSERT BUFFER AND ADAPTIVE HASH INDEX heading. For example:

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges

Relevant data points include:

size: The number of pages used within the change buffer. Change buffer size is equal to seg size - (1 + free list len). The 1 + value represents the change buffer header page.
seg size: The size of the change buffer, in pages.

For information about monitoring change buffer status, see Section 15.5.2, “Change Buffer”.

Answer 6

When a page is read into the buffer pool, buffered changes are merged upon completion of the read, before the page is made available.
Change buffer merging is performed as a background task. The innodb_io_capacity parameter sets an upper limit on the I/O activity performed by InnoDB background tasks such as merging data from the change buffer.
A change buffer merge is performed during crash recovery. Changes are applied from the change buffer (in the system tablespace) to leaf pages of secondary indexes as index pages are read into the buffer pool.
The change buffer is fully durable and will survive a system crash. Upon restart, change buffer merge operations resume as part of normal operations.
A full merge of the change buffer can be forced as part of a slow server shutdown using --innodb-fast-shutdown=0.

Answer 7

Updated pages are flushed by the same flushing mechanism that flushes the other pages that occupy the buffer pool.

Answer 8

The change buffer is a feature designed to reduce random I/O to secondary indexes as indexes grow larger and no longer fit in the InnoDB buffer pool. Generally, the change buffer should be used when the entire data set does not fit into the buffer pool, when there is substantial DML activity that modifies secondary index pages, or when there are lots of secondary indexes that are regularly changed by DML activity.

Answer 9

You might consider disabling the change buffer if the entire data set fits within the InnoDB buffer pool, if you have relatively few secondary indexes, or if you are using solid-state storage, where random reads are about as fast as sequential reads. Before making configuration changes, it is recommended that you run tests using a representative workload to determine if disabling the change buffer provides any benefit.

Answer 10

See Section 15.5.2, “Change Buffer”.

A.15.1.	What types of operations modify secondary indexes and result in change buffering?
	`INSERT`, `UPDATE`, and `DELETE` operations can modify secondary indexes. If an affected index page is not in the buffer pool, the changes can be buffered in the change buffer.
A.15.2.	What is the benefit of the `InnoDB` change buffer?
	Buffering secondary index changes when secondary index pages are not in the buffer pool avoids expensive random access I/O operations that would be required to immediately read in affected index pages from disk. Buffered changes can be applied later, in batches, as pages are read into the buffer pool by other read operations.
A.15.3.	Does the change buffer support other types of indexes?
	No. The change buffer only supports secondary indexes. Clustered indexes, full-text indexes, and spatial indexes are not supported. Full-text indexes have their own caching mechanism.
A.15.4.	How much space does `InnoDB` use for the change buffer?
	Prior to the introduction of the `innodb_change_buffer_max_size` configuration option in MySQL 5.6, the maximum size of the on-disk change buffer in the system tablespace was 1/3 of the `InnoDB` buffer pool size. In MySQL 5.6 and later, the `innodb_change_buffer_max_size` configuration option defines the maximum size of the change buffer as a percentage of the total buffer pool size. By default, `innodb_change_buffer_max_size` is set to 25. The maximum setting is 50. `InnoDB` does not buffer an operation if it would cause the on-disk change buffer to exceed the defined limit. Change buffer pages are not required to persist in the buffer pool and may be evicted by LRU operations.
A.15.5.	How do I determine the current size of the change buffer?
	The current size of the change buffer is reported by `SHOW ENGINE INNODB STATUS \G`, under the `INSERT BUFFER AND ADAPTIVE HASH INDEX` heading. For example: `------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges` Relevant data points include: `size`: The number of pages used within the change buffer. Change buffer size is equal to `seg size - (1 + free list len)`. The `1 +` value represents the change buffer header page. `seg size`: The size of the change buffer, in pages. For information about monitoring change buffer status, see Section 15.5.2, “Change Buffer”.
A.15.6.	When does change buffer merging occur?
	When a page is read into the buffer pool, buffered changes are merged upon completion of the read, before the page is made available. Change buffer merging is performed as a background task. The `innodb_io_capacity` parameter sets an upper limit on the I/O activity performed by `InnoDB` background tasks such as merging data from the change buffer. A change buffer merge is performed during crash recovery. Changes are applied from the change buffer (in the system tablespace) to leaf pages of secondary indexes as index pages are read into the buffer pool. The change buffer is fully durable and will survive a system crash. Upon restart, change buffer merge operations resume as part of normal operations. A full merge of the change buffer can be forced as part of a slow server shutdown using `--innodb-fast-shutdown=0`.
A.15.7.	When is the change buffer flushed?
	Updated pages are flushed by the same flushing mechanism that flushes the other pages that occupy the buffer pool.
A.15.8.	When should the change buffer be used?
	The change buffer is a feature designed to reduce random I/O to secondary indexes as indexes grow larger and no longer fit in the `InnoDB` buffer pool. Generally, the change buffer should be used when the entire data set does not fit into the buffer pool, when there is substantial DML activity that modifies secondary index pages, or when there are lots of secondary indexes that are regularly changed by DML activity.
A.15.9.	When should the change buffer not be used?
	You might consider disabling the change buffer if the entire data set fits within the `InnoDB` buffer pool, if you have relatively few secondary indexes, or if you are using solid-state storage, where random reads are about as fast as sequential reads. Before making configuration changes, it is recommended that you run tests using a representative workload to determine if disabling the change buffer provides any benefit.
A.15.10.	Where can I find additional information about the change buffer?
	See Section 15.5.2, “Change Buffer”.

A.15 MySQL 8.0 FAQ: InnoDB Change Buffer