MySQL -- Innodb中的change buffer

標籤：最大   var   ash   comm   size   card   資料類型   value   存在   

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 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash 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> 

　　

MySQL -- Innodb中的change buffer