MySQL Change Buffer

Change Buffer（修改缓存）

• change buffer 是一个用来存储不在 buffer pool 中的二级索引页的特殊数据结构。通常 DML 操作（增，删，改等操作）发生时会将修改过的索引数据页存放到 change buffer 中，而后这些索引页需要被 buffer pool 读取时，将合并加载到 buffer pool 中。
• 二级索引与聚集索引并不相同，通常二级索引都是非唯一的，并且二级索引的插入顺序都相对比较随机。类似的，删除和更新操作涉及到的索引页可能并不是在索引树中相邻的页。合并被修改的缓存的操作会在稍后的时间完成，这个时机会是在这些受影响的页被其他操作读取到 buffer pool 的时候进行，这样就可以避免一些二级索引页从磁盘上随机读取操作。
• 清理操作会实时处理，当系统闲置，或者 slow shutdown 时，会将更新的索引页写到磁盘中。清理操作可以将若干索引值写入到磁盘块中，这样比每次都立即写磁盘的效率会高出很多。
• 修改缓存的合并操作在受影响的行数以及若干个二级索引被更新的情况下，可能需要消耗若干小时。此时，磁盘的 I/O 开销将上升，此时将会千万明显的磁盘相关的查询操作变慢。修改缓存的合并操作同样会在提交事务后继续触发，同样的服务关闭和重启也会触发。
• 在内存中，修改缓存是 buffer pool 的一部分。在磁盘中，修改缓存是系统表空间的一部分，当服务被关掉后，索引修改缓存会被保存起来。
• 这种类型的数据缓存由 innodb_change_buffering 变更管理。
• 当二级缓存中包含了降序（descending）索引字段或主键中包含降序（descending)索引字段时，修改缓存是不生效的。

配置修改缓存

• 当插入，更新和删除操作发生在一个表上时，索引字段（通常指二级索引）的值通常是无序的，需要通过 I/O 子系统将二级索引更新到最新数据。而修改缓存将不在 buffer pool 中的二级索引实体相关的索引页缓存起来，如此避免了立即从磁盘中的页中读取数据的昂贵的 I/O 操作。缓存修改会在数据被加载到 buffer pool 时对页进行合并操作，并且，更新的页会在稍后被刷新到磁盘中。 InnoDB 主线程在服务闲置时触发修改缓存的合并机制，此外当系统发生 slow shutdown 的时候也会触发。
• 由于修改缓存的低磁盘读和写的我，对于 I/O 负载高的情况是很有使用价值的。例如，有很高的 DML 磁盘操作的 bulk insert 应用程序，就可以通过使用修改缓存获得不小的收益。
• 当然修改缓存是 buffer pool 的一部分，它会占用缓存数据页，降低可用内存的使用率。如果 buffer pool 中包含了几乎所有的工作数据集，或者如果表中只有很少的二级索引，或许关闭修改缓存会很有用。如果工作数据集都在 buffer pool 中，修改缓存起不到太大的作用，因为它只对不在 buffer pool 中的页起作用。
• innodb_change_buffering 变量控制修改缓存的缓存范围。我们可以通过这个变量开启或关闭插入，删除操作（索引数据被初始化为被标记为删除状态）以及清理操作（当索引记录被物理删除时）。更新操作由插入和删除操作合并起来。 innodb_change_buffering 默认值为 all。

配置修改缓存的最大值

• innodb_change_buffer_max_size 该变量可以将修改缓存最大数值占用 buffer pool 的百分比值。默认值为 25，最大可设置为 50。
• 当 MySQL Server 有很多的插入，更新和删除在线操作,并且修改缓存的合并操作没有办法跟上新产生的修改缓存对象从而导致修改缓存达到它的最大值限制时，可以增加 innodb_change_buffer_max_size 的数值。
• 当 MySQL 服务用于静态报表数据，或者当修改缓存消费了太多 buffer pool 分享的内存，导致 buffer pool 中的内存页的生命周期短于预期的时间。

监控修改缓存

• show engine innodb statusG

mysql> SHOW ENGINE INNODB STATUSG

-------------------------------------
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
discarded operations:
 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

• information_schema.innodb_metric

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'G

• information_schema.innodb_buffer_page

mysql> 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 |
+---------------------+-------------+-------------------------------+

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   |
+-------------------------------------------------------+---------+-------+