• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • InnoDB INFORMATION_SCHEMA指标表

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

    INNODB_METRICS表格列如下所示。有关列的说明,请参见“ 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_openedmetadata_table_handles_closed计数器,请[mysqld]在MySQL服务器配置文件的部分中输入以下行。

    [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
    • module_undo(子系统=undo

    例15.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计数器的描述可COMMENTINNODB_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_COUNT,和COUNT_RESETINNODB_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_COUNTAVG_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