MySQL Bugs: #80083: Setting innodb_monitor_enable to ALL does not enable all monitors

Bug #80083	Setting innodb_monitor_enable to ALL does not enable all monitors
Submitted:	20 Jan 2016 19:32	Modified:	13 May 2016 19:20
Reporter:	Davi Arnaut (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ALL, innodb_monitor_enable

Description:
Setting innodb_monitor_enable to ALL does not _effectively_ enable
all monitor counters. The problem is that when innodb_monitor_enable
is set to ALL it enables all monitor counters but does not enable
each individual monitor module.

Yet, in some parts of the code, like in buf_page_monitor, the
counters are only updated if the module is enabled. In particular,
this appears to affect all counters in the buffer_page_io module.

How to repeat:
mysql> CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> SET GLOBAL innodb_monitor_enable='ALL';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'buffer_page_written_index_%';
+-----------------------------------------+-------+
| NAME                                    | COUNT |
+-----------------------------------------+-------+
| buffer_page_written_index_leaf          |     0 |
| buffer_page_written_index_non_leaf      |     0 |
| buffer_page_written_index_ibuf_leaf     |     0 |
| buffer_page_written_index_ibuf_non_leaf |     0 |
| buffer_page_written_index_inode         |     0 |
+-----------------------------------------+-------+
5 rows in set (0.00 sec)

mysql> INSERT INTO t1 VALUES (1); FLUSH TABLES t1 FOR EXPORT; UNLOCK TABLES;
Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'buffer_page_written_index_%';
+-----------------------------------------+-------+
| NAME                                    | COUNT |
+-----------------------------------------+-------+
| buffer_page_written_index_leaf          |     0 |
| buffer_page_written_index_non_leaf      |     0 |
| buffer_page_written_index_ibuf_leaf     |     0 |
| buffer_page_written_index_ibuf_non_leaf |     0 |
| buffer_page_written_index_inode         |     0 |
+-----------------------------------------+-------+
5 rows in set (0.00 sec)

mysql> SET GLOBAL innodb_monitor_enable='%';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (2); FLUSH TABLES t1 FOR EXPORT; UNLOCK TABLES;
Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'buffer_page_written_index_%';
+-----------------------------------------+-------+
| NAME                                    | COUNT |
+-----------------------------------------+-------+
| buffer_page_written_index_leaf          |     3 |
| buffer_page_written_index_non_leaf      |     0 |
| buffer_page_written_index_ibuf_leaf     |     0 |
| buffer_page_written_index_ibuf_non_leaf |     0 |
| buffer_page_written_index_inode         |     1 |
+-----------------------------------------+-------+
5 rows in set (0.00 sec)

Suggested fix:
Enable all modules when innodb_monitor_enable is set to ALL.

Thank you for the bug report.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.12 Source distribution PULL: 2016-JAN-14

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > create database d;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > use d
Database changed
mysql 5.7 > CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > SET GLOBAL innodb_monitor_enable='ALL';
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 > SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'buffer_page_written_index_%';
+-----------------------------------------+-------+
| NAME                                    | COUNT |
+-----------------------------------------+-------+
| buffer_page_written_index_leaf          |     0 |
| buffer_page_written_index_non_leaf      |     0 |
| buffer_page_written_index_ibuf_leaf     |     0 |
| buffer_page_written_index_ibuf_non_leaf |     0 |
| buffer_page_written_index_inode         |     0 |
+-----------------------------------------+-------+
5 rows in set (0.00 sec)

mysql 5.7 > INSERT INTO t1 VALUES (1); FLUSH TABLES t1 FOR EXPORT; UNLOCK TABLES;
Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'buffer_page_written_index_%';
+-----------------------------------------+-------+
| NAME                                    | COUNT |
+-----------------------------------------+-------+
| buffer_page_written_index_leaf          |     0 |
| buffer_page_written_index_non_leaf      |     0 |
| buffer_page_written_index_ibuf_leaf     |     0 |
| buffer_page_written_index_ibuf_non_leaf |     0 |
| buffer_page_written_index_inode         |     0 |
+-----------------------------------------+-------+
5 rows in set (0.00 sec)

mysql 5.7 > SET GLOBAL innodb_monitor_enable='%';
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > INSERT INTO t1 VALUES (2); FLUSH TABLES t1 FOR EXPORT; UNLOCK TABLES;
Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'buffer_page_written_index_%';
+-----------------------------------------+-------+
| NAME                                    | COUNT |
+-----------------------------------------+-------+
| buffer_page_written_index_leaf          |     3 |
| buffer_page_written_index_non_leaf      |     0 |
| buffer_page_written_index_ibuf_leaf     |     0 |
| buffer_page_written_index_ibuf_non_leaf |     0 |
| buffer_page_written_index_inode         |     1 |
+-----------------------------------------+-------+
5 rows in set (0.02 sec)

Looks the problem is that when innodb_monitor_enable is set to "ALL",
it does not handle counters marked with MONITOR_GROUP_MODULE, which
indicates that counters cannot be turned on/off individually, instead
the counters need to be turned on/off as a group using the module
name. The "buffer_page_io" module has this flag set. This is handled
correctly when using wildcard update.

Posted by developer:
 
Fixed as of the upcoming 5.6.32, 5.7.14 release, and here's the changelog entry:

Setting innodb_monitor_enable to all did not enable all counters.

Thank you for the bug report.