Documentation Home
MySQL 8.0 参考手册  / 第 15 章 InnoDB 存储引擎  / 15.15 InnoDB INFORMATION_SCHEMA 表  /  14.16.6 InnoDB INFORMATION_SCHEMA 指标表

14.16.6 InnoDB INFORMATION_SCHEMA 指标表

INNODB_METRICS表提供有关InnoDB性能和资源相关计数器的信息。

INNODB_METRICS表格列如下所示。有关列说明,请参阅 第 24.4.16 节,“INFORMATION_SCHEMA INNODB_METRICS 表”

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 46273
      MAX_COUNT: 46273
      MIN_COUNT: NULL
      AVG_COUNT: 492.2659574468085
    COUNT_RESET: 46273
MAX_COUNT_RESET: 46273
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: 2014-11-28 16:07:53
  TIME_DISABLED: NULL
   TIME_ELAPSED: 94
     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和 计数器,请在 MySQL 服务器配置文件的部分中 metadata_table_handles_closed输入以下行。[mysqld]

[mysqld]
innodb_monitor_enable = module_recovery,metadata_table_handles_opened,metadata_table_handles_closed

在配置文件中启用多个计数器或模块时,指定 innodb_monitor_enable变量后跟以逗号分隔的计数器和模块名称,如上所示。只有 innodb_monitor_enable变量可以在配置文件中使用。和 变量仅在命令行上受支持 innodb_monitor_disableinnodb_monitor_reset

笔记

因为每个计数器都会增加一定程度的运行时开销,所以在生产服务器上保守地使用计数器来诊断特定问题或监视特定功能。建议使用测试或开发服务器以更广泛地使用计数器。

柜台

可用计数器列表可能会更改。在表中查询 INFORMATION_SCHEMA.INNODB_METRICS 您的 MySQL 服务器版本中可用的计数器。

默认启用的计数器对应于 SHOW ENGINE INNODB STATUS输出中显示的那些。输出中显示的计数器 SHOW ENGINE INNODB STATUS始终在系统级别启用,但可以针对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_neighbor                    | buffer              | disabled |
| buffer_flush_neighbor_pages              | buffer              | disabled |
| buffer_flush_neighbor_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.01 sec)

计数器模块

每个计数器都与一个特定的模块相关联。模块名称可用于启用、禁用或重置特定子系统的所有计数器。例如,用于 启用与子系统 module_dml关联的所有计数器。dml

mysql> SET GLOBAL innodb_monitor_enable = module_dml;

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 |
+-------------+-----------+---------+

模块名称可以 innodb_monitor_enable与相关变量一起使用。

SUBSYSTEM下面列出了 模块名称和对应的名称。

  • module_adaptive_hash(子系统 = adaptive_hash_index

  • module_buffer(子系统 = buffer

  • module_buffer_page(子系统 = buffer_page_io

  • module_compress(子系统 = compression

  • module_ddl(子系统 = ddl

  • module_dml(子系统 = dml

  • module_file(子系统 = file_system

  • module_ibuf_system(子系统 = change_buffer

  • module_icp(子系统 = icp

  • module_index(子系统 = index

  • module_innodb(子系统 = innodb

  • module_lock(子系统 = lock

  • module_log(子系统 = recovery

  • module_metadata(子系统 = metadata

  • module_os(子系统 = os

  • module_purge(子系统 = purge

  • module_trx(子系统 = transaction

示例 14.11 使用 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.02 sec)
  2. 启用dml_inserts计数器。

    mysql> SET GLOBAL innodb_monitor_enable = dml_inserts;
    Query OK, 0 rows affected (0.01 sec)

    dml_inserts可以在表的COMMENT列中 找到计数器 的说明INNODB_METRICS

    mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts";
    +-------------+-------------------------+
    | NAME        | COMMENT                 |
    +-------------+-------------------------+
    | dml_inserts | Number of rows inserted |
    +-------------+-------------------------+
  3. INNODB_METRICS在表中 查询dml_inserts计数器数据。因为没有执行任何 DML 操作,所以计数器值为零或 NULL。TIME_ENABLEDTIME_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: 2014-12-04 14:18:28
      TIME_DISABLED: NULL
       TIME_ELAPSED: 28
         TIME_RESET: NULL
             STATUS: enabled
               TYPE: status_counter
            COMMENT: Number of rows inserted
  4. 向表中插入三行数据。

    mysql> INSERT INTO t1 values(1);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> INSERT INTO t1 values(2);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> INSERT INTO t1 values(3);
    Query OK, 1 row affected (0.00 sec)
  5. 再次查询INNODB_METRICS表以获取dml_inserts计数器数据。许多计数器值现在已经递增,包括 COUNTMAX_COUNTAVG_COUNTCOUNT_RESET。有关这些值的说明,请参阅 INNODB_METRICS表定义。

    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.046153846153846156
        COUNT_RESET: 3
    MAX_COUNT_RESET: 3
    MIN_COUNT_RESET: NULL
    AVG_COUNT_RESET: NULL
       TIME_ENABLED: 2014-12-04 14:18:28
      TIME_DISABLED: NULL
       TIME_ELAPSED: 65
         TIME_RESET: NULL
             STATUS: enabled
               TYPE: status_counter
            COMMENT: Number of rows inserted
  6. 重置dml_inserts计数器并INNODB_METRICS再次查询表以获取dml_inserts计数器数据。之前报告的 %_RESET值(例如COUNT_RESETMAX_RESET)被设置回零。COUNTMAX_COUNT和 等值AVG_COUNT从启用计数器开始累积收集数据,不受复位影响。

    mysql> SET GLOBAL innodb_monitor_reset = 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: 3
          MAX_COUNT: 3
          MIN_COUNT: NULL
          AVG_COUNT: 0.03529411764705882
        COUNT_RESET: 0
    MAX_COUNT_RESET: 0
    MIN_COUNT_RESET: NULL
    AVG_COUNT_RESET: 0
       TIME_ENABLED: 2014-12-04 14:18:28
      TIME_DISABLED: NULL
       TIME_ELAPSED: 85
         TIME_RESET: 2014-12-04 14:19:44
             STATUS: enabled
               TYPE: status_counter
            COMMENT: Number of rows inserted
  7. 要重置所有计数器值,您必须首先禁用计数器。禁用计数器会将 STATUS值设置为 disabled

    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: 3
          MAX_COUNT: 3
          MIN_COUNT: NULL
          AVG_COUNT: 0.030612244897959183
        COUNT_RESET: 0
    MAX_COUNT_RESET: 0
    MIN_COUNT_RESET: NULL
    AVG_COUNT_RESET: 0
       TIME_ENABLED: 2014-12-04 14:18:28
      TIME_DISABLED: 2014-12-04 14:20:06
       TIME_ELAPSED: 98
         TIME_RESET: NULL
             STATUS: disabled
               TYPE: status_counter
            COMMENT: Number of rows inserted
    笔记

    计数器和模块名称支持通配符匹配。例如 dml_inserts,您可以不指定完整的计数器名称,而是指定dml_i%. 您还可以使用通配符匹配一次启用、禁用或重置多个计数器或模块。例如,指定 dml_%启用、禁用或重置所有以 开头的计数器dml_

  8. innodb_monitor_reset_all 禁用计数器后,您可以使用该选项 重置所有计数器值 。所有值都设置为零或 NULL。

    mysql> SET GLOBAL innodb_monitor_reset_all = 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: 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