mysql change engine_MySQL -- Innodb中的change buffer

change buffer是一种特殊的数据结构，当要修改的辅助索引页不在buffer pool中时，用来cache对辅助索引页的修改。对辅助索引页的操作可能是insert、update和delete操作。等到相关的索引页被读入buffer pool中后，才会使用change buffer中的内容对辅助索引页进行修改(即merge操作)。

和聚集索引不同，辅助索引通常是不唯一的，插入辅助索引通常也是随机的。同样，对辅助索引的删除、更新也通常是不连续的。

等到相关的索引页被读入buffer pool中后，才会使用change buffer中的内容对辅助索引页进行修改(即merge操作)可以避免大量的磁盘随机访问I/O。

间歇性的，在系统空闲或关闭过程中，会执行purge操作，将新的索引页写入磁盘。purge操作一次写多个索引值会比每次修改后就立即写入磁盘的效率高。

对change buffer的merge可能需要好几个小时，如果被更新的辅助索引行比较多。在merge过程中，磁盘的I/O会增加，可能会引起其他查询的性能的降低。

merge操作也可能发生在事务提交后。事实上，即使在实例重启后，还会可能发生merge操作。

在内存中，change buffer会占用buffer pool的空间；在物理磁盘上，change buffer是system tablespace的一部分，所以对索引的修改在数据库重启后仍然存在change buffer中。

change buffer包含的特性也叫作change buffering，包含insert buffering、delete buffering、purge buffering。

change buffer中数据类型和总量由参数innodb_change_buffering和innodb_chagne_buffer_max_size配置。查看change buffer中数据的信息，可以通过show engine innodb status查看。

change buffer在老版本中被称作insert buffer。

1.监控change buffer

mysql> show engine innodb status\G

...

-------------------------------------

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

...

2.查看information_schema

information_schema.innodb_metrics提供了change buffer的统计信息名称和说明：

mysql> select name, comment from information_schema.innodb_metrics where name like '%ibuf%';

+-----------------------------------------+-------------------------------------------------------------+

| name | comment |

+-----------------------------------------+-------------------------------------------------------------+

| buffer_page_read_index_ibuf_leaf | Number of Insert Buffer Index Leaf Pages read |

| buffer_page_read_index_ibuf_non_leaf | Number of Insert Buffer Index Non-Leaf Pages read |

| buffer_page_read_ibuf_free_list | Number of Insert Buffer Free List Pages read |

| buffer_page_read_ibuf_bitmap | Number of Insert Buffer Bitmap Pages read |

| buffer_page_written_index_ibuf_leaf | Number of Insert Buffer Index Leaf Pages written |

| buffer_page_written_index_ibuf_non_leaf | Number of Insert Buffer Index Non-Leaf Pages written |

| buffer_page_written_ibuf_free_list | Number of Insert Buffer Free List Pages written |

| buffer_page_written_ibuf_bitmap | Number of Insert Buffer Bitmap Pages written |

| ibuf_merges_insert | Number of inserted records merged by change buffering |

| ibuf_merges_delete_mark | Number of deleted records merged by change buffering |

| ibuf_merges_delete | Number of purge records merged by change buffering |

| ibuf_merges_discard_insert | Number of insert merged operations discarded |

| ibuf_merges_discard_delete_mark | Number of deleted merged operations discarded |

| ibuf_merges_discard_delete | Number of purge merged operations discarded |

| ibuf_merges | Number of change buffer merges |

| ibuf_size | Change buffer size in pages |

| innodb_ibuf_merge_usec | Time (in microseconds) spent to process change buffer merge |

+-----------------------------------------+-------------------------------------------------------------+

17 rows in set (0.00 sec)

mysql>

information_schema.innodb_buffer_page提供了buffer pool中每个页的元数据，包含change buffer 索引页和change buffer位图页。

change buffer页中page_type.ibuf_index表示change buffer索引页；page_type.ibuf_bitmap表示change buffer位图页。

提醒：查看innodb_buffer_page会有很大的性能开销。最好是在空闲时间或测试环境执行。

比如，可以查看change buffer的索引页和位图页占据buffer pool的比例：

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 |

+---------------------+-------------+-------------------------------+

| 14 | 8192 | 0.1709 |

+---------------------+-------------+-------------------------------+

1 row in set (0.05 sec)

performance_schema还为高级性能监控提供了change buffer mutex的等待指令：

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

| wait/synch/mutex/innodb/ibuf_mutex | NO | NO |

| wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | NO | NO |

+-------------------------------------------------------+---------+-------+

3 rows in set (0.00 sec)

3.配置change buffer

可以通过参数innodb_change_buffering来控制是否启用change buffer。

--all：默认值。开启buffer inserts、delete-marking operations、purges

--none：不开启change buffer

--inserts：只是开启buffer insert操作

--deletes:只是开delete-marking操作

--changes：开启buffer insert操作和delete-marking操作

--purges：对只是在后台执行的物理删除操作开启buffer功能

从5.6.2开始，参数innodb_change_buffer_max_size设置了change buffer可以占用buffer pool的百分比，默认是25，最大可以设置为50。

mysql> show variables like 'innodb_change_buffer_max_size';

+-------------------------------+-------+

| Variable_name | Value |

+-------------------------------+-------+

| innodb_change_buffer_max_size | 25 |

+-------------------------------+-------+

1 row in set (0.00 sec)

mysql>