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;
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;