# 【MySQL 5.7 Reference Manual】15.4.2 Change Buffer（变更缓冲）

15.4.2 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 15.21.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 15.6.5, “Configuring InnoDB Change Buffering”. You can also configure the maximum change buffer size. For more information, see Section 15.6.5.1, “Configuring the Change Buffer Maximum Size”.

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.

mysql> SHOW ENGINE INNODB STATUS\G
Change buffer status information is located under the INSERT BUFFER AND ADAPTIVE HASH INDEX heading and appears similar to the following:

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
insert 0, delete mark 0, delete 0
Hash table size 4425293, used cells 32, node heap has 1 buffer(s)
13577.57 hash searches/s, 202.47 non-hash searches/s
For more information, see Section 15.17.3, “InnoDB Standard Monitor and Lock Monitor Output”.

• The INFORMATION_SCHEMA.INNODB_METRICS table provides most of the data points found in InnoDB Standard Monitor output, plus other data points. To view change buffer metrics and a description of each, issue the following query:

INFORMATION_SCHEMA.INNODB_METRICS表提供了在InnoDB标准监视器中输出的大部分数据点，以及其他数据点。要看变更缓冲的指标及其描述，输入以下查询：
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
For INNODB_METRICS table usage information, see Section 15.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.

• 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.

INFORMATION_SCHEMA.INNODB_BUFFER_PAGE表提供缓冲池中每一页的元数据，包括变更缓冲索引页和变更缓冲位图页。变更缓冲页是通过一些标记来识别的，PAGE_TYPE. IBUF_INDEX表示页类型是变更缓冲索引页，IBUF_BITMAP表示页类型是变更缓冲位图页。
Warning
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.

SELECT
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE PAGE_TYPE LIKE 'IBUF%'
) AS change_buffer_pages,
(
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
) 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, seeSection 23.31.1, “The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table”. For related usage information, see Section 15.15.5, “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提供对变更缓冲互斥等待的检测信息以增强对于性能的监控能力。要看性能缓冲检测信息，输入以下查询：
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 15.16.2, “Monitoring InnoDB Mutex Waits Using Performance Schema”.

• 本文已收录于以下专栏：

举报原因： 您举报文章：【MySQL 5.7 Reference Manual】15.4.2 Change Buffer（变更缓冲） 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)