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:

ColumnDescription
NAMEUnique counter name.
SUBSYSTEMInnoDB 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.
COUNTCount since being enabled.
MAX_COUNTMaximum value since being enabled.
MIN_COUNTMinimum value since being enabled.
AVG_COUNTAverage value since being enabled.
COUNT_RESETCount since last being reset.
MAX_COUNT_RESETMaximum value since last being reset.
MIN_COUNT_RESETMinimum value since last being reset.
AVG_COUNT_RESETAverage value since last being reset.
TIME_ENABLEDTime last enabled.
TIME_DISABLEDTime last disabled
TIME_ELAPSEDTime since enabled
TIME_RESETTime last reset.
STATUSWhether the counter is currently enabled to disabled.
TYPEItem type; one of counter, value, status_counter, set_owner, set_member.
COMMENTCounter 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 NameSubsytem Field
module_metadatametadata
module_locklock
module_bufferbuffer
module_buf_pagebuffer_page_io
module_osos
module_trxtransaction
module_purgepurge
module_compresscompression
module_filefile_system
module_indexindex
module_adaptive_hashadaptive_hash_index
module_ibuf_systemchange_buffer
module_srvserver
module_ddlddl
module_dmldml
module_logrecovery
module_icpicp

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.

Comments

Comments loading...
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.