上头INNODB元件表提供资讯innoDB性能和资源计数器

INNODB元件表列显示如下。列描述见264.23节“Inforce_SCHEMAINNODB_METERICS表”。.

mysql/select*Inforce_SCHEMA.INNODB_Metricsrow ***************************            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

启动、禁用和重置计数器

可使用下列变量启动、禁用和重置计数器

计数器模块启动时使用 MySQL服务器配置文件也可以启动举例说,帮助日志记录模块化metadata_table_handles_opened并metadata_table_handles_closed计数器输入下行[ mysqldMySQL服务器配置文件段

[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_METRICSMySQL服务器版可用计数表

计数器默认匹配显示显示引擎INNODB状态输出.计数器显示显示引擎INNODB状态输出常系统级启动,但可禁止INNODB元件表格内反状态非持久化除非配置异常, 计数器恢复默认启动状态或禁用状态

运行程序受计数器增删影响时,建议审查发布注解并查询INNODB元件表识别这些修改作为升级过程的一部分

mysql安全名称子系统状态+------------------------------------------+---------------------+----------+ | 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)

反模块

每一计数器都关联到一个模块模块名称可用于启动、禁用或重置子系统所有计数器例举使用模块_dml激活所有关联计数器ml子系统

mysqlsetGeneralnodb_monitor_enmysql安全名称子系统状态++子系统++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

模块名称可用innodb_monitor_enable相关变量

模块名称和对应子系统名字列表如下