W3cubDocs

/MariaDB

Information Schema INNODB_METRICS Table

MariaDB starting with 10.0.0

The INNODB_METRICS table was added in MariaDB 10.0.0

The Information Schema INNODB_METRICS table contains a list of useful InnoDB performance metrics. Each row in the table represents an instrumented counter that can be stopped, started and reset, and which can be grouped together by module.

The PROCESS privilege is required to view the table.

It has the following columns:

Column Description
NAME Unique counter name.
SUBSYSTEM InnoDB subsystem. See below for the matching module to use to enable/disable monitoring this subsytem with the innodb_monitor_enable and innodb_monitor_disable system variables.
COUNT Count since being enabled.
MAX_COUNT Maximum value since being enabled.
MIN_COUNT Minimum value since being enabled.
AVG_COUNT Average value since being enabled.
COUNT_RESET Count since last being reset.
MAX_COUNT_RESET Maximum value since last being reset.
MIN_COUNT_RESET Minimum value since last being reset.
AVG_COUNT_RESET Average value since last being reset.
TIME_ENABLED Time last enabled.
TIME_DISABLED Time last disabled
TIME_ELAPSED Time since enabled
TIME_RESET Time last reset.
STATUS Whether the counter is currently enabled to disabled.
TYPE Item type; one of counter, value, status_counter, set_owner, set_member.
COMMENT Counter description.

Enabling and disabling counters

Most of the counters are disabled by default. To enable them, use the innodb_monitor_enable system variable. You can either enable a variable by its name, for example:

SET GLOBAL innodb_monitor_enable = icp_match;

or enable a number of counters grouped by module. The SUBSYSTEM field indicates which counters are grouped together, but the following module names need to be used:

Module Name Subsytem Field
module_metadata metadata
module_lock lock
module_buffer buffer
module_buf_page buffer_page_io
module_os os
module_trx transaction
module_purge purge
module_compress compression
module_file file_system
module_index index
module_adaptive_hash adaptive_hash_index
module_ibuf_system change_buffer
module_srv server
module_ddl ddl
module_dml dml
module_log recovery
module_icp icp

There are four counters in the icp subsystem:

SELECT NAME, SUBSYSTEM FROM INNODB_METRICS WHERE SUBSYSTEM='icp';
+------------------+-----------+
| NAME             | SUBSYSTEM |
+------------------+-----------+
| icp_attempts     | icp       |
| icp_no_match     | icp       |
| icp_out_of_range | icp       |
| icp_match        | icp       |
+------------------+-----------+

To enable them all, use the associated module name from the table above, module_icp.

SET GLOBAL innodb_monitor_enable = module_icp;

The % wildcard, used to represent any number of characters, can also be used when naming counters, for example:

SET GLOBAL innodb_monitor_enable = 'buffer%'

To disable counters, use the innodb_monitor_disable system variable, using the same naming rules as described above for enabling.

Counter status is not persistent, and will be reset when the server restarts. It is possible to use the options on the command line, or the innodb_monitor_enable option only in a configuration file.

Resetting counters

Counters can also be reset. Resetting sets all the *_COUNT_RESET values to zero, while leaving the *_COUNT values, which perform counts since the counter was enabled, untouched. Resetting is performed with the innodb_monitor_reset (for individual counters) and innodb_monitor_reset_all (for all counters) system variables.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/information-schema-innodb_metrics-table/