696 lines
26 KiB
Plaintext
696 lines
26 KiB
Plaintext
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
|
|
lock_deadlocks disabled
|
|
lock_deadlock_false_positives disabled
|
|
lock_deadlock_rounds disabled
|
|
lock_threads_waiting 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_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_n_to_flush_by_age disabled
|
|
buffer_flush_adaptive_avg_time_slot disabled
|
|
buffer_LRU_batch_flush_avg_time_slot disabled
|
|
buffer_flush_adaptive_avg_time_thread disabled
|
|
buffer_LRU_batch_flush_avg_time_thread disabled
|
|
buffer_flush_adaptive_avg_time_est disabled
|
|
buffer_LRU_batch_flush_avg_time_est disabled
|
|
buffer_flush_avg_time disabled
|
|
buffer_flush_adaptive_avg_pass disabled
|
|
buffer_LRU_batch_flush_avg_pass disabled
|
|
buffer_flush_avg_pass disabled
|
|
buffer_LRU_get_free_loops disabled
|
|
buffer_LRU_get_free_waits 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_flush_total_pages disabled
|
|
buffer_LRU_batches_flush disabled
|
|
buffer_LRU_batch_flush_pages disabled
|
|
buffer_LRU_batch_evict_total_pages disabled
|
|
buffer_LRU_batches_evict disabled
|
|
buffer_LRU_batch_evict_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_rseg_array 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_rseg_array disabled
|
|
buffer_page_written_other disabled
|
|
buffer_page_written_on_log_no_waits disabled
|
|
buffer_page_written_on_log_waits disabled
|
|
buffer_page_written_on_log_wait_loops 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_on_log_no_waits disabled
|
|
trx_on_log_waits disabled
|
|
trx_on_log_wait_loops 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
|
|
purge_truncate_history_count disabled
|
|
purge_truncate_history_usec disabled
|
|
undo_truncate_count disabled
|
|
undo_truncate_sweep_count disabled
|
|
undo_truncate_sweep_usec disabled
|
|
undo_truncate_start_logging_count disabled
|
|
undo_truncate_flush_count disabled
|
|
undo_truncate_flush_usec disabled
|
|
undo_truncate_done_logging_count disabled
|
|
undo_truncate_usec disabled
|
|
log_lsn_last_flush disabled
|
|
log_lsn_last_checkpoint disabled
|
|
log_lsn_current disabled
|
|
log_lsn_archived disabled
|
|
log_lsn_checkpoint_age disabled
|
|
log_lsn_buf_dirty_pages_added disabled
|
|
log_lsn_buf_pool_oldest_approx disabled
|
|
log_lsn_buf_pool_oldest_lwm disabled
|
|
log_max_modified_age_async disabled
|
|
log_max_modified_age_sync disabled
|
|
log_waits disabled
|
|
log_write_requests disabled
|
|
log_writes disabled
|
|
log_flush_total_time disabled
|
|
log_flush_max_time disabled
|
|
log_flush_avg_time disabled
|
|
log_flush_lsn_avg_rate disabled
|
|
log_full_block_writes disabled
|
|
log_partial_block_writes disabled
|
|
log_padded disabled
|
|
log_next_file disabled
|
|
log_checkpoints disabled
|
|
log_free_space disabled
|
|
log_concurrency_margin disabled
|
|
log_writer_no_waits disabled
|
|
log_writer_waits disabled
|
|
log_writer_wait_loops disabled
|
|
log_writer_on_file_space_waits disabled
|
|
log_writer_on_archiver_waits disabled
|
|
log_flusher_no_waits disabled
|
|
log_flusher_waits disabled
|
|
log_flusher_wait_loops disabled
|
|
log_write_notifier_no_waits disabled
|
|
log_write_notifier_waits disabled
|
|
log_write_notifier_wait_loops disabled
|
|
log_flush_notifier_no_waits disabled
|
|
log_flush_notifier_waits disabled
|
|
log_flush_notifier_wait_loops disabled
|
|
log_write_to_file_requests_interval disabled
|
|
log_on_write_no_waits disabled
|
|
log_on_write_waits disabled
|
|
log_on_write_wait_loops disabled
|
|
log_on_flush_no_waits disabled
|
|
log_on_flush_waits disabled
|
|
log_on_flush_wait_loops disabled
|
|
log_on_recent_written_wait_loops disabled
|
|
log_on_recent_closed_wait_loops disabled
|
|
log_on_buffer_space_no_waits disabled
|
|
log_on_buffer_space_waits disabled
|
|
log_on_buffer_space_wait_loops disabled
|
|
log_on_file_space_no_waits disabled
|
|
log_on_file_space_waits disabled
|
|
log_on_file_space_wait_loops 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_mem_validate_usec disabled
|
|
innodb_master_purge_usec disabled
|
|
innodb_dict_lru_usec disabled
|
|
innodb_dict_lru_count 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_sx_spin_waits disabled
|
|
innodb_rwlock_s_spin_rounds disabled
|
|
innodb_rwlock_x_spin_rounds disabled
|
|
innodb_rwlock_sx_spin_rounds disabled
|
|
innodb_rwlock_s_os_waits disabled
|
|
innodb_rwlock_x_os_waits disabled
|
|
innodb_rwlock_sx_os_waits disabled
|
|
dml_reads disabled
|
|
dml_inserts disabled
|
|
dml_deletes disabled
|
|
dml_updates disabled
|
|
ddl_background_drop_tables disabled
|
|
ddl_online_create_index disabled
|
|
ddl_pending_alter_table disabled
|
|
ddl_sort_file_alter_table disabled
|
|
ddl_log_file_alter_table disabled
|
|
icp_attempts disabled
|
|
icp_no_match disabled
|
|
icp_out_of_range disabled
|
|
icp_match disabled
|
|
module_cpu disabled
|
|
cpu_utime_abs disabled
|
|
cpu_stime_abs disabled
|
|
cpu_utime_pct disabled
|
|
cpu_stime_pct disabled
|
|
cpu_n disabled
|
|
module_page_track disabled
|
|
page_track_resets disabled
|
|
page_track_partial_block_writes disabled
|
|
page_track_full_block_writes disabled
|
|
page_track_checkpoint_partial_flush_request 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_deadlock_false_positives disabled
|
|
lock_deadlock_rounds disabled
|
|
lock_threads_waiting 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
|
|
log_full_block_writes disabled
|
|
log_partial_block_writes disabled
|
|
innodb_rwlock_s_spin_waits disabled
|
|
innodb_rwlock_x_spin_waits disabled
|
|
innodb_rwlock_sx_spin_waits disabled
|
|
innodb_rwlock_s_spin_rounds disabled
|
|
innodb_rwlock_x_spin_rounds disabled
|
|
innodb_rwlock_sx_spin_rounds disabled
|
|
innodb_rwlock_s_os_waits disabled
|
|
innodb_rwlock_x_os_waits disabled
|
|
innodb_rwlock_sx_os_waits disabled
|
|
page_track_partial_block_writes disabled
|
|
page_track_full_block_writes 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 '_'
|
|
create table monitor_test(col int) engine = innodb;
|
|
drop table monitor_test;
|
|
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
|
|
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
|
|
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
|
|
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;
|