InnoDB INFORMATION_SCHEMA指标表
该INNODB_METRICS
表提供有关InnoDB
性能和资源相关计数器的信息。
INNODB_METRICS
表格列如下所示。有关列的说明,请参见“ INFORMATION_SCHEMA INNODB_METRICS表”。
mysql>SELECT *FROM INFORMATION_SCHEMA.INNODB_METRICSWHERE 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
和metadata_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_disable
和innodb_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_METRICSORDER 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_METRICSWHERE 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
表中的计数器数据。
创建一个简单的
InnoDB
表:mysql>
USE test;Database changed mysql>CREATE TABLE t1 (c1 INT)ENGINE =INNODB; Query OK, 0 rows affected (0.02 sec)启用
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_METRICSWHERE NAME ="dml_inserts"; +------------- +------------------------- + | NAME | COMMENT | +------------- +------------------------- + | dml_inserts | Number of rows inserted | +------------- +------------------------- +在
INNODB_METRICS
表中查询dml_inserts
计数器数据。由于尚未执行DML操作,因此计数器值为零或NULL。该TIME_ENABLED
和TIME_ELAPSED
值表明,当计数器最后一次启用,自那时以来,多少秒的流逝。mysql>
SELECT *FROM INFORMATION_SCHEMA.INNODB_METRICSWHERE 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在表中插入三行数据。
mysql>
INSERT INTO t1values (1); Query OK, 1 row affected (0.00 sec) mysql>INSERT INTO t1values (2); Query OK, 1 row affected (0.00 sec) mysql>INSERT INTO t1values (3); Query OK, 1 row affected (0.00 sec)INNODB_METRICS
再次在表中查询dml_inserts
计数器数据。许多计数器值现在已经增加,包括COUNT
,MAX_COUNT
,AVG_COUNT
,和COUNT_RESET
。INNODB_METRICS
有关这些值的描述,请参考表定义。mysql>
SELECT *FROM INFORMATION_SCHEMA.INNODB_METRICSWHERE 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重置
dml_inserts
计数器,然后INNODB_METRICS
再次在表中查询dml_inserts
计数器数据。%_RESET
先前报告的值(例如COUNT_RESET
和MAX_RESET
)被设置回零。值,如COUNT
,MAX_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_METRICSWHERE 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要重置所有计数器值,必须首先禁用该计数器。禁用计数器会将
STATUS
值设置为disabled
。mysql>
SET GLOBAL innodb_monitor_disable = dml_inserts; Query OK, 0 rows affected (0.00 sec) mysql>SELECT *FROM INFORMATION_SCHEMA.INNODB_METRICSWHERE 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_
。禁用计数器后,您可以使用该
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_METRICSWHERE 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