Edit File: monitor.result
set global innodb_monitor_disable = All; select name, status from information_schema.innodb_metrics; name status metadata_table_handles_opened disabled metadata_table_handles_closed disabled metadata_table_reference_count disabled metadata_mem_pool_size disabled lock_deadlocks disabled lock_timeouts disabled lock_rec_lock_waits disabled lock_table_lock_waits disabled lock_rec_lock_requests disabled lock_rec_lock_created disabled lock_rec_lock_removed disabled lock_rec_locks disabled lock_table_lock_created disabled lock_table_lock_removed disabled lock_table_locks disabled lock_row_lock_current_waits disabled lock_row_lock_time disabled lock_row_lock_time_max disabled lock_row_lock_waits disabled lock_row_lock_time_avg disabled buffer_pool_size disabled buffer_pool_reads disabled buffer_pool_read_requests disabled buffer_pool_write_requests disabled buffer_pool_wait_free disabled buffer_pool_read_ahead disabled buffer_pool_read_ahead_evicted disabled buffer_pool_pages_total disabled buffer_pool_pages_misc disabled buffer_pool_pages_data disabled buffer_pool_bytes_data disabled buffer_pool_pages_dirty disabled buffer_pool_bytes_dirty disabled buffer_pool_pages_free disabled buffer_pages_created disabled buffer_pages_written disabled buffer_pages_read disabled buffer_data_reads disabled buffer_data_written disabled buffer_flush_batch_scanned disabled buffer_flush_batch_num_scan disabled buffer_flush_batch_scanned_per_call disabled buffer_flush_batch_rescan disabled buffer_flush_batch_total_pages disabled buffer_flush_batches disabled buffer_flush_batch_pages disabled buffer_flush_neighbor_total_pages disabled buffer_flush_neighbor disabled buffer_flush_neighbor_pages disabled buffer_flush_n_to_flush_requested disabled buffer_flush_avg_page_rate disabled buffer_flush_lsn_avg_rate disabled buffer_flush_pct_for_dirty disabled buffer_flush_pct_for_lsn disabled buffer_flush_sync_waits disabled buffer_flush_adaptive_total_pages disabled buffer_flush_adaptive disabled buffer_flush_adaptive_pages disabled buffer_flush_sync_total_pages disabled buffer_flush_sync disabled buffer_flush_sync_pages disabled buffer_flush_background_total_pages disabled buffer_flush_background disabled buffer_flush_background_pages disabled buffer_LRU_batch_scanned disabled buffer_LRU_batch_num_scan disabled buffer_LRU_batch_scanned_per_call disabled buffer_LRU_batch_total_pages disabled buffer_LRU_batches disabled buffer_LRU_batch_pages disabled buffer_LRU_single_flush_scanned disabled buffer_LRU_single_flush_num_scan disabled buffer_LRU_single_flush_scanned_per_call disabled buffer_LRU_single_flush_failure_count disabled buffer_LRU_get_free_search disabled buffer_LRU_search_scanned disabled buffer_LRU_search_num_scan disabled buffer_LRU_search_scanned_per_call disabled buffer_LRU_unzip_search_scanned disabled buffer_LRU_unzip_search_num_scan disabled buffer_LRU_unzip_search_scanned_per_call disabled buffer_page_read_index_leaf disabled buffer_page_read_index_non_leaf disabled buffer_page_read_index_ibuf_leaf disabled buffer_page_read_index_ibuf_non_leaf disabled buffer_page_read_undo_log disabled buffer_page_read_index_inode disabled buffer_page_read_ibuf_free_list disabled buffer_page_read_ibuf_bitmap disabled buffer_page_read_system_page disabled buffer_page_read_trx_system disabled buffer_page_read_fsp_hdr disabled buffer_page_read_xdes disabled buffer_page_read_blob disabled buffer_page_read_zblob disabled buffer_page_read_zblob2 disabled buffer_page_read_other disabled buffer_page_written_index_leaf disabled buffer_page_written_index_non_leaf disabled buffer_page_written_index_ibuf_leaf disabled buffer_page_written_index_ibuf_non_leaf disabled buffer_page_written_undo_log disabled buffer_page_written_index_inode disabled buffer_page_written_ibuf_free_list disabled buffer_page_written_ibuf_bitmap disabled buffer_page_written_system_page disabled buffer_page_written_trx_system disabled buffer_page_written_fsp_hdr disabled buffer_page_written_xdes disabled buffer_page_written_blob disabled buffer_page_written_zblob disabled buffer_page_written_zblob2 disabled buffer_page_written_other disabled os_data_reads disabled os_data_writes disabled os_data_fsyncs disabled os_pending_reads disabled os_pending_writes disabled os_log_bytes_written disabled os_log_fsyncs disabled os_log_pending_fsyncs disabled os_log_pending_writes disabled trx_rw_commits disabled trx_ro_commits disabled trx_nl_ro_commits disabled trx_commits_insert_update disabled trx_rollbacks disabled trx_rollbacks_savepoint disabled trx_rollback_active disabled trx_active_transactions disabled trx_rseg_history_len disabled trx_undo_slots_used disabled trx_undo_slots_cached disabled trx_rseg_current_size disabled purge_del_mark_records disabled purge_upd_exist_or_extern_records disabled purge_invoked disabled purge_undo_log_pages disabled purge_dml_delay_usec disabled purge_stop_count disabled purge_resume_count disabled log_checkpoints disabled log_lsn_last_flush disabled log_lsn_last_checkpoint disabled log_lsn_current disabled log_lsn_checkpoint_age disabled log_lsn_buf_pool_oldest disabled log_max_modified_age_async disabled log_max_modified_age_sync disabled log_pending_log_writes disabled log_pending_checkpoint_writes disabled log_num_log_io disabled log_waits disabled log_write_requests disabled log_writes disabled compress_pages_compressed disabled compress_pages_decompressed disabled compression_pad_increments disabled compression_pad_decrements disabled index_page_splits disabled index_page_merge_attempts disabled index_page_merge_successful disabled index_page_reorg_attempts disabled index_page_reorg_successful disabled index_page_discards disabled adaptive_hash_searches disabled adaptive_hash_searches_btree disabled adaptive_hash_pages_added disabled adaptive_hash_pages_removed disabled adaptive_hash_rows_added disabled adaptive_hash_rows_removed disabled adaptive_hash_rows_deleted_no_hash_entry disabled adaptive_hash_rows_updated disabled file_num_open_files disabled ibuf_merges_insert disabled ibuf_merges_delete_mark disabled ibuf_merges_delete disabled ibuf_merges_discard_insert disabled ibuf_merges_discard_delete_mark disabled ibuf_merges_discard_delete disabled ibuf_merges disabled ibuf_size disabled innodb_master_thread_sleeps disabled innodb_activity_count disabled innodb_master_active_loops disabled innodb_master_idle_loops disabled innodb_background_drop_table_usec disabled innodb_ibuf_merge_usec disabled innodb_log_flush_usec disabled innodb_mem_validate_usec disabled innodb_master_purge_usec disabled innodb_dict_lru_usec disabled innodb_checkpoint_usec disabled innodb_dblwr_writes disabled innodb_dblwr_pages_written disabled innodb_page_size disabled innodb_rwlock_s_spin_waits disabled innodb_rwlock_x_spin_waits disabled innodb_rwlock_s_spin_rounds disabled innodb_rwlock_x_spin_rounds disabled innodb_rwlock_s_os_waits disabled innodb_rwlock_x_os_waits disabled dml_reads disabled dml_inserts disabled dml_deletes disabled dml_updates disabled ddl_background_drop_indexes disabled ddl_background_drop_tables disabled ddl_online_create_index disabled ddl_pending_alter_table disabled icp_attempts disabled icp_no_match disabled icp_out_of_range disabled icp_match disabled set global innodb_monitor_enable = all; select name from information_schema.innodb_metrics where status!='enabled'; name set global innodb_monitor_enable = aaa; ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of 'aaa' set global innodb_monitor_disable = All; select name from information_schema.innodb_metrics where status!='disabled'; name set global innodb_monitor_reset_all = all; select name from information_schema.innodb_metrics where count!=0; name set global innodb_monitor_enable = "%lock%"; select name from information_schema.innodb_metrics where status != IF(name like "%lock%", 'enabled', 'disabled'); name set global innodb_monitor_disable = "%lock%"; select name, status from information_schema.innodb_metrics where name like "%lock%"; name status lock_deadlocks disabled lock_timeouts disabled lock_rec_lock_waits disabled lock_table_lock_waits disabled lock_rec_lock_requests disabled lock_rec_lock_created disabled lock_rec_lock_removed disabled lock_rec_locks disabled lock_table_lock_created disabled lock_table_lock_removed disabled lock_table_locks disabled lock_row_lock_current_waits disabled lock_row_lock_time disabled lock_row_lock_time_max disabled lock_row_lock_waits disabled lock_row_lock_time_avg disabled innodb_rwlock_s_spin_waits disabled innodb_rwlock_x_spin_waits disabled innodb_rwlock_s_spin_rounds disabled innodb_rwlock_x_spin_rounds disabled innodb_rwlock_s_os_waits disabled innodb_rwlock_x_os_waits disabled set global innodb_monitor_enable = "%lock*"; ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '%lock*' set global innodb_monitor_enable="%%%%%%%%%%%%%%%%%%%%%%%%%%%"; select name from information_schema.innodb_metrics where status!='enabled'; name set global innodb_monitor_disable="%%%%%"; select name from information_schema.innodb_metrics where status!='disabled'; name set global innodb_monitor_enable="%"; select name from information_schema.innodb_metrics where status!='enabled'; name set global innodb_monitor_disable="%_%"; select name from information_schema.innodb_metrics where status!='disabled'; name set global innodb_monitor_enable="log%%%%"; select name from information_schema.innodb_metrics where status != IF(name like "log%", 'enabled', 'disabled'); name set global innodb_monitor_enable="os_%a_fs_ncs"; set global innodb_monitor_enable="os%pending%"; select name, status from information_schema.innodb_metrics where name like "os%"; name status os_data_reads disabled os_data_writes disabled os_data_fsyncs enabled os_pending_reads enabled os_pending_writes enabled os_log_bytes_written disabled os_log_fsyncs disabled os_log_pending_fsyncs enabled os_log_pending_writes enabled set global innodb_monitor_enable=""; ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '' set global innodb_monitor_enable="_"; ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '_' set global innodb_monitor_disable = module_metadata; set global innodb_monitor_reset_all = module_metadata; set global innodb_monitor_enable = metadata_table_handles_opened; create table monitor_test(col int) engine = innodb; select * from monitor_test; col select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name = "metadata_table_handles_opened"; name max_count min_count count max_count_reset min_count_reset count_reset status metadata_table_handles_opened 1 NULL 1 1 NULL 1 enabled set global innodb_monitor_reset = metadata_table_handles_opened; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name = "metadata_table_handles_opened"; name max_count min_count count max_count_reset min_count_reset count_reset status metadata_table_handles_opened 1 NULL 1 NULL NULL 0 enabled drop table monitor_test; create table monitor_test(col int) engine = innodb; select * from monitor_test; col select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name = "metadata_table_handles_opened"; name max_count min_count count max_count_reset min_count_reset count_reset status metadata_table_handles_opened 2 NULL 2 1 NULL 1 enabled set global innodb_monitor_reset_all = metadata_table_handles_opened; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name = "metadata_table_handles_opened"; name max_count min_count count max_count_reset min_count_reset count_reset status metadata_table_handles_opened 2 NULL 2 1 NULL 1 enabled set global innodb_monitor_disable = metadata_table_handles_opened; set global innodb_monitor_reset = metadata_table_handles_opened; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name = "metadata_table_handles_opened"; name max_count min_count count max_count_reset min_count_reset count_reset status metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled drop table monitor_test; create table monitor_test(col int) engine = innodb; select * from monitor_test; col select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name = "metadata_table_handles_opened"; name max_count min_count count max_count_reset min_count_reset count_reset status metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled set global innodb_monitor_reset_all = metadata_table_handles_opened; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name = "metadata_table_handles_opened"; name max_count min_count count max_count_reset min_count_reset count_reset status metadata_table_handles_opened NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_enable = metadata_table_handles_opened; drop table monitor_test; create table monitor_test(col int) engine = innodb stats_persistent=0; select * from monitor_test; col select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name = "metadata_table_handles_opened"; name max_count min_count count max_count_reset min_count_reset count_reset status metadata_table_handles_opened 1 NULL 1 1 NULL 1 enabled set global innodb_monitor_enable = metadata_table_handles_closed; create index idx on monitor_test(col); select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name = "metadata_table_handles_closed"; name max_count min_count count max_count_reset min_count_reset count_reset status metadata_table_handles_closed 1 NULL 1 1 NULL 1 enabled select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "metadata%"; name max_count min_count count max_count_reset min_count_reset count_reset status metadata_table_handles_opened 2 NULL 2 2 NULL 2 enabled metadata_table_handles_closed 1 NULL 1 1 NULL 1 enabled metadata_table_reference_count NULL NULL 0 NULL NULL 0 disabled metadata_mem_pool_size NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_disable = module_metadata; set global innodb_monitor_reset = module_metadata; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "metadata%"; name max_count min_count count max_count_reset min_count_reset count_reset status metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled metadata_table_handles_closed 1 NULL 1 NULL NULL 0 disabled metadata_table_reference_count NULL NULL 0 NULL NULL 0 disabled metadata_mem_pool_size NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_reset_all = module_metadata; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "metadata%"; name max_count min_count count max_count_reset min_count_reset count_reset status metadata_table_handles_opened NULL NULL 0 NULL NULL 0 disabled metadata_table_handles_closed NULL NULL 0 NULL NULL 0 disabled metadata_table_reference_count NULL NULL 0 NULL NULL 0 disabled metadata_mem_pool_size NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_enable = module_trx; begin; insert into monitor_test values(9); commit; begin; insert into monitor_test values(9); rollback; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "trx_rollbacks" or name like "trx_active_transactions"; name max_count min_count count max_count_reset min_count_reset count_reset status trx_rollbacks 1 NULL 1 1 NULL 1 enabled trx_active_transactions 1 0 0 1 0 0 enabled set global innodb_monitor_disable = module_trx; set global innodb_monitor_enable = module_dml; insert into monitor_test values(9); update monitor_test set col = 10 where col = 9; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status dml_reads 4 NULL 4 4 NULL 4 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 0 NULL 0 0 NULL 0 enabled dml_updates 2 NULL 2 2 NULL 2 enabled delete from monitor_test; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status dml_reads 6 NULL 6 6 NULL 6 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 2 NULL 2 2 NULL 2 enabled dml_updates 2 NULL 2 2 NULL 2 enabled set global innodb_monitor_reset = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status dml_reads 6 NULL 6 0 NULL 0 enabled dml_inserts 1 NULL 1 0 NULL 0 enabled dml_deletes 2 NULL 2 0 NULL 0 enabled dml_updates 2 NULL 2 0 NULL 0 enabled insert into monitor_test values(9); insert into monitor_test values(1); delete from monitor_test; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status dml_reads 8 NULL 8 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled set global innodb_monitor_disable = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status dml_reads 8 NULL 8 2 NULL 2 disabled dml_inserts 3 NULL 3 2 NULL 2 disabled dml_deletes 4 NULL 4 2 NULL 2 disabled dml_updates 2 NULL 2 0 NULL 0 disabled set global innodb_monitor_reset_all = module_dml; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts NULL NULL 0 NULL NULL 0 disabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_enable = dml_inserts; insert into monitor_test values(9); insert into monitor_test values(1); delete from monitor_test; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status dml_reads NULL NULL 0 NULL NULL 0 disabled dml_inserts 2 NULL 2 2 NULL 2 enabled dml_deletes NULL NULL 0 NULL NULL 0 disabled dml_updates NULL NULL 0 NULL NULL 0 disabled set global innodb_monitor_disable = module_dml; drop table monitor_test; set global innodb_monitor_enable = file_num_open_files; select name, max_count, min_count, count, max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "file_num_open_files"; name max_count min_count count max_count_reset min_count_reset count_reset status file_num_open_files # # # # # # enabled set global innodb_monitor_disable = file_num_open_files; set global innodb_monitor_enable = "icp%"; create table monitor_test(a char(3), b int, c char(2), primary key (a(1), c(1)), key(b)) engine = innodb; insert into monitor_test values("13", 2, "aa"); select a from monitor_test where b < 1 for update; a select name, count from information_schema.innodb_metrics where name like "icp%"; name count icp_attempts 1 icp_no_match 0 icp_out_of_range 1 icp_match 0 select a from monitor_test where b < 3 for update; a 13 select name, count from information_schema.innodb_metrics where name like "icp%"; name count icp_attempts 2 icp_no_match 0 icp_out_of_range 1 icp_match 1 drop table monitor_test; set global innodb_monitor_disable = all; set global innodb_monitor_reset_all = all; select 1 from `information_schema`.`INNODB_METRICS` where case (1) when (1) then (AVG_COUNT_RESET) else (1) end; 1 set global innodb_monitor_enable = default; set global innodb_monitor_disable = default; set global innodb_monitor_reset = default; set global innodb_monitor_reset_all = default; # # Bug#22576241 SETTING INNODB_MONITOR_ENABLE TO ALL DOES NOT ENABLE ALL # MONITORS # CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'buffer_page_written_index_leaf'; NAME COUNT buffer_page_written_index_leaf 0 SET GLOBAL innodb_monitor_enable='module_buffer_page'; INSERT INTO t1 VALUES (1), (2), (3), (4); FLUSH TABLES t1 FOR EXPORT; UNLOCK TABLES; SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'buffer_page_written_index_leaf'; NAME COUNT buffer_page_written_index_leaf NNNN SET GLOBAL innodb_monitor_disable='module_buffer_page'; SET GLOBAL innodb_monitor_reset_all='module_buffer_page'; SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'buffer_page_written_index_leaf'; NAME COUNT buffer_page_written_index_leaf 0 SET GLOBAL innodb_monitor_enable='%'; INSERT INTO t1 VALUES (5), (6), (7), (8); FLUSH TABLES t1 FOR EXPORT; UNLOCK TABLES; SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'buffer_page_written_index_leaf'; NAME COUNT buffer_page_written_index_leaf NNNN SET GLOBAL innodb_monitor_disable='%'; SET GLOBAL innodb_monitor_reset_all='%'; SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'buffer_page_written_index_leaf'; NAME COUNT buffer_page_written_index_leaf 0 SET GLOBAL innodb_monitor_enable='ALL'; INSERT INTO t1 VALUES (9), (10), (11), (12); FLUSH TABLES t1 FOR EXPORT; UNLOCK TABLES; SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'buffer_page_written_index_leaf'; NAME COUNT buffer_page_written_index_leaf NNNN SET GLOBAL innodb_monitor_enable=default; SET GLOBAL innodb_monitor_disable=default; SET GLOBAL innodb_monitor_reset_all=default; DROP TABLE t1;