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. |
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.
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.
© 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/