InnoDB INFORMATION_SCHEMA度量表
MySQL 5.6.2中引入了INNODB_METRICS表，它将所有InnoDB性能和资源相关的计数器合并到一个INFORMATION_SCHEMA表中。
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 17263004
MAX_COUNT: 17263004
MIN_COUNT: NULL
AVG_COUNT: 0.8178722213173183
COUNT_RESET: 17263004
MAX_COUNT_RESET: 17263004
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2023-05-10 09:02:54
TIME_DISABLED: NULL
TIME_ELAPSED: 21107214
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of rows inserted
启用、禁用和重置计数器
可以使用以下配置选项启用、禁用和重置计数器:
.innodb_monitor_enable:启用一个或多个计数器。
SET GLOBAL innodb_monitor_enable = [counter-name|module_name|pattern|all];
.innodb_monitor_disable:禁用一个或多个计数器。
SET GLOBAL innodb_monitor_disable = [counter-name|module_name|pattern|all];
.innodb_monitor_reset:将一个或多个计数器的计数值重置为零。
SET GLOBAL innodb_monitor_reset = [counter-name|module_name|pattern|all];
.innodb_monitor_reset_all:重置一个或多个计数器的所有值。在使用innodb_monitor_reset_all之前，必须禁用计数器。
SET GLOBAL innodb_monitor_reset_all = [counter-name|module_name|pattern|all];
你也可以在启动时使用MySQL服务器配置文件启用计数器和计数器模块。例如，要启用log模块、metadata_table_handles_opened和metadata_table_handles_closed计数器，在my.cnf配置文件的[mysqld]部分输入以下代码。
[mysqld] innodb_monitor_enable = module_recovery,metadata_table_handles_opened,metadata_table_handles_closed
当在你的配置文件中启用多个计数器或模块时，你必须指定innodb_monitor_enable配置选项，后面跟着用逗号分隔的计数器和模块名称，如上例所示。只有innodb_monitor_enable选项可以在你的配置文件中使用。禁用和重置配置选项仅支持在命令行上。
注意:
因为每个计数器都会在服务器上增加一定程度的运行时开销，所以通常在试验和基准测试期间在测试和开发服务器上启用更多的计数器，并且仅在生产服务器上启用计数器以诊断已知问题或监视可能成为特定服务器和工作负载瓶颈的方面。
计数器
INNODB_METRICS表中的计数器可能会发生变化，所以要获取最新的计数器列表，可以在运行中的MySQL服务器上查询。
默认启用的计数器与“SHOW ENGINE INNODB STATUS”使用的计数器对应。SHOW ENGINE INNODB STATUS使用的计数器在系统级别上总是“on”的，但是你可以根据需要禁用INNODB_METRICS表的这些计数器。另外，计数器状态不是持久的。除非另外指定，否则计数器在服务器重启时将恢复到默认的启用或禁用状态。
如果你运行的程序会受到INNODB_METRICS表中新增或更改的影响，建议你查看发布说明，并在升级之前查询INNODB_METRICS表中的新版本。
mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS ORDER BY NAME; +------------------------------------------+---------------------+----------+ | name | subsystem | status | +------------------------------------------+---------------------+----------+ | 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_deleted_no_hash_entry | adaptive_hash_index | disabled | | adaptive_hash_rows_removed | adaptive_hash_index | disabled | | adaptive_hash_rows_updated | adaptive_hash_index | disabled | | adaptive_hash_searches | adaptive_hash_index | enabled | | adaptive_hash_searches_btree | adaptive_hash_index | enabled | | buffer_data_reads | buffer | enabled | | buffer_data_written | buffer | enabled | | buffer_flush_adaptive | buffer | disabled | | buffer_flush_adaptive_avg_pass | buffer | disabled | | buffer_flush_adaptive_avg_time_est | buffer | disabled | | buffer_flush_adaptive_avg_time_slot | buffer | disabled | | buffer_flush_adaptive_avg_time_thread | buffer | disabled | | buffer_flush_adaptive_pages | buffer | disabled | | buffer_flush_adaptive_total_pages | buffer | disabled | | buffer_flush_avg_page_rate | buffer | disabled | | buffer_flush_avg_pass | buffer | disabled | | buffer_flush_avg_time | buffer | disabled | | buffer_flush_background | buffer | disabled | | buffer_flush_background_pages | buffer | disabled | | buffer_flush_background_total_pages | buffer | disabled | | buffer_flush_batches | buffer | disabled | | buffer_flush_batch_num_scan | buffer | disabled | | buffer_flush_batch_pages | buffer | disabled | | buffer_flush_batch_scanned | buffer | disabled | | buffer_flush_batch_scanned_per_call | buffer | disabled | | buffer_flush_batch_total_pages | buffer | disabled | | buffer_flush_lsn_avg_rate | buffer | disabled | | buffer_flush_nei***or | buffer | disabled | | buffer_flush_nei***or_pages | buffer | disabled | | buffer_flush_nei***or_total_pages | buffer | disabled | | buffer_flush_n_to_flush_by_age | buffer | disabled | | buffer_flush_n_to_flush_requested | buffer | disabled | | buffer_flush_pct_for_dirty | buffer | disabled | | buffer_flush_pct_for_lsn | buffer | disabled | | buffer_flush_sync | buffer | disabled | | buffer_flush_sync_pages | buffer | disabled | | buffer_flush_sync_total_pages | buffer | disabled | | buffer_flush_sync_waits | buffer | disabled | | buffer_LRU_batches_evict | buffer | disabled | | buffer_LRU_batches_flush | buffer | disabled | | buffer_LRU_batch_evict_pages | buffer | disabled | | buffer_LRU_batch_evict_total_pages | buffer | disabled | | buffer_LRU_batch_flush_avg_pass | buffer | disabled | | buffer_LRU_batch_flush_avg_time_est | buffer | disabled | | buffer_LRU_batch_flush_avg_time_slot | buffer | disabled | | buffer_LRU_batch_flush_avg_time_thread | buffer | disabled | | buffer_LRU_batch_flush_pages | buffer | disabled | | buffer_LRU_batch_flush_total_pages | buffer | disabled | | buffer_LRU_batch_num_scan | buffer | disabled | | buffer_LRU_batch_scanned | buffer | disabled | | buffer_LRU_batch_scanned_per_call | buffer | disabled | | buffer_LRU_get_free_loops | buffer | disabled | | buffer_LRU_get_free_search | Buffer | disabled | | buffer_LRU_get_free_waits | buffer | disabled | | buffer_LRU_search_num_scan | buffer | disabled | | buffer_LRU_search_scanned | buffer | disabled | | buffer_LRU_search_scanned_per_call | buffer | disabled | | buffer_LRU_single_flush_failure_count | Buffer | disabled | | buffer_LRU_single_flush_num_scan | buffer | disabled | | buffer_LRU_single_flush_scanned | buffer | disabled | | buffer_LRU_single_flush_scanned_per_call | buffer | disabled | | buffer_LRU_unzip_search_num_scan | buffer | disabled | | buffer_LRU_unzip_search_scanned | buffer | disabled | | buffer_LRU_unzip_search_scanned_per_call | buffer | disabled | | buffer_pages_created | buffer | enabled | | buffer_pages_read | buffer | enabled | | buffer_pages_written | buffer | enabled | | buffer_page_read_blob | buffer_page_io | disabled | | buffer_page_read_fsp_hdr | buffer_page_io | disabled | | buffer_page_read_ibuf_bitmap | buffer_page_io | disabled | | buffer_page_read_ibuf_free_list | buffer_page_io | disabled | | buffer_page_read_index_ibuf_leaf | buffer_page_io | disabled | | buffer_page_read_index_ibuf_non_leaf | buffer_page_io | disabled | | buffer_page_read_index_inode | buffer_page_io | disabled | | buffer_page_read_index_leaf | buffer_page_io | disabled | | buffer_page_read_index_non_leaf | buffer_page_io | disabled | | buffer_page_read_other | buffer_page_io | disabled | | buffer_page_read_system_page | buffer_page_io | disabled | | buffer_page_read_trx_system | buffer_page_io | disabled | | buffer_page_read_undo_log | buffer_page_io | disabled | | buffer_page_read_xdes | buffer_page_io | disabled | | buffer_page_read_zblob | buffer_page_io | disabled | | buffer_page_read_zblob2 | buffer_page_io | disabled | | buffer_page_written_blob | buffer_page_io | disabled | | buffer_page_written_fsp_hdr | buffer_page_io | disabled | | buffer_page_written_ibuf_bitmap | buffer_page_io | disabled | | buffer_page_written_ibuf_free_list | buffer_page_io | disabled | | buffer_page_written_index_ibuf_leaf | buffer_page_io | disabled | | buffer_page_written_index_ibuf_non_leaf | buffer_page_io | disabled | | buffer_page_written_index_inode | buffer_page_io | disabled | | buffer_page_written_index_leaf | buffer_page_io | disabled | | buffer_page_written_index_non_leaf | buffer_page_io | disabled | | buffer_page_written_other | buffer_page_io | disabled | | buffer_page_written_system_page | buffer_page_io | disabled | | buffer_page_written_trx_system | buffer_page_io | disabled | | buffer_page_written_undo_log | buffer_page_io | disabled | | buffer_page_written_xdes | buffer_page_io | disabled | | buffer_page_written_zblob | buffer_page_io | disabled | | buffer_page_written_zblob2 | buffer_page_io | disabled | | buffer_pool_bytes_data | buffer | enabled | | buffer_pool_bytes_dirty | buffer | enabled | | buffer_pool_pages_data | buffer | enabled | | buffer_pool_pages_dirty | buffer | enabled | | buffer_pool_pages_free | buffer | enabled | | buffer_pool_pages_misc | buffer | enabled | | buffer_pool_pages_total | buffer | enabled | | buffer_pool_reads | buffer | enabled | | buffer_pool_read_ahead | buffer | enabled | | buffer_pool_read_ahead_evicted | buffer | enabled | | buffer_pool_read_requests | buffer | enabled | | buffer_pool_size | server | enabled | | buffer_pool_wait_free | buffer | enabled | | buffer_pool_write_requests | buffer | enabled | | compression_pad_decrements | compression | disabled | | compression_pad_increments | compression | disabled | | compress_pages_compressed | compression | disabled | | compress_pages_decompressed | compression | disabled | | ddl_background_drop_indexes | ddl | disabled | | ddl_background_drop_tables | ddl | disabled | | ddl_log_file_alter_table | ddl | disabled | | ddl_online_create_index | ddl | disabled | | ddl_pending_alter_table | ddl | disabled | | ddl_sort_file_alter_table | ddl | disabled | | dml_deletes | dml | enabled | | dml_inserts | dml | enabled | | dml_reads | dml | disabled | | dml_updates | dml | enabled | | file_num_open_files | file_system | enabled | | ibuf_merges | change_buffer | enabled | | ibuf_merges_delete | change_buffer | enabled | | ibuf_merges_delete_mark | change_buffer | enabled | | ibuf_merges_discard_delete | change_buffer | enabled | | ibuf_merges_discard_delete_mark | change_buffer | enabled | | ibuf_merges_discard_insert | change_buffer | enabled | | ibuf_merges_insert | change_buffer | enabled | | ibuf_size | change_buffer | enabled | | icp_attempts | icp | disabled | | icp_match | icp | disabled | | icp_no_match | icp | disabled | | icp_out_of_range | icp | disabled | | index_page_discards | index | disabled | | index_page_merge_attempts | index | disabled | | index_page_merge_successful | index | disabled | | index_page_reorg_attempts | index | disabled | | index_page_reorg_successful | index | disabled | | index_page_splits | index | disabled | | innodb_activity_count | server | enabled | | innodb_background_drop_table_usec | server | disabled | | innodb_checkpoint_usec | server | disabled | | innodb_dblwr_pages_written | server | enabled | | innodb_dblwr_writes | server | enabled | | innodb_dict_lru_count | server | disabled | | innodb_dict_lru_usec | server | disabled | | innodb_ibuf_merge_usec | server | disabled | | innodb_log_flush_usec | server | disabled | | innodb_master_active_loops | server | disabled | | innodb_master_idle_loops | server | disabled | | innodb_master_purge_usec | server | disabled | | innodb_master_thread_sleeps | server | disabled | | innodb_mem_validate_usec | server | disabled | | innodb_page_size | server | enabled | | innodb_rwlock_sx_os_waits | server | enabled | | innodb_rwlock_sx_spin_rounds | server | enabled | | innodb_rwlock_sx_spin_waits | server | enabled | | innodb_rwlock_s_os_waits | server | enabled | | innodb_rwlock_s_spin_rounds | server | enabled | | innodb_rwlock_s_spin_waits | server | enabled | | innodb_rwlock_x_os_waits | server | enabled | | innodb_rwlock_x_spin_rounds | server | enabled | | innodb_rwlock_x_spin_waits | server | enabled | | lock_deadlocks | lock | enabled | | lock_rec_locks | lock | disabled | | lock_rec_lock_created | lock | disabled | | lock_rec_lock_removed | lock | disabled | | lock_rec_lock_requests | lock | disabled | | lock_rec_lock_waits | lock | disabled | | lock_row_lock_current_waits | lock | enabled | | lock_row_lock_time | lock | enabled | | lock_row_lock_time_avg | lock | enabled | | lock_row_lock_time_max | lock | enabled | | lock_row_lock_waits | lock | enabled | | lock_table_locks | lock | disabled | | lock_table_lock_created | lock | disabled | | lock_table_lock_removed | lock | disabled | | lock_table_lock_waits | lock | disabled | | lock_timeouts | lock | enabled | | log_checkpoints | recovery | disabled | | log_lsn_buf_pool_oldest | recovery | disabled | | log_lsn_checkpoint_age | recovery | disabled | | log_lsn_current | recovery | disabled | | log_lsn_last_checkpoint | recovery | disabled | | log_lsn_last_flush | recovery | disabled | | log_max_modified_age_async | recovery | disabled | | log_max_modified_age_sync | recovery | disabled | | log_num_log_io | recovery | disabled | | log_padded | recovery | enabled | | log_pending_checkpoint_writes | recovery | disabled | | log_pending_log_flushes | recovery | disabled | | log_waits | recovery | enabled | | log_writes | recovery | enabled | | log_write_requests | recovery | enabled | | metadata_table_handles_closed | metadata | disabled | | metadata_table_handles_opened | metadata | disabled | | metadata_table_reference_count | metadata | disabled | | os_data_fsyncs | os | enabled | | os_data_reads | os | enabled | | os_data_writes | os | enabled | | os_log_bytes_written | os | enabled | | os_log_fsyncs | os | enabled | | os_log_pending_fsyncs | os | enabled | | os_log_pending_writes | os | enabled | | os_pending_reads | os | disabled | | os_pending_writes | os | disabled | | purge_del_mark_records | purge | disabled | | purge_dml_delay_usec | purge | disabled | | purge_invoked | purge | disabled | | purge_resume_count | purge | disabled | | purge_stop_count | purge | disabled | | purge_undo_log_pages | purge | disabled | | purge_upd_exist_or_extern_records | purge | disabled | | trx_active_transactions | transaction | disabled | | trx_commits_insert_update | transaction | disabled | | trx_nl_ro_commits | transaction | disabled | | trx_rollbacks | transaction | disabled | | trx_rollbacks_savepoint | transaction | disabled | | trx_rollback_active | transaction | disabled | | trx_ro_commits | transaction | disabled | | trx_rseg_current_size | transaction | disabled | | trx_rseg_history_len | transaction | enabled | | trx_rw_commits | transaction | disabled | | trx_undo_slots_cached | transaction | disabled | | trx_undo_slots_used | transaction | disabled | +------------------------------------------+---------------------+----------+ 235 rows in set (0.03 sec)
计数器模块
模块名与INNODB_METRICS表中的SUBSYSTEM列对应，但不完全相同。相对于单独启用、禁用或重置计数器，您可以使用模块名称来快速启用、禁用或重置特定子系统的所有计数器。例如，使用module_dml启用与dml子系统关联的所有计数器。
mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem ='dml'; +-------------+-----------+----------+ | name | subsystem | status | +-------------+-----------+----------+ | dml_reads | dml | disabled | | dml_inserts | dml | enabled | | dml_deletes | dml | enabled | | dml_updates | dml | enabled | +-------------+-----------+----------+ 4 rows in set (0.00 sec) mysql> SET GLOBAL innodb_monitor_enable = module_dml; Query OK, 0 rows affected (0.00 sec) mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem ='dml'; +-------------+-----------+---------+ | name | subsystem | status | +-------------+-----------+---------+ | dml_reads | dml | enabled | | dml_inserts | dml | enabled | | dml_deletes | dml | enabled | | dml_updates | dml | enabled | +-------------+-----------+---------+ 4 rows in set (0.00 sec)
下面是innodb_monitor_enable和相关的配置选项可以使用的module_name值以及相应的子系统名称。
.module_adaptive_hash (subsystem = adaptive_hash_index) .module_buffer (subsystem = buffer) .module_buffer_page (subsystem = buffer_page_io) .module_compress (subsystem = compression) .module_ddl (subsystem = ddl) .module_dml (subsystem = dml) .module_file (subsystem = file_system) .module_ibuf_system (subsystem = change_buffer) .module_icp (subsystem = icp) .module_index (subsystem = index) .module_innodb (subsystem = innodb) .module_lock (subsystem = lock) .module_log (subsystem = recovery) .module_metadata (subsystem = metadata) .module_os (subsystem = os) .module_purge (subsystem = purge) .module_trx (subsystem = transaction)
使用INNODB_METRICS表计数器
下面这个例子演示了启用、禁用、重置计数器，以及查询INNODB_METRICS表中的计数器数据。
1.创建一个简单的InnoDB表;
mysql> use test Database changed mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB; Query OK, 0 rows affected (0.01 sec)
2.启用dml_inserts计数器。
先禁用dml_inserts计数器并将所有计数清零
mysql> SET GLOBAL innodb_monitor_disable=dml_inserts;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 17263007
MAX_COUNT: 17263007
MIN_COUNT: NULL
AVG_COUNT: 0.817808627174022
COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: 0
TIME_ENABLED: 2023-05-10 09:02:54
TIME_DISABLED: 2024-01-09 16:37:13
TIME_ELAPSED: 21108859
TIME_RESET: NULL
STATUS: disabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.00 sec)
mysql> SET GLOBAL innodb_monitor_reset_all=dml_inserts;
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 0
MAX_COUNT: NULL
MIN_COUNT: NULL
AVG_COUNT: NULL
COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: NULL
TIME_DISABLED: NULL
TIME_ELAPSED: NULL
TIME_RESET: NULL
STATUS: disabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.00 sec)
再启用dml_inserts计数器
mysql> SET GLOBAL innodb_monitor_enable = dml_inserts; Query OK, 0 rows affected (0.00 sec)
在INNODB_METRICS表的COMMENT列中找到的关于dml_inserts计数器的描述:
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"; +-------------+-------------------------+ | NAME | COMMENT | +-------------+-------------------------+ | dml_inserts | Number of rows inserted | +-------------+-------------------------+ 1 row in set (0.00 sec)
3.查询INNODB_METRICS表中的dml_inserts计数器数据。因为没有执行DML操作，所以计数器值为零或NULL。TIME_ENABLED和TIME_ELAPSED值表示计数器上次启用的时间，以及从该时间起已经过去了多少秒。
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 0
MAX_COUNT: 0
MIN_COUNT: NULL
AVG_COUNT: 0
COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2024-01-09 16:40:33
TIME_DISABLED: NULL
TIME_ELAPSED: 150
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.00 sec)
4.向表中插入三行数据。
mysql> INSERT INTO t1 values(1); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t1 values(2); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t1 values(3); Query OK, 1 row affected (0.00 sec)
5.再次查询INNODB_METRICS表获取dml_inserts计数器数据。现在有一些计数器的值已经递增，包括COUNT、MAX_COUNT、AVG_COUNT和COUNT_RESET。
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 3
MAX_COUNT: 3
MIN_COUNT: NULL
AVG_COUNT: 0.00980392156862745
COUNT_RESET: 3
MAX_COUNT_RESET: 3
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2024-01-09 16:40:33
TIME_DISABLED: NULL
TIME_ELAPSED: 306
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.00 sec)
6.重置dml_inserts计数器，然后再次在INNODB_METRICS表中查询dml_inserts计数器数据。之前报告的%_RESET值，如COUNT_RESET和MAX_RESET，将重置为零。COUNT、MAX_COUNT和AVG_COUNT等值从计数器启用时开始收集数据，这些值不受重置的影响。
mysql> SET GLOBAL innodb_monitor_disable=dml_inserts;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 20
MAX_COUNT: 20
MIN_COUNT: NULL
AVG_COUNT: 0.03424657534246575
COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: 0
TIME_ENABLED: 2024-01-09 16:40:33
TIME_DISABLED: 2024-01-09 16:50:17
TIME_ELAPSED: 584
TIME_RESET: NULL
STATUS: disabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.00 sec)
7.要重置所有计数器值，必须首先禁用计数器。禁用计数器将状态值设置为禁用。
mysql> SET GLOBAL innodb_monitor_disable=dml_inserts;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 20
MAX_COUNT: 20
MIN_COUNT: NULL
AVG_COUNT: 0.03076923076923077
COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: 0
TIME_ENABLED: 2024-01-09 16:40:33
TIME_DISABLED: 2024-01-09 16:51:23
TIME_ELAPSED: 650
TIME_RESET: NULL
STATUS: disabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.01 sec)
注意:
计数器和模块名称支持通配符匹配。例如，可以指定dml_i%，而不是指定完整的dml_inserts计数器名称。还可以使用通配符匹配一次启用、禁用或重置多个计数器或模块。例如，指定dml_%来启用、禁用或重置所有以dml_%开头的计数器。
8.当计数器被禁用后，你可以使用innodb_monitor_reset_all选项重置所有计数器的值。所有值都设置为0或NULL。
mysql> SET GLOBAL innodb_monitor_reset_all=dml_inserts;
Query OK, 0 rows affected (0.05 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 0
MAX_COUNT: NULL
MIN_COUNT: NULL
AVG_COUNT: NULL
COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: NULL
TIME_DISABLED: NULL
TIME_ELAPSED: NULL
TIME_RESET: NULL
STATUS: disabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.00 sec)