MySQL Bugs: #70213: INFORMATION_SCHEMA.innodb_metrics documentation incomplete

Bug #70213	INFORMATION_SCHEMA.innodb_metrics documentation incomplete
Submitted:	2 Sep 2013 10:52	Modified:	19 Nov 2013 18:25
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6.13,5.7.1	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any

Description:
The documentation for innodb_metrics is incomplete as it does not mention (or confirm) how the startup configuration via /etc/my.cnf should be adjusted so that non-default settings can be enabled.

[ my interest was to see the log_* entries so I can see the innodb checkpoint age and thus avoid parsing SHOW ENGINE INNODB STATUS output, but the issue is generic ]

It's also not very clear how /etc/my.cnf should be configured when multiple configuration settings of this type are needed, something that is likely to be useful.

Configuration of this table is not consistent with configuration of for example performance_schema. This is confusing.

How to repeat:
I see that on a 5.6.13 server:

root@myhost [(none)]> select name, subsystem, count, status from information_schema.innodb_metrics where subsystem = 'recovery';
+-------------------------------+-----------+-----------+----------+
| name                          | subsystem | count     | status   |
+-------------------------------+-----------+-----------+----------+
| log_checkpoints               | recovery  |         0 | disabled |
| log_lsn_last_flush            | recovery  |         0 | disabled |
| log_lsn_last_checkpoint       | recovery  |         0 | disabled |
| log_lsn_current               | recovery  |         0 | disabled |
| log_lsn_checkpoint_age        | recovery  |         0 | disabled |
| log_lsn_buf_pool_oldest       | recovery  |         0 | disabled |
| log_max_modified_age_async    | recovery  |         0 | disabled |
| log_max_modified_age_sync     | recovery  |         0 | disabled |
| log_pending_log_writes        | recovery  |         0 | disabled |
| log_pending_checkpoint_writes | recovery  |         0 | disabled |
| log_num_log_io                | recovery  |         0 | disabled |
| log_waits                     | recovery  |         0 | enabled  |
| log_write_requests            | recovery  | 921538576 | enabled  |
| log_writes                    | recovery  | 228961048 | enabled  |
+-------------------------------+-----------+-----------+----------+
14 rows in set (0.00 sec)

Note: that some entries are enabled and others are not.
Given my need I need to now configure this table to enable all these entries

Suggested fix:
In my case I did this dynamically:

set global innodb_monitor_enable = module_log;

but it would be convenient if the documentation gave a couple of examples of this and showed the correct way to enable multiple entries and multiple modules, as with some configuration settings this is done by adding comma separated entries, and in others mysqld expects separate lines to be configured in /etc/my.cnf.

Thus I'd suggest adding a section: configuration that
(1) mentions the default settings which are enabled. having to look for each version is not easy / helpful.
(2) mention how to change the running config (the documentation is mainly there)
(3) mention how to change the configuration on startup, e.g. enabling module_log and say  metadata_table_handles_opened, metadata_table_handles_closed which would be a combination of using a module name and a specific row.

Given the comment "The items represented in the innodb_metrics table are subject to change, so for the most up-to-date list, query a running MySQL server.". Please change this.

DO NOT CHANGE entries on minor version updates, unless you just ADD new entries.

If you make changes please document when they changed.

So something like:

Changes:
- 5.6.20 added log_some_new_feature (default: disabled)
- 5.7.1 added xxx_v1, xxx_v2, xxx_v3 (default: all enabled)
- 5.7.1 changed log_xxx (now disabled, was enabled)

Please also note: as discussed elsewhere, configuration of this table is different to for example configuration of the performance_schema tables. This inconsistency is a nuisance for any programs that want to dynamically adjust the configuration of a running server based on it's /etc/my.cnf setttings, or simply check if they are consistent.  Please fix this as soon as reasonably possible.

Thank you for the bug report.

So I've just checked (5.6.13) what happens with a few different settings in /etc/my.cnf

# this works
innodb_monitor_enable = module_log,metadata_table_handles_opened,metadata_table_handles_closed

# this does not work - only the last entry is actually enabled (5.6.13)
innodb_monitor_enable = module_log
innodb_monitor_enable = metadata_table_handles_opened
innodb_monitor_enable = metadata_table_handles_closed

The latter of course is inconsistent with:

[root@myhost-01 ~]# grep repl /etc/my.cnf
replicate-wild-do-table = mysql.%
replicate-wild-do-table = mydb.%
[root@myhost-01 ~]#

So good clear documentation would be helpful here to avoid confusion and mistakes.

Compare again to the same server's P_S configuration:

{noformat}
[root@myhost-01 ~]# grep perfor /etc/my.cnf
performance_schema = 1
# performance_schema.setup_consumers begin
performance_schema_consumer_events_waits_current = ON
performance_schema_consumer_events_waits_history = ON
# performance_schema.setup_consumers end

Discussed with Andrii, assigning to myself since Paul's currently unavailable.

Thank you for the bug report. Unfortunately, we are not able to address every request but here's what's been done:

(1) Information has been added outlining the semantics for enabling counters using the my.cnf file.
(2) The "inconsistent" semantics are "as designed" but the nuisance of semantics that differ from performance_schema semantics, for example, has been noted by the MySQL Development team. 
(3) The MySQL Development team has noted the request to refrain from changing or deleting counters in minor releases and will avoid doing so. New counter additions will continue to occur in any release, as new features and functionality are added.
(4) Related documentation updates:
* Expanded examples
* An updated list of counters (note that counters may differ by platform).
* A recommendation has been added to check release notes and review the innodb_metrics table for new releases prior to upgrading. 
* Information regarding why some counters are enabled by default (Counters that are enabled by default correspond to those used by SHOW ENGINE INNODB STATUS.)

Documentation changes should show up in the next day or so.