MySQL Insert buffer detailed

The change buffer is a special data structure, caches changes to secondary index pages, affected pages The buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), is merged later when the pages ar e loaded into the buffer pool by other read operations.

The change Buffer/insert buffer is a record that caches non-unique cable index inserts, UPDATE, delete operations, and merges when other operations load the index data into the buffer.

InnoDB when changes are made to a non-unique index, inserting data into the insert buffer allows the transaction to feel that the data has been flushed to the hard drive to promote transaction execution efficiency, and to incrementally refresh the index pages according to the internal algorithm, thus merging multiple index operations into one operation to improve efficiency.

Insert buffer first caches the records of the secondary index, which must ensure that the cached records in one index page do not cause the splitting of the page, and that the cached data is actively read when the index page is merged, and the InnoDB engine has an insert buffer The bitmap page records the remaining space for each secondary index page, which must be actively merged when the remaining space is less than the internally defined threshold, and the insert buffer bitmap can only trace the free space on one secondary index page 2KB, so the insert Buffer can only cache one secondary index page with a total size of only 2KB.

Insert buffer can directly look at a B+tree index tree, because the structure and the B+tree index are similar, when an index page data merge operation, the index tree of insert buffer also needs to do a balanced operation, the entire insert The buffer index tree holds x-latch, blocking other operations on the insert buffer, of course, the X-latch holding time is very short, and the efficiency of merging multiple secondary indexes is significantly increased.

When merging data, the insert buffer lock process is:

Index leaf page x-latch->insert buffer tree X-latch->insert buffer page X-LATCH->FSP (module) x-latch

The insert buffer plus lock has a priority and can no longer hold a lock on a layer when holding a lock on a layer, which is different from the B+tree index, and avoids deadlocks when the insert buffer is high concurrency.

In memory, the change buffer occupies part of the InnoDB buffer pool. On disk, the change buffer was part of the system tablespace, so, index changes remain buffered across database restart S.

The in-memory change buffer is stored in the InnoDB buffer, and the hard disk is stored in the system table space, so the database can also be merged after the restart without losing data.

The innodb_change_buffering controls what type of data the buffer space stores, and the configurable items are:

All: Default value, cache insert, tag Delete, and background physical cleanup

None: Data generated by any operation is not cached

Inserts: Cache insert-generated data

Deletes: Cache tag removal changes to secondary indexes

Merge items for Changes:inserts and deletes

Purges: Data generated by internal physical cleanup

Innodb_change_buffer_max_size: Set Insert buffer space size, default is 25, maximum is 50, the value is innodb_buffer_pool_size percent

Insert buffer state to view the value of the insert buffer and ADAPTIVE HASH index:

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 138389, node heap has 0 buffer (s)

Hash table Size 138389, node heap has 0 buffer (s)

Hash table Size 138389, node heap has 0 buffer (s)

Hash table Size 138389, node heap has 0 buffer (s)

Hash table Size 138389, node heap has 0 buffer (s)

Hash table Size 138389, node heap has 0 buffer (s)

Hash table Size 138389, node heap has 1 buffer (s)

Hash table Size 138389, node heap has 0 buffer (s)

0.00 Hash SEARCHES/S, 0.00 Non-hash SEARCHES/S

You can also query to occupy the buffer data page in the following table in the INFORMATION_SCHEMA library

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 |

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

|        41 |          32764 | 0.1251 |

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

This article is from the "Eye Brother" blog, please be sure to keep this source http://2364914.blog.51cto.com/2354914/1755265

MySQL Insert buffer detailed