[MySQL 5.6] Innodb New Monitoring Table Innodb_metrics

In addition to the performance schema, a new INFORMATION_SCHEMA table is provided in MySQL 5.6 to monitor the INNODB internal running state--innodb_metrics; The table maintains a set of counters,

These counters allow the user to monitor the health of the INNODB internal operation. There are 210 counters in the current MySQL5.6.12 version:

Mysql> Select COUNT (*) from Innodb_metrics;

+ ———-+

| COUNT (*) |

+ ———-+

| 210 |

+ ———-+

1 row in Set (0.00 sec)

Mysql> Select COUNT (*) from innodb_metrics where status = ' disabled ';

+ ———-+

| COUNT (*) |

+ ———-+

| 148 |

+ ———-+

1 row in Set (0.00 sec)

Mysql> Select COUNT (*) from innodb_metrics where status = ' enabled ';

+ ———-+

| COUNT (*) |

+ ———-+

| 62 |

+ ———-+

1 row in Set (0.00 sec)

62 Counters Open By default

The Innodb_metrics table includes the following columns (excerpted from official documents)

Column Name	Description
NAME	Unique name for the counter.
SUBSYSTEM	The aspect of InnoDB, the metric applies to. See the list following, the table for the corresponding module names, and the SET GLOBAL syntax.
COUNT	Value since the counter is enabled.
Max_count	Maximum value since the counter is enabled.
Min_count	Minimum value since the counter is enabled.
Avg_count	Average value since the counter is enabled.
Count_reset	Counter value since it was last reset. ( the _reset Fields act like the lap counter on a stopwatch:you can measure the activity during some time interval, wh Ile The cumulative figures is still availablein the COUNT, Max_count, and so on fields.)
Max_count_reset	Maximum counter value since it is last reset.
Min_count_reset	Minimum counter value since it is last reset.
Avg_count_reset	Average counter value since it is last reset.
Time_enabled	Timestamp of last start.
Time_disabled	Timestamp of Last stop.
Time_elapsed	Elapsed time in seconds since the counter started.
Time_reset	Timestamp of Last stop.
STATUS	Whether The counter is still running () or stopped ().
TYPE	Whether the item is a cumulative counter, or measures the current value of some resource.
COMMENT	Additional description.

For example, we want to query the amount of DML execution:

Mysql> Select status, Name, COUNT, SUBSYSTEM from Innodb_metrics where NAME is like '%dml% ';

+ ———-+ ———————-+ ——-+ ——— –+

| Status | NAME | COUNT | SUBSYSTEM |

+ ———-+ ———————-+ ——-+ ——— –+

| Disabled |     purge_dml_delay_usec | 0 | Purge |

| Enabled |   Dml_reads | 942 | DML |

| Enabled |     Dml_inserts | 0 | DML |

| Enabled |     Dml_deletes | 0 | DML |

| Enabled |   Dml_updates | 913 | DML |

+ ———-+ ———————-+ ——-+ ——— –+

5 rows in Set (0.00 sec)

We can control the setting of the counter by following several variables:

Mysql> Show variables like '%monitor% ';

+ ———————— –+ ——-+

| variable_name | Value |

+ ———————— –+ ——-+

|       innodb_monitor_disable | |

|       innodb_monitor_enable | |

|       Innodb_monitor_reset | |

|       Innodb_monitor_reset_all | |

+ ———————— –+ ——-+

4 rows in Set (0.00 sec)

Let's take AHI-related counters for example, by default they're off

Mysql> Select status, name, subsystem from innodb_metrics where status = ' disabled ' and subsystem like '%adaptive_hash_ index% ';

+ ———-+ —————————————— + ——————— +

| Status | name | subsystem |

+ ———-+ —————————————— + ——————— +

| Disabled | Adaptive_hash_searches_btree | Adaptive_hash_index |

| Disabled | adaptive_hash_pages_added | Adaptive_hash_index |

| Disabled | adaptive_hash_pages_removed | Adaptive_hash_index |

| Disabled | adaptive_hash_rows_added | Adaptive_hash_index |

| Disabled | adaptive_hash_rows_removed | Adaptive_hash_index |

| Disabled | Adaptive_hash_rows_deleted_no_hash_entry | Adaptive_hash_index |

| Disabled | adaptive_hash_rows_updated | Adaptive_hash_index |

+ ———-+ —————————————— + ——————— +

7 Rows in Set (0.00 sec)

To open a counter:

mysql> Set Global innodb_monitor_enable = ' adaptive_hash_% ';

Query OK, 0 rows Affected (0.00 sec)

To close the counter:

mysql> Set Global innodb_monitor_disable = ' adaptive_hash_% ';

Query OK, 0 rows Affected (0.00 sec)

Reset the values of all AHI columns:

mysql> Set Global Innodb_monitor_reset_all = "adaptive_hash_%";

Query OK, 0 rows Affected (0.00 sec)

Reset only the value of the counter:

mysql> Set Global Innodb_monitor_reset = "adaptive_hash_%";

Query OK, 0 rows Affected (0.00 sec)

Open according to module name:

mysql> Set Global innodb_monitor_enable = Module_adaptive_hash;

Query OK, 0 rows Affected (0.00 sec)

To open all counters:

mysql> Set Global innodb_monitor_enable = ALL;

Query OK, 0 rows Affected (0.00 sec)

To close all counters:

mysql> Set Global innodb_monitor_disable = ALL;

Query OK, 0 rows Affected (0.00 sec)

We can set a specific counter, either through wildcards, or through the module name, or all, to set the counter, the configuration is quite flexible.

The corresponding relationship between module name and subsystem:

Module name	Correspondence Subsystem	Describe
Module_metadata	Metadata	Table-level Open, close, number of references, etc.
Module_lock	Lock	Lock system related information, such as the number of deadlocks, creation/removal/request of record locks, including table lock statistics, lock wait/hold time, etc...
Module_buffer	Buffer	Operations related to buffer pool,
Module_buf_page	Buffer_page_io	Buffer pool do a count of write operations
Module_os	Os	Data read and write information of OS layer
Module_trx	Transaction	Transaction volume statistics, such as read-only transactions, write transactions, rollback transactions, active transactions, transaction undo information, and so on.
Module_purge	Purge	Purge operations statistics, such as Purge tag deleted records tree, Purge undo Log page number, etc.
Module_compress	Compression	Compress table related statistics, such as compression, decompression, increase/decrease the number of padding and so on.
Module_file	File_system	Only one counter:file_num_open_files indicates the number of open files
Module_index	Index	Number of index splits and merges
Module_adaptive_hash	Adaptive_hash_index	Adaptive hash-related operations
Module_ibuf_system	Change_buffer	Change buffer related operations statistics
Module_srv	Server	Instance internal running state, such as BP size, page size, master thread information, spin statistics, read and write lock information, write double write buffer count
Module_ddl	Ddl	DDL statistics
Module_dml	Dml	Number of read/insert/delete/update
Module_log	Recovery	Information related to redo log, such as Reodo CHECKPOINR information, flush information, synchronous/asynchronous brush log points, log write volume, pending log requests, etc...
Module_icp	Icp	Information about the index condition Pushdown in the INNODB layer

[MySQL 5.6] Innodb New Monitoring Table Innodb_metrics