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_splits	disabled
index_merges	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;
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;
