MySQL--Change buffer in InnoDB

Change buffer is a special data structure that is used to modify the secondary index page when the secondary index page to be modified is not in the buffer pool. The operations on the secondary index page may be insert, UPDATE, and delete operations. When the relevant index page is read into buffer pool, the secondary index page is modified (that is, the merge operation) using the contents of the change buffer.

Unlike clustered indexes, secondary indexes are often not unique, and inserting secondary indexes is often random. Similarly, deletions and updates to secondary indexes are often discontinuous.

When the associated index page is read into buffer pool, modifying the secondary index page using the content in change buffer (that is, the merge operation) avoids a large number of disk random-access I/O.

Intermittently, during system idle or shutdown, the purge operation is performed, and the new index page is written to disk. Purge operations write multiple index values at once is more efficient than writing to disk immediately after each modification.

The merge to change buffer may take several hours if the number of secondary index rows being updated is more. During the merge process, the disk's I/O increases and may cause a decrease in performance for other queries.
The merge operation may also occur after a transaction commits. In fact, the merge operation may occur even after the instance is restarted.

In memory, the change buffer takes up space in buffer pool, and on the physical disk, changes buffer is part of system tablespace, so modifications to the index still exist after the database restarts.

The change buffer contains attributes that are also known as change buffering, including insert buffering, delete buffering, purge buffering.

The data type and total amount in change buffer are configured by the parameters innodb_change_buffering and innodb_chagne_buffer_max_size. View information about the data in the change buffer, which can be viewed through show engine InnoDB status.

The change buffer is called insert buffer in the old version.

1. Monitor 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, node Heap has 1 buffer (s) 13577.57 hash searches/s, 202.47 non-hash searches/s ...

2. View Information_schema
Information_schema.innodb_metrics provides the statistics name and description for 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 provides metadata for each page in the buffer pool, including the Change Buffer index page and the change Buffer bitmap page.
The change buffer page Page_type.ibuf_index represents the Change Buffer index page; Page_type.ibuf_bitmap represents the Change buffer bitmap page.

Warning: Viewing innodb_buffer_page can have a significant performance overhead. It is best to perform in idle time or test environment.

For example, you can view the index page for change buffer and the bitmap page to occupy the ratio of 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 |+---------------------+-------------+-------------------------------+|                  |        8192 |                        0.1709 |+---------------------+-------------+-------------------------------+1 row in Set (0.05 sec)

Performance_schema also provides a wait instruction for the change buffer mutex for advanced performance monitoring:

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. Configure Change Buffer

You can use the parameter innodb_change_buffering to control whether change buffer is enabled.

--all: Default value. Open buffer inserts, delete-marking operations, Purges--none: Do not turn on change buffer--inserts: Just open the buffer insert operation--deletes: Just open the delete-marking operation--changes: Turn on the buffer insert operation and the delete-marking operation--purges: Turn on the buffer function for just the physical deletion performed in the background

Starting with 5.6.2, the parameter innodb_change_buffer_max_size sets the percentage of the change buffer that can occupy the buffer pool, which is 25 by default and can be set to a maximum of 50.

Mysql> Show variables like ' innodb_change_buffer_max_size '; +-------------------------------+-------+| Variable_name                 | Value |+-------------------------------+-------+| Innodb_change_buffer_max_size | 25    

　　

MySQL--Change buffer in InnoDB