The Information Schema INNODB_METRICS table contains a wide range of low-level performance metrics and counters for the InnoDB storage engine.
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:
NAME
Unique counter name.
Note: In MariaDB 10.4 and earlier the ENABLED column was called STATUS.
Most of the counters are disabled by default. To enable them, use the system variable. You can either enable a variable by its name, for example:
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:
There are four counters in the icp subsystem:
To enable them all, use the associated module name from the table above, module_icp.
The % wildcard, used to represent any number of characters, can also be used when naming counters, for example:
To disable counters, use the 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 (for individual counters) and (for all counters) system variables.
From MariaDB 10.6, the interface was simplified by removing the following variables:
buffer_LRU_batches_flush
buffer_LRU_batch_flush_pages
buffer_LRU_batches_evict
buffer_LRU_batch_evict_pages
And by making the following reflect the status variables:
buffer_LRU_batch_flush_total_pages:
buffer_LRU_batch_evict_total_pages:
The intention is to eventually remove the interface entirely (see ).
This page is licensed: CC BY-SA / Gnu FDL
module_purge
purge
module_compress
compression
module_file
file_system
module_index
index
module_adaptive_hash
adaptive_hash_index From , if is disabled (the default), adaptive_hash_index will not be updated.
module_ibuf_system
change_buffer
module_srv
server
module_ddl
ddl
module_dml
dml
module_log
recovery
module_icp
icp
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.
ENABLED
1 if enabled, 0 otherwise
TYPE
Item type; one of counter, value, status_counter, set_owner, set_member.
COMMENT
Counter description.
module_metadata
metadata
module_lock
lock
module_buffer
buffer
module_buf_page
buffer_page_io
module_os
os
module_trx
transaction
SET GLOBAL innodb_monitor_enable = icp_match;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 |
+------------------+-----------+SET GLOBAL innodb_monitor_enable = module_icp;SET GLOBAL innodb_monitor_enable = 'buffer%'SELECT name,subsystem,type,comment FROM INFORMATION_SCHEMA.INNODB_METRICS\G
*************************** 1. row ***************************
name: metadata_table_handles_opened
subsystem: metadata
type: counter
comment: Number of table handles opened
*************************** 2. row ***************************
name: lock_deadlocks
subsystem: lock
type: value
comment: Number of deadlocks
*************************** 3. row ***************************
name: lock_timeouts
subsystem: lock
type: value
comment: Number of lock timeouts
*************************** 4. row ***************************
name: lock_rec_lock_waits
subsystem: lock
type: counter
comment: Number of times enqueued into record lock wait queue
*************************** 5. row ***************************
name: lock_table_lock_waits
subsystem: lock
type: counter
comment: Number of times enqueued into table lock wait queue
*************************** 6. row ***************************
name: lock_rec_lock_requests
subsystem: lock
type: counter
comment: Number of record locks requested
*************************** 7. row ***************************
name: lock_rec_lock_created
subsystem: lock
type: counter
comment: Number of record locks created
*************************** 8. row ***************************
name: lock_rec_lock_removed
subsystem: lock
type: counter
comment: Number of record locks removed from the lock queue
*************************** 9. row ***************************
name: lock_rec_locks
subsystem: lock
type: counter
comment: Current number of record locks on tables
*************************** 10. row ***************************
name: lock_table_lock_created
subsystem: lock
type: counter
comment: Number of table locks created
...
*************************** 207. row ***************************
name: icp_attempts
subsystem: icp
type: counter
comment: Number of attempts for index push-down condition checks
*************************** 208. row ***************************
name: icp_no_match
subsystem: icp
type: counter
comment: Index push-down condition does not match
*************************** 209. row ***************************
name: icp_out_of_range
subsystem: icp
type: counter
comment: Index push-down condition out of range
*************************** 210. row ***************************
name: icp_match
subsystem: icp
type: counter
comment: Index push-down condition matches