Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual Change Buffer

The change buffer is a special data structure that caches changes to secondary index pages when affected pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

Unlike clustered indexes, secondary indexes are usually non-unique, and inserts into secondary indexes happen in a relatively random order. Similarly, deletes and updates may affect secondary index pages that are not adjacently located in an index tree. Merging cached changes at a later time, when affected pages are read into the buffer pool by other operations, avoids substantial random access I/O that would be required to read-in secondary index pages from disk.

Periodically, the purge operation that runs when the system is mostly idle, or during a slow shutdown, writes the updated index pages to disk. The purge operation can write disk blocks for a series of index values more efficiently than if each value were written to disk immediately.

Change buffer merging may take several hours when there are numerous secondary indexes to update and many affected rows. During this time, disk I/O is increased, which can cause a significant slowdown for disk-bound queries. Change buffer merging may also continue to occur after a transaction is committed. In fact, change buffer merging may continue to occur after a server shutdown and restart (see Section 14.20.2, “Forcing InnoDB Recovery” for more information).

In memory, the change buffer occupies part of the InnoDB buffer pool. On disk, the change buffer is part of the system tablespace, so that index changes remain buffered across database restarts.

The type of data cached in the change buffer is governed by the innodb_change_buffering configuration option. For more information see, Section 14.6.4, “Configuring InnoDB Change Buffering”.

Monitoring the Change Buffer

The following options are available for change buffer monitoring:

  • InnoDB Standard Monitor output includes status information for the change buffer. To view monitor data, issue the SHOW ENGINE INNODB STATUS command.


    Change buffer status information is located under the INSERT BUFFER AND ADAPTIVE HASH INDEX heading and appears similar to the following:

    Ibuf: size 1, free list len 0, seg size 2, 0 merges
    merged operations:
     insert 0, delete mark 0, delete 0
    discarded operations:
     insert 0, delete mark 0, delete 0
    Hash table size 276707, node heap has 1 buffer(s)
    15.81 hash searches/s, 46.33 non-hash searches/s

    For a description of each data point, see Section 14.17.3, “InnoDB Standard Monitor and Lock Monitor Output”.

  • The INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table provides metadata about each page in the buffer pool, including change buffer index and change buffer bitmap pages. Change buffer pages are identified by PAGE_TYPE. IBUF_INDEX is the page type for change buffer index pages, and IBUF_BITMAP is the page type for change buffer bitmap pages.


    Querying the INNODB_BUFFER_PAGE table can introduce significant performance overhead. To avoid impacting performance, reproduce the issue you want to investigate on a test instance and run your queries on the test instance.

    For example, you can query the INNODB_BUFFER_PAGE table to determine the approximate number of IBUF_INDEX and IBUF_BITMAP pages as a percentage of total buffer pool pages.

    ) AS change_buffer_pages,
    ) AS total_pages,
    SELECT ((change_buffer_pages/total_pages)*100)
    ) AS change_buffer_page_percentage;
    | change_buffer_pages | total_pages | change_buffer_page_percentage |
    |                  25 |        8192 |                        0.3052 |

    For information about other data provided by the INNODB_BUFFER_PAGE table, see Section 21.28.6, “The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table”. For related usage information, see Section 14.15.3, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables”.

  • Performance Schema provides change buffer mutex wait instrumentation for advanced performance monitoring. To view change buffer instrumentation, issue the following query (Performance Schema must be enabled):

    mysql> SELECT * FROM performance_schema.setup_instruments 
    WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%';
    | NAME                                                  | ENABLED | TIMED |
    | wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
    | wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
    | wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |

    For information about monitoring InnoDB mutex waits, see Section 14.16.1, “Monitoring InnoDB Mutex Waits Using Performance Schema”.

Download this Manual
User Comments
  Posted by Marek Kowal on September 30, 2004
If you type show innodb status\G, it will show - under section "INSERT BUFFER AND ADAPTIVE HASH INDEX" line like that:

54737 inserts, 12769 merged recs, 3612 merges

From what I have understood, this basically shows that 54 000 records have been inserted, but only 12 000 have been merged into indexes. The trick I have just found is that even after you stop last query to the database, the database still keeps on doing heavy IO, and what is happening then is that the remaining records are merged. The procedure continues until "inserts"=="merged recs". Only after that the IO really stops.

In my case the merging procedure takes about 3hrs after the last query. Even if you stop the database by shutdown command during merging, when you turn it on again, it will continue to merge the rows anyway. Only then it will make it slower, or so it seems from my experience. Dunno why. Also, if you shut down the database and then start it and it will continue to merge records, the "inserts" counter will be zeroed, so you will have not the slightest idea, how long to wait untill it finishes ;-)

Tip: DO NOT shut down the database until you see that everything is merged. Also, keep an eye on the difference between those values. If it grows constantly during normal operation, you are really missing some resources on your computer (probably IO). Also, merging is usually the reason for big IO even when the traffic drops down and you'd expect the database to perform faster, but it does not ;-) In the worst possible scenario (happened to me), the server was working on maximum IO rate, but was completely unusable, all IO went down to merging. This "deadlock" could only be resolved manually by stopping all queries for 6hrs...

  Posted by Wagner Bianchi on May 27, 2015
Take care when executing the proposed SELECT ... INFORMATION_SCHEMA.INNODB_BUFFER_PAGE mentioned above on a heavy loaded production server. Besides it's going to take several minutes, it's going to impose some hard pressure on MySQL Instance.
Sign Up Login You must be logged in to post a comment.