MySQL -- Innodb中的change buffer

2017-10-04 10:30 abce 阅读(3898) 评论(0) 编辑收藏举报

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> 