"MySQL" InnoDB three major features of insert buffer

First, what is insert buffer

Insert buffer is a special data structure (b + tree) that is not part of the cache, but a physical page that caches secondary index pages when the affected index page is not in buffer pool . when the buffer page is read into the buffer pool , the merge operation can be INSERT , UPDATE or DELETE operations (DML)

At the very beginning, it was only an insert, so called Insert buffer, now it's called change buffer.

Insert buffer applies only to Non-unique secondary indexes, which means that it can only be used on non-unique indexes for the following reasons

1, primary key is inserted in ascending order, the abnormal insertion of the clustered index is generally sequential, non-random IO

2 Write a unique index to check whether a record exists, so before modifying a unique index, you must read the modified record-related index page to know that it is not unique, so insert buffer is meaningless, read it (random io)

So only valid for non-unique indexes

Second, the principle of insert buffer

For non-unique indexes, the modification of the secondary index does not update the leaf pages of the index in real-time, but instead caches several updates to the same page, merging them into one-off update operations, reducing IO, and turning random io into sequential io, which avoids the performance loss of random IO . Improve the write performance of the database

Specific process

Decide which page to update is not in the buffer pool

A, if in, then directly inserted;

b, if not, the index page is saved in insert Buffer, and the leaf nodes in non-unique indexes and index pages are merged according to the master thread's scheduling rules

scheduling rules for Master thread

A, active merger[innodb the main thread of the regular completion, user thread no sense]

Active merge is judged by the InnoDB main thread (svr_master_thread): If I/o in the past 1s is less than 5% of the system I/O capability, a merge operation with insert buffer is active. The number of pages in the merge is 5% of the system I/O capability, read in async IO mode. Every 10s, the insert Buffer Meger operation must be triggered. The number of Meger pages is still 5% of the system I/O capability.

1) The main thread issues an async IO request, Async reads the index page that needs to be merge
2) I/O handler thread, after receiving the completed async I/O, merge

b, passive merge[user thread complete, user can feel the Meger operation brings the performance impact]

1) Insert operation, resulting in insufficient page space, need to split (split). Because the insert buffer is for a single page only, you cannot buffer page split[pages in memory], which causes the passive meger of the page. Similarly, the update operation causes insufficient page space, purge causes the page to be empty, and so on. In summary, if the current operation causes the page split or merge, then the passive merge will be caused;

2) insert operation, for various other reasons, insert buffer optimization returns FALSE, the need to actually read the page, the passive merge. Unlike a different one, the page is on disk and needs to be read into memory;

3) in the insert buffer operation, found that the insert buffer is too large to compress the insert buffer, you need to force the passive merge, do not allow the insert operation.

Iii. Internal implementation of insert buffer

1, insert buffer data structure is a B + tree, in the previous version of MySQL4.1 each table has an insert buffer B + Tree

After MySQL4.1, there is only one insert buffer B + tree in the global, which is responsible for insert buffer for all tables ' secondary indexes. This B + tree is stored in the shared tablespace , which is ibdata1 in the default. Therefore, attempting to recover data from a table by using a standalone tablespace ibd file often causes the check table to fail. This is because the data in the secondary index of the table may also be in the insert buffer, which is in the shared table space. Therefore, after recovering through the IDB file, a repair table operation is required to rebuild all secondary indexes on the table

2. The non-leaf node of insert buffer holds the search key (key value) of the query.

Its construction consists of three fields: space (4 byte) + marker (1byte) + offset (4byte) = Search key (9 byte)

Space indicates the tablespace in which the record is to be inserted id,innodb, each table has a unique space ID, which can be queried by Space ID to find out which table;

marker is used to accommodate the old version of insert buffer;

offset indicates the offset at which the page resides.

3, when a secondary index needs to be inserted into the page (space, offset), if the page is not in the buffer pool, then InnoDB first constructs a search key according to the above rules, next query insert buffer this B + tree, This record is then inserted into the leaf node of the insert buffer B + Tree

4. For records inserted into the insert buffer B + tree leaf node, it is necessary to construct the following rules:

Space | Marker | Offset | metadata | Secondary index record

When the Insert buffer index is enabled, records in the secondary index page (space, page_no) may be inserted into the insert buffer B + tree, so that each merge Insert buffer page must be successful, You also need a special page to mark the free space for each secondary index page (space, page_no), and the type of the page is insert buffer bitmap.

Iv. Disadvantages of Insert buffer

1. The instance recovery time may become longer after the database outage. If an application performs a large number of insert and update operations and involves a non-unique clustered index, a large amount of in-memory insert buffer data is not merged into the index page once the outage occurs, resulting in a long instance recovery time

2, in the case of dense writing, the insertion buffer will consume too much buffer pool memory (innodb_buffer_pool), by default, the maximum can occupy 1/2, which will bring some problems in practical application

3. Insert buffer cannot be controlled, for different workloads and hardware configuration, especially in SSD prevalence today

V. View INSERT Buffer

Mysql> Show engine InnoDB status \g-------------------------------------INSERT BUFFER and ADAPTIVE HASH INDEX-------------------------------------ibuf:size 1, free list len 0, seg size 2,41 inserts, merged RECs, 499 merges Hash table size 3984403, node heap has 967 buffer (s) 14.66 hash searches/s, 64.65 non-hash searches/s---log---log sequence Number 27233311008Log flushed  up to 27233311008Last checkpoint at 272333105930 pending log writes, 0 pending CHKP writes37848626 log I/O ' s done, 1.00 log I/O ' S/second

• size: The number of pages used within the change buffer. Change buffer size is equal to seg size - (1 + free list len) . The 1 + value represents the change Buffer header page.

• free list len: The number of pages within the change buffer. Represents the amount of free pages

• seg size: The size of the change buffer, in pages. The size of the insert buffer is 2 *16kb

• merges: The total number of the change buffer merges. Indicates how many merges

• merged operations - insert: The number of inserted records merged.merged Records inserted

• merged operations - delete mark: The number of deleted records merged.merged deleted records

• merged operations - delete: The number of purge records merged.merged purge records

• discarded operations - insert: The number of insert merge operations discarded.

• discarded operations - delete mark: The number of delete merge operations discarded.

• discarded operations - delete: The number of purge merge operations discarded.

Reference

Https://dev.mysql.com/doc/refman/5.5/en/innodb-standard-monitor.html

Https://dev.mysql.com/doc/refman/5.5/en/innodb-insert-buffering.html

https://www.percona.com/blog/2009/01/13/some-little-known-facts-about-innodb-insert-buffer/

http://blog.itpub.net/22664653/viewspace-1163838/

"MySQL" InnoDB three major features of insert buffer