What do the InnoDB insert buffer statistics mean?
Ever seen this in SHOW INNODB STATUS and wondered what it means?
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 4634, seg size 4636,
I’ve never been quite sure, and Peter didn’t really clarify things himself, so I took a look at the source. If I’m not mistaken, the seg size is really one more than the number of records the insert buffer can hold, the free list length is the number that aren’t in use, and the size is just seg_size - (1 + free_list_len). These seem to be kept in lockstep as records are inserted and merged.
I think those list the number of pages, not records and are related by:
data->size = data->seg_size – (1 + data->free_list_len);
Mark Callaghan
26 Oct 09 at 1:28 pm
Thanks. So you concur that the equation holds over time? My reading of the source says yes, but as you can see I’m not as expert as I could be.
Xaprb
27 Oct 09 at 9:28 pm
I copied that from the code, so it should hold. The other interesting number is:
(’merged records’ – ‘merges’) * 2
That is an estimate for the IO (reads+writes) saved by the insert buffer.
Mark Callaghan
27 Oct 09 at 9:34 pm
Right, I saw the same code — but I think that code is only executed when the insert buffer is initialized, and I wanted to additionally understand whether it holds as the insert buffer is modified over time, which I believe it is.
Xaprb
27 Oct 09 at 9:41 pm
s/believe it is/believe it does/g
Xaprb
27 Oct 09 at 9:42 pm
The code I copied is from ibuf_data_sizes_update and cscope shows it is called from ibuf_data_init_for_space, ibuf_insert_low and ibuf_delete_rec. I assume it is called after init.
Mark Callaghan
27 Oct 09 at 9:45 pm
[...] Schwartz also has a question for you, what do the InnoDB insert buffer statistics mean?: Ever seen this in SHOW INNODB STATUS and wondered what it means? [...]
Log Buffer #167: a Carnival of the Vanities for DBAs | Pythian Group Blog
30 Oct 09 at 1:04 pm