Three features of Innodb: insertbuffer_MySQL

I. What is insertbufferinsertbuffer? it is a special data structure (B + tree). It is not a part of the cache, but a physical page. when the affected index page is not in the bufferpool, it caches the changes of secondaryindexpages, when bufferpage reads buffe .. 1. what is insert buffer?

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

At the beginning, only insert operations are supported. Therefore, insert buffer is now called change buffer.

Insert buffer only applies to non-unique secondary indexes, that is, it can only be used for non-unique indexes. The reason is as follows:

1. the primary key is inserted in an ascending order. Abnormal insertion of the clustered index is generally in the order of non-random I/O.

2. to write a unique index, check whether the record exists. Therefore, before modifying a unique index, you must read the index pages related to the modified records to see if they are unique. This makes no sense for Insert buffer. you need to read them (random IO)

So it is only valid for non-unique indexes.

II. principle of insert buffer

For non-unique indexes, the modification operation of secondary indexes is not to update the leaf pages of indexes in real time, but to cache several updates to the same page and merge them into one-time update operations to reduce IO, converting random IO to sequential IO can avoid performance loss caused by random IO and improve database write performance.

Procedure

First, judge that the page to be updated is not in the buffer pool.

A. If yes, insert it directly;

B. If not, store the index page into the Insert Buffer and merge the leaf nodes in non-unique indexes and index pages according to the scheduling rules of the Master Thread.

Master Thread scheduling rules

A. active merger [the innodb main thread is regularly completed, and the user thread is imperceptible]

Active merge uses the innodb main thread (svr_master_thread) to determine that if the I/O in the past 1 s is less than 5% of the system's I/O capability, the merge operation of the insert buffer is performed automatically. The number of pages on merge is 5% of the system's I/O capability. The Read mode is async io. Every 10 s, an insert buffer meger operation must be triggered. Meger's page size is still 5% of the system's I/O capability.

1) the main thread sends an async io request, and async reads the index page to be indexed by merge.

2) the I/O handler thread performs merge after receiving the complete async I/O.

B. passive merge [the user thread is completed, and the user can feel the performance impact of meger operations]

1) the insert operation causes insufficient page space and requires split ). Because insert buffer only targets a single page, it cannot buffer page split [the page is already in memory], hence the passive meger of the page. Similarly, the update operation causes insufficient page space, and the purge operation causes the page to be empty. In short, if the current operation causes page split or merge, it will lead to passive merge;

2) insert operations. for other reasons, insert buffer optimization returns false. passive merge is required when the page needs to be read. The difference is that pages on disk need to be read into memory;

3) when performing the insert buffer operation, we find that the insert buffer is too large and needs to be compressed. in this case, we need to force the passive merge and do not allow the insert operation.

III. internal implementation of insert buffer

1. the data structure of insert buffer is a B + tree. in versions earlier than MySQL4.1, each table has an insert buffer B + tree.

After MySQL4.1, there is only one insert buffer B + tree in the world, which is used to insert buffer the secondary indexes of all tables. This B + tree is stored in the shared tablespace, which is ibdata1 by default. Therefore, when you try to recover table data through the ibd file of an independent tablespace, the check table may fail. This is because the data in the secondary index of the table may still be in the insert buffer, that is, the shared tablespace. Therefore, after the idb file is restored, you need to perform the repair table operation to recreate all secondary indexes on the table.

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

Its construction includes three fields: space (4 byte) + marker (1 byte) + offset (4 byte) = search key (9 byte)

Space indicates the id of the tablespace where the record to be inserted is located. in InnoDB, each table has a unique space id. you can use the space id query to find the table;

Marker is used to be compatible with earlier versions of insert buffer;

Offset indicates the offset of the page.

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

4. for records inserted into insert buffer B + leaf nodes, the following rules should be constructed:

Space | marker | offset | metadata | secondary index record

After the insert buffer index is enabled, records on the secondary index page (space, page_no) may be inserted into the insert buffer B + tree. therefore, to ensure that each merge insert buffer page must be successful, A special page is also required to mark the available space of each secondary index page (space, page_no). the type of this page is insert buffer bitmap.

IV. Disadvantages of insert buffer

1. the instance recovery time may become longer after the database is down. If the application executes a large number of insert and update operations and involves non-unique clustered indexes, once a crash occurs, a large amount of inserted buffer data in the memory is not merged into the index page, the instance recovery takes a long time.

2. in case of intensive writing, the insert buffer will occupy too much buffer pool memory (innodb_buffer_pool). by default, it can occupy a maximum of 1/2, which may cause certain problems in practical applications.

3. insert buffer cannot be controlled. for different workloads and hardware configuration, especially when SSD is prevalent today

5. 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, 41 merged recs, 499 mergesHash 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 free within the change buffer. represents The number of idle pages.

Seg size: The size of the change buffer, in pages. The size of the inserted buffer is 2*16 kB.

Merges: The total number of change buffer merges. indicates The number of merge times.

Merged operations-insert: The number of inserted records merged. merged inserted records

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

Merged operations-delete: The number of purge records merged. merged clear 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.