MySQL 8.4 版本注意事項
INNODB_METRICS 資料表提供關於 InnoDB 效能和資源相關計數器的資訊。
INNODB_METRICS 資料表的欄位如下所示。有關欄位說明，請參閱章節 28.4.21，「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 和 metadata_table_handles_closed 計數器，請在 MySQL 伺服器設定檔的 [mysqld] 區段中輸入以下行。
[mysqld]
innodb_monitor_enable = log,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_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_by_dirty_page | 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_rseg_array | 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_on_log_no_waits | buffer_page_io | disabled |
| buffer_page_written_on_log_waits | buffer_page_io | disabled |
| buffer_page_written_on_log_wait_loops | buffer_page_io | disabled |
| buffer_page_written_other | buffer_page_io | disabled |
| buffer_page_written_rseg_array | 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 |
| cpu_n | cpu | disabled |
| cpu_stime_abs | cpu | disabled |
| cpu_stime_pct | cpu | disabled |
| cpu_utime_abs | cpu | disabled |
| cpu_utime_pct | cpu | disabled |
| dblwr_async_requests | dblwr | disabled |
| dblwr_flush_requests | dblwr | disabled |
| dblwr_flush_wait_events | dblwr | disabled |
| dblwr_sync_requests | dblwr | 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_system_deletes | dml | enabled |
| dml_system_inserts | dml | enabled |
| dml_system_reads | dml | enabled |
| dml_system_updates | dml | enabled |
| 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_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_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_deadlock_false_positives | lock | enabled |
| lock_deadlock_rounds | lock | enabled |
| lock_rec_grant_attempts | 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_rec_release_attempts | lock | enabled |
| 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_schedule_refreshes | 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_threads_waiting | lock | enabled |
| lock_timeouts | lock | enabled |
| log_checkpoints | log | disabled |
| log_concurrency_margin | log | disabled |
| log_flusher_no_waits | log | disabled |
| log_flusher_waits | log | disabled |
| log_flusher_wait_loops | log | disabled |
| log_flush_avg_time | log | disabled |
| log_flush_lsn_avg_rate | log | disabled |
| log_flush_max_time | log | disabled |
| log_flush_notifier_no_waits | log | disabled |
| log_flush_notifier_waits | log | disabled |
| log_flush_notifier_wait_loops | log | disabled |
| log_flush_total_time | log | disabled |
| log_free_space | log | disabled |
| log_full_block_writes | log | disabled |
| log_lsn_archived | log | disabled |
| log_lsn_buf_dirty_pages_added | log | disabled |
| log_lsn_buf_pool_oldest_approx | log | disabled |
| log_lsn_buf_pool_oldest_lwm | log | disabled |
| log_lsn_checkpoint_age | log | disabled |
| log_lsn_current | log | disabled |
| log_lsn_last_checkpoint | log | disabled |
| log_lsn_last_flush | log | disabled |
| log_max_modified_age_async | log | disabled |
| log_max_modified_age_sync | log | disabled |
| log_next_file | log | disabled |
| log_on_buffer_space_no_waits | log | disabled |
| log_on_buffer_space_waits | log | disabled |
| log_on_buffer_space_wait_loops | log | disabled |
| log_on_file_space_no_waits | log | disabled |
| log_on_file_space_waits | log | disabled |
| log_on_file_space_wait_loops | log | disabled |
| log_on_flush_no_waits | log | disabled |
| log_on_flush_waits | log | disabled |
| log_on_flush_wait_loops | log | disabled |
| log_on_recent_closed_wait_loops | log | disabled |
| log_on_recent_written_wait_loops | log | disabled |
| log_on_write_no_waits | log | disabled |
| log_on_write_waits | log | disabled |
| log_on_write_wait_loops | log | disabled |
| log_padded | log | disabled |
| log_partial_block_writes | log | disabled |
| log_waits | log | enabled |
| log_writer_no_waits | log | disabled |
| log_writer_on_archiver_waits | log | disabled |
| log_writer_on_file_space_waits | log | disabled |
| log_writer_waits | log | disabled |
| log_writer_wait_loops | log | disabled |
| log_writes | log | enabled |
| log_write_notifier_no_waits | log | disabled |
| log_write_notifier_waits | log | disabled |
| log_write_notifier_wait_loops | log | disabled |
| log_write_requests | log | enabled |
| log_write_to_file_requests_interval | log | disabled |
| metadata_table_handles_closed | metadata | disabled |
| metadata_table_handles_opened | metadata | disabled |
| metadata_table_reference_count | metadata | disabled |
| module_cpu | cpu | disabled |
| module_dblwr | dblwr | disabled |
| module_page_track | page_track | 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 |
| page_track_checkpoint_partial_flush_request | page_track | disabled |
| page_track_full_block_writes | page_track | disabled |
| page_track_partial_block_writes | page_track | disabled |
| page_track_resets | page_track | 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_truncate_history_count | purge | disabled |
| purge_truncate_history_usec | purge | disabled |
| purge_undo_log_pages | purge | disabled |
| purge_upd_exist_or_extern_records | purge | disabled |
| sampled_pages_read | sampling | disabled |
| sampled_pages_skipped | sampling | disabled |
| trx_active_transactions | transaction | disabled |
| trx_allocations | transaction | disabled |
| trx_commits_insert_update | transaction | disabled |
| trx_nl_ro_commits | transaction | disabled |
| trx_on_log_no_waits | transaction | disabled |
| trx_on_log_waits | transaction | disabled |
| trx_on_log_wait_loops | 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 |
| undo_truncate_count | undo | disabled |
| undo_truncate_done_logging_count | undo | disabled |
| undo_truncate_start_logging_count | undo | disabled |
| undo_truncate_usec | undo | disabled |
+---------------------------------------------+---------------------+----------+
314 rows in set (0.00 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(子系統 =log)module_metadata(子系統 =metadata)module_os(子系統 =os)module_purge(子系統 =purge)module_trx(子系統 =transaction)module_undo(子系統 =undo)
範例 17.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計數器的描述可以在INNODB_METRICS資料表的COMMENT欄位中找到。mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE 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_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將三列資料插入資料表中。
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)再次查詢
INNODB_METRICS資料表以取得dml_inserts計數器資料。現在，許多計數器值已遞增，包括COUNT、MAX_COUNT、AVG_COUNT和COUNT_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重設計數器
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_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若要重設所有計數器值，您必須先停用計數器。停用計數器會將
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_i%，而不是指定完整的dml_inserts計數器名稱。您也可以使用萬用字元比對一次啟用、停用或重設多個計數器或模組。例如，指定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_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