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.16.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.16.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 can 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.16.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.16.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 

