MySQL 8.0 FAQ: InnoDB Change Buffer 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. 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: •A full merge of the change buffer can be forced as part of a slow server shutdown using  -- innodb-fast-shutdown=0 . •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 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. •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. •When a page is read into the buffer pool, buffered changes are merged upon completion of the read, before the page is made available. A.16.8. When should the change buffer be used? A.16.7. When is the change buffer flushed? A.16.6. When does change buffer merging occur? A.16.5. How do I determine the current size of the change buffer? Relevant data points include: ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges For information about monitoring change buffer status, see Section 15.5.2, “Change Buffer” . • seg size : The size of the change buffer, in pages. • 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. 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 Updated pages are flushed by the same flushing mechanism that flushes the other pages that occupy the buffer pool. 5276