Some little known facts about Innodb Insert Buffer

Despite being standard Innodb feature forever Insert Buffers remains some kind of mysterious thing for a lot of people, so let me try to explain thing a little bit.

Innodb uses insert buffer to “cheat” and not to update index leaf pages when at once but “buffer” such updates so several updates to the same page can be performed with single sweep. Insert buffer only can work for non-unique keys because until the merge is performed it is impossible to check if the value is unique.

Insert buffer is allocated in the Innodb system table space. Even though it is called “buffer” similar to “doublewrite buffer” it is really the space in the tablepace. Though it can be cached in the buffer pool same as other pages. This property allows insert buffer to survive transaction commits and even MySQL restarts. Really it may take weeks before the given index page is merged, though usually it is much sooner than that.

There are two ways of insert buffer merge is happening. First is on demand merge – if accessed page contains unmerged records in insert buffer the merge is performed before page is made available. This means insert buffer can slow down read operations.

The other way insert buffer is merged is by background thread. There are very little merges happening if system is loaded and merge process becomes more active if system is idle. This behavior can cause interesting results, like you had system lightly used and have very little IO activity, but when you remove the load from the system completely you see high IO load which goes for hours even after all buffer pool dirty pages are completed. This can be very surprising.

Stats about Innodb Insert Merge Buffer are available in SHOW INNODB STATUS output:

The “seg size” is a full allocated size of segment in pages. So in this case it is about 180MB
The “free list” is number of pages which are free – containing no unmerged records. The “size” is size (in pages) of insert buffer which is not merged.

The fact size is in pages is not really helpful because depending on the row size there can be different number of rows in the insert buffer – and it is rows we see in performance stats, for example to understand when insert buffer merge will be completed.

The “inserts” is number of inserts to insert buffer since start and number of merged records is number of records which were merged to their appropriate page locations since start. So we know in this case insert buffer has grown 534339 records since start. There is a temptation to use this number as count of unmerged rows in insert buffer but this would not be correct – insert buffer may not be empty at the start. So you can only tell insert buffer has at least this number of records. For the same reason do not get scared if you see more merged records than inserted.

The value of 2246304 merges shows us there was about 3 records merged for each merge operation, meaning insert buffer could in theory reduce IO needed to update leaf pages 3 times.

As I mentioned Insert buffer merge can take quite a while – with 100 records merged per second we’re looking at least 5343 seconds or 1.5 hours on this server… and there are insert buffers which are 10x and 100x larger than this.

Innodb unfortunately offers no control for insert buffer while it surely would be benefiting for different workloads and hardware configuration. For example there is very good question if insert buffer really makes sense for SSD because saving random IO is not so much needed for these devices.