MySQL Bugs: #71328: Manual does not describe how to monitor InnoDB change buffer usage

Bug #71328	Manual does not describe how to monitor InnoDB change buffer usage
Submitted:	9 Jan 2014 14:27	Modified:	26 May 2015 13:05
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any
Tags:	change buffering, innodb, innodb_change_buffer_max_size, missing manual

Description:
Manual describes how change buffer works and what options are there to control it, at http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-performance-change_b.... I see two problems in that section:

1. It does not say anything about new 5.6 option to control memory allocated for the change buffer, http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_change_buffer_...

2. It does not explain how to monitor change buffer usage, for example it's current size. In 5.6 we have several rows in the INFORMATION_SCHEMA.INNODB_METRICS for this:

mysql> select name, `count` from information_schema.innodb_metrics where name like 'ibuf%';
+---------------------------------+--------+
| name                            | count  |
+---------------------------------+--------+
| ibuf_merges_insert              |      0 |
| ibuf_merges_delete_mark         | 519100 |
| ibuf_merges_delete              |      0 |
| ibuf_merges_discard_insert      |      0 |
| ibuf_merges_discard_delete_mark |      0 |
| ibuf_merges_discard_delete      |      0 |
| ibuf_merges                     |   3613 |
| ibuf_size                       |      1 |
+---------------------------------+--------+
8 rows in set (0.01 sec)

(maybe more). In all versions we have this section in INNODB STATUS:

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 174, seg size 176, 3613 merges
merged operations:
 insert 0, delete mark 519100, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0

So, manual should explain these values and their use to monitor and set proper size of change buffer for the load.

If there are some other ways to control/monitor usage of change buffer, describe them.

How to repeat:
Try to find out, based on the manual, how large your InnoDB change buffer is and how it is used (if at all).

Suggested fix:
Describe all know ways to monitor and control InnoDB change buffer usage in all versions.

Hello Valeriy,

Thank you for the bug report.

Thanks,
Umesh

Posted by developer:
 
Information about change buffer monitoring has been added to the following pages:

https://dev.mysql.com/doc/refman/5.5/en/innodb-insert-buffering.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-insert-buffering.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-insert-buffering.html

Information about configuring change buffer maximum size has been added here:

https://dev.mysql.com/doc/refman/5.6/en/innodb-change-buffer-maximum-size.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer-maximum-size.html

The latest content should appear on-line within 24 hours.

Thank you for the bug report.