InnoDB Insert Buffer Insertion buffers Understanding

Today when doing a big business of data removal, see the following performance graphs

After the delete action begins, the size of the insert buffer increases to 140. For descriptions of these status parameters

InnoDB Insert Buffer

Insert buffering, not part of the cache, but physical pages, inserts or updates for nonclustered indexes, not every time the index page is inserted directly. Instead, determine whether the inserted nonclustered index page is in the buffer pool. If in, then insert directly, if no longer, It is placed in an insert buffer. The merge operation of the insert buffer and nonclustered index page child nodes is then performed at a certain frequency.
Usage criteria: Nonclustered index, non-unique

1. IBUF Inserts
   Number of records inserted
2. IBUF merged
   The number of merged pages
3. Ibuf Merges
   Number of merges

If the value of Merges/merged equals 3/1, the IO request for the non-clustered index page is approximately 3 times times lower for the insert buffer

InnoDB Insert Buffer usageibuf Cell count fragment size ibuf used cells insert buffer size IBUF free cells " Free list " the length of

It can be understood that, in peacetime normal business, need to merge the level two index basically no, in bulk Delete, there are many need to merge the level of two index changes.

See how many IO requests are saved by the merge operation (1034310+3)/113909=9.08,

-------------------------------------INSERT BUFFER and ADAPTIVE HASH INDEX-------------------------------- -----1, free list len 134, seg size 136, 1139093, delete mark 2319764, delete 10343100, delete Mark 0, delete 0288996893, node heap has 304687 buffer (s)1923.58 hash searches/s, 1806.60 non-hash Searc hes/s

Excerpt from a friend's blog about insert buffer

One, insert buffering (insert buffer/changebuffer): Improve insert performance      only for nonclustered indexes (not unique) inserts and updates are valid, for each insert is not written to the index page, but first to determine whether the inserted nonclustered index page is in the buffer pool, If it is inserted directly, if it is not, it is placed in insert Buffer First, then the merge operation is performed at a certain frequency. This typically allows multiple insertions to be combined into one operation, improving insert performance. Conditions for using insert buffering:* Nonclustered index * non-unique insert buffer maximum usage space is 1/2 buffer pool size, cannot be resized, in plugin InnoDB, upgrade to change buffer. Not only the insert, but also the update, delete are valid. The parameters are: innodb_change_buffering, the values set are: Inserts, deletes, purges, changes (inserts and Deletes), all (default), none. Parameters can be used to control the size of their use: innodb_change_buffer_max_size, which defaults to 25, which is 1/4 of the buffer pool. The maximum can be set to 50. was introduced in 5.6. The above mentioned the merger at a certain frequency, what is the condition of the so-called frequency? 1) The secondary index page is read into the buffer pool. The normal select first checks whether the insert buffer has the nonclustered index page present, and if so, merges the insert. 2) The secondary index page does not have free space. Space is less than 1/32 page size, the merge operation is forced. 3) The Master Thread merges operations per second and every 10 seconds.

InnoDB buffer pool contains data page types such as: index page, data page, undo page, insert buffer (insert buffer), Adaptive Hash Index, INNODB store is lock information, data dictionary information, etc., structure diagram as follows

There are a few questions to answer.

1: Why is insert Buffer,insert buffer going to help us solve the problem?

What are the limitations of 2:insert buffer and why are there these restrictions?

Let's start with the first question.

As a practical example to illustrate, we go to the library to return books, corresponding to the library, he did insert (add) operation, the administrator in 1 hours received 100 books, this time he has 2 ways to return the book back to the Bookshelf

1) Each return a book, according to the Code of the book (bookcase area-row-number) to send the book back to the Rack

2) temporarily do not do the return operation, first put on the cabinet surface, and so on when not busy, and then the book according to the bookcase area-row-number first row, and then a one-time return

Using Method 1, the administrator needs to enter and exit (IO) collection Area 100 times, keep climbing climb low to complete the book return operation, Red, the efficiency is very poor.

With Method 2, the administrator only needs access to the (IO) collection Area 1 times, the same location of the book, no matter how many, as long as climbing a staircase, greatly reducing the workload of the Administrator.

So the library is in accordance with the method and the other to do the book action. But you have to say that my library is easy to manage with 20 books, a 0.5-metre shelf, methods 2 and 1, which are not in the scope of our discussion. When the amount of data is very small, there is no efficiency problem.

relational database in the processing of the insert operation, the method is similar to the above, each insert is equivalent to a book, it also needs a counter to save the inserted data, and then classified archives, do not busy when the batch of the return to the position. This counter is insert buffer.

I think that's why there are insert buffer, more in the performance optimization considerations.

Second question, what is the limit: "Only for nonclustered indexes (non-unique) inserts and updates are valid"

Why are inserts and updates valid for nonclustered indexes (not unique)?

Or return to the example, also a book A to the library, the administrator to determine whether the book is unique, he is not visible on the counter, must climb to the specified location to confirm that the process has already produced an IO operation, equivalent to no savings in any operation.

So this buffer only handles non-unique insertions, and does not require that the judgment be unique. The clustered index, needless to say, is certainly unique, and MySQL is now only clustered through the primary key.

Inside MySQL, the size of the insert buffer can be set within the code to the maximum of 50% of the entire InnoDB buffer pool size. This is actually unscientific, can imagine a 100 square meters of the library, 50 square meters is to do the back of the book counter is what look like?

In front of the administrator's book, he will choose to do in the "not busy time" to do, the priority to deal with the front desk cancellation operation, which is reflected in MySQL:

1) Every 1 seconds, if the number of Io is less than 5, merge insert buffer.

2) Every 10 seconds, the number of Io is less than 200, merging up to 5 insert buffers.