mirror of
https://github.com/MariaDB/server.git
synced 2025-02-05 21:32:18 +01:00
788 lines
29 KiB
Text
788 lines
29 KiB
Text
select name, if(enabled,'enabled','disabled') status
|
|
from information_schema.innodb_metrics;
|
|
name status
|
|
metadata_table_handles_opened disabled
|
|
lock_deadlocks enabled
|
|
lock_timeouts enabled
|
|
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 enabled
|
|
lock_row_lock_time enabled
|
|
lock_row_lock_time_max enabled
|
|
lock_row_lock_waits enabled
|
|
lock_row_lock_time_avg enabled
|
|
buffer_pool_size enabled
|
|
buffer_pool_reads enabled
|
|
buffer_pool_read_requests enabled
|
|
buffer_pool_write_requests enabled
|
|
buffer_pool_wait_free enabled
|
|
buffer_pool_read_ahead enabled
|
|
buffer_pool_read_ahead_evicted enabled
|
|
buffer_pool_pages_total enabled
|
|
buffer_pool_pages_misc enabled
|
|
buffer_pool_pages_data enabled
|
|
buffer_pool_bytes_data enabled
|
|
buffer_pool_pages_dirty enabled
|
|
buffer_pool_bytes_dirty enabled
|
|
buffer_pool_pages_free enabled
|
|
buffer_pages_created enabled
|
|
buffer_pages_written enabled
|
|
buffer_pages_read enabled
|
|
buffer_index_sec_rec_cluster_reads enabled
|
|
buffer_index_sec_rec_cluster_reads_avoided enabled
|
|
buffer_data_reads enabled
|
|
buffer_data_written enabled
|
|
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 disabled
|
|
buffer_flush_adaptive_avg_pass disabled
|
|
buffer_LRU_get_free_loops 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 enabled
|
|
buffer_LRU_batch_evict_total_pages enabled
|
|
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 enabled
|
|
os_data_writes enabled
|
|
os_data_fsyncs enabled
|
|
os_pending_reads enabled
|
|
os_pending_writes enabled
|
|
os_log_bytes_written enabled
|
|
os_log_fsyncs enabled
|
|
os_log_pending_fsyncs enabled
|
|
os_log_pending_writes enabled
|
|
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_rseg_history_len enabled
|
|
trx_undo_slots_used disabled
|
|
trx_undo_slots_cached enabled
|
|
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_pending_log_flushes disabled
|
|
log_pending_checkpoint_writes disabled
|
|
log_num_log_io disabled
|
|
log_waits enabled
|
|
log_write_requests enabled
|
|
log_writes enabled
|
|
log_padded enabled
|
|
compress_pages_compressed disabled
|
|
compress_pages_decompressed disabled
|
|
compression_pad_increments disabled
|
|
compression_pad_decrements disabled
|
|
compress_saved disabled
|
|
compress_pages_page_compressed disabled
|
|
compress_page_compressed_trim_op disabled
|
|
compress_pages_page_decompressed disabled
|
|
compress_pages_page_compression_error disabled
|
|
compress_pages_encrypted disabled
|
|
compress_pages_decrypted 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 enabled
|
|
adaptive_hash_searches_btree enabled
|
|
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 enabled
|
|
ibuf_merges_insert enabled
|
|
ibuf_merges_delete_mark enabled
|
|
ibuf_merges_delete enabled
|
|
ibuf_merges_discard_insert enabled
|
|
ibuf_merges_discard_delete_mark enabled
|
|
ibuf_merges_discard_delete enabled
|
|
ibuf_merges enabled
|
|
ibuf_size enabled
|
|
innodb_master_thread_sleeps disabled
|
|
innodb_activity_count enabled
|
|
innodb_master_active_loops disabled
|
|
innodb_master_idle_loops disabled
|
|
innodb_log_flush_usec disabled
|
|
innodb_dict_lru_usec disabled
|
|
innodb_dict_lru_count_active disabled
|
|
innodb_dict_lru_count_idle disabled
|
|
innodb_dblwr_writes enabled
|
|
innodb_dblwr_pages_written enabled
|
|
innodb_page_size enabled
|
|
dml_reads disabled
|
|
dml_inserts enabled
|
|
dml_deletes enabled
|
|
dml_updates enabled
|
|
dml_system_reads enabled
|
|
dml_system_inserts enabled
|
|
dml_system_deletes enabled
|
|
dml_system_updates enabled
|
|
ddl_background_drop_indexes 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
|
|
create temporary table orig_innodb_metrics as select name, enabled from information_schema.innodb_metrics;
|
|
set global innodb_monitor_disable = All;
|
|
select name from information_schema.innodb_metrics where enabled;
|
|
name
|
|
set global innodb_monitor_enable = all;
|
|
select name from information_schema.innodb_metrics where not 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 enabled;
|
|
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 enabled != (name like "%lock%");
|
|
name
|
|
set global innodb_monitor_disable = "%lock%";
|
|
select name, if(enabled,'enabled','disabled') 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
|
|
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 not enabled;
|
|
name
|
|
set global innodb_monitor_disable="%%%%%";
|
|
select name from information_schema.innodb_metrics where enabled;
|
|
name
|
|
set global innodb_monitor_enable="%";
|
|
select name from information_schema.innodb_metrics where not enabled;
|
|
name
|
|
set global innodb_monitor_disable="%_%";
|
|
select name from information_schema.innodb_metrics where enabled;
|
|
name
|
|
set global innodb_monitor_enable="log%%%%";
|
|
select name from information_schema.innodb_metrics
|
|
where enabled != (name like "log%");
|
|
name
|
|
set global innodb_monitor_enable="os_%a_fs_ncs";
|
|
set global innodb_monitor_enable="os%pending%";
|
|
select name, if(enabled,'enabled','disabled') 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,
|
|
if(enabled,'enabled','disabled') 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,
|
|
if(enabled,'enabled','disabled') 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,
|
|
if(enabled,'enabled','disabled') 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,
|
|
if(enabled,'enabled','disabled') 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,
|
|
if(enabled,'enabled','disabled') 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,
|
|
if(enabled,'enabled','disabled') 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,
|
|
if(enabled,'enabled','disabled') 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,
|
|
if(enabled,'enabled','disabled') 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 1 NULL 1 1 NULL 1 enabled
|
|
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,
|
|
if(enabled,'enabled','disabled') 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 1 NULL 1 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,
|
|
if(enabled,'enabled','disabled') 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
|
|
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,
|
|
if(enabled,'enabled','disabled') status
|
|
from information_schema.innodb_metrics
|
|
where name='trx_rollbacks';
|
|
name max_count min_count count max_count_reset min_count_reset count_reset status
|
|
trx_rollbacks 1 NULL 1 1 NULL 1 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,
|
|
if(enabled,'enabled','disabled') 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 2 NULL 2 2 NULL 2 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
|
|
dml_system_reads 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_inserts 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_deletes 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_updates 0 NULL 0 0 NULL 0 enabled
|
|
delete from monitor_test;
|
|
select name, max_count, min_count, count,
|
|
max_count_reset, min_count_reset, count_reset,
|
|
if(enabled,'enabled','disabled') 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 2 NULL 2 2 NULL 2 enabled
|
|
dml_updates 2 NULL 2 2 NULL 2 enabled
|
|
dml_system_reads 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_inserts 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_deletes 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_updates 0 NULL 0 0 NULL 0 enabled
|
|
set global innodb_monitor_reset = module_dml;
|
|
select name, max_count, min_count, count,
|
|
max_count_reset, min_count_reset, count_reset,
|
|
if(enabled,'enabled','disabled') 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 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
|
|
dml_system_reads 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_inserts 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_deletes 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_updates 0 NULL 0 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,
|
|
if(enabled,'enabled','disabled') 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 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
|
|
dml_system_reads 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_inserts 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_deletes 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_updates 0 NULL 0 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,
|
|
if(enabled,'enabled','disabled') 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 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
|
|
dml_system_reads 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_inserts 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_deletes 0 NULL 0 0 NULL 0 enabled
|
|
dml_system_updates 0 NULL 0 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,
|
|
if(enabled,'enabled','disabled') 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 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
|
|
dml_system_reads 0 NULL 0 0 NULL 0 disabled
|
|
dml_system_inserts 0 NULL 0 0 NULL 0 disabled
|
|
dml_system_deletes 0 NULL 0 0 NULL 0 disabled
|
|
dml_system_updates 0 NULL 0 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,
|
|
if(enabled,'enabled','disabled') 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
|
|
dml_system_reads NULL NULL 0 NULL NULL 0 disabled
|
|
dml_system_inserts NULL NULL 0 NULL NULL 0 disabled
|
|
dml_system_deletes NULL NULL 0 NULL NULL 0 disabled
|
|
dml_system_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,
|
|
if(enabled,'enabled','disabled') 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
|
|
dml_system_reads NULL NULL 0 NULL NULL 0 disabled
|
|
dml_system_inserts NULL NULL 0 NULL NULL 0 disabled
|
|
dml_system_deletes NULL NULL 0 NULL NULL 0 disabled
|
|
dml_system_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,
|
|
if(enabled,'enabled','disabled') 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 STATS_PERSISTENT=0;
|
|
SELECT /*1*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
|
|
LIKE 'buffer_page_written_index_leaf';
|
|
NAME COUNT > 0
|
|
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 /*2*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
|
|
LIKE 'buffer_page_written_index_leaf';
|
|
NAME COUNT > 0
|
|
buffer_page_written_index_leaf 1
|
|
SET GLOBAL innodb_monitor_disable='module_buffer_page';
|
|
SET GLOBAL innodb_monitor_reset_all='module_buffer_page';
|
|
SELECT /*3*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
|
|
LIKE 'buffer_page_written_index_leaf';
|
|
NAME COUNT > 0
|
|
buffer_page_written_index_leaf 0
|
|
SET GLOBAL innodb_monitor_enable='%';
|
|
SET GLOBAL innodb_monitor_reset_all= '%', innodb_compression_algorithm= foo;
|
|
ERROR 42000: Variable 'innodb_compression_algorithm' can't be set to the value of 'foo'
|
|
INSERT INTO t1 VALUES (5), (6), (7), (8);
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
UNLOCK TABLES;
|
|
SELECT /*4*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
|
|
LIKE 'buffer_page_written_index_leaf';
|
|
NAME COUNT > 0
|
|
buffer_page_written_index_leaf 1
|
|
SET GLOBAL innodb_monitor_disable='%';
|
|
SET GLOBAL innodb_monitor_reset_all='%';
|
|
SELECT /*5*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
|
|
LIKE 'buffer_page_written_index_leaf';
|
|
NAME COUNT > 0
|
|
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 /*6*/ NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
|
|
LIKE 'buffer_page_written_index_leaf';
|
|
NAME COUNT > 0
|
|
buffer_page_written_index_leaf 1
|
|
DROP TABLE t1;
|
|
CREATE TABLE fl0 (
|
|
id INT NOT NULL PRIMARY KEY
|
|
) ENGINE = InnoDB;
|
|
CREATE TABLE fl1 (
|
|
id INT NOT NULL PRIMARY KEY,
|
|
fl0_id INT,
|
|
CONSTRAINT `fkl0`
|
|
FOREIGN KEY (fl0_id) REFERENCES fl0 (id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE RESTRICT
|
|
) ENGINE = InnoDB;
|
|
CREATE TABLE fl2 (
|
|
id INT NOT NULL PRIMARY KEY,
|
|
fl1_id INT,
|
|
CONSTRAINT `fkl1`
|
|
FOREIGN KEY (fl1_id) REFERENCES fl1 (id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE SET NULL
|
|
) ENGINE = InnoDB;
|
|
INSERT INTO fl0 VALUES (1000);
|
|
INSERT INTO fl1 VALUES (500, 1000), (1500, 1000);
|
|
INSERT INTO fl2 VALUES (200, 500), (800, 500), (1200, 1500), (1800, 1500);
|
|
CREATE TABLE t1(id INT PRIMARY KEY, a INT, b CHAR(1), UNIQUE KEY u(a,b))
|
|
ENGINE=InnoDB;
|
|
SET @start = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
|
|
= 'lock_rec_lock_created');
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d');
|
|
DELETE FROM t1 WHERE a = 9999 AND b='b';
|
|
COMMIT;
|
|
SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME
|
|
= 'lock_rec_lock_created');
|
|
SELECT @end - @start;
|
|
@end - @start
|
|
1
|
|
# Records must not be S/X-locked if a table is X-locked.
|
|
SET @start = @end;
|
|
SET autocommit = 0;
|
|
BEGIN;
|
|
LOCK TABLE t1 WRITE;
|
|
SELECT * FROM t1;
|
|
id a b
|
|
1 1 a
|
|
4 4 d
|
|
3 10000 c
|
|
SELECT * FROM t1 WHERE a>=10000;
|
|
id a b
|
|
3 10000 c
|
|
SELECT * FROM t1 FOR UPDATE;
|
|
id a b
|
|
1 1 a
|
|
4 4 d
|
|
3 10000 c
|
|
SELECT * FROM t1 WHERE a>=10000 FOR UPDATE;
|
|
id a b
|
|
3 10000 c
|
|
UPDATE t1 SET b = 'b' WHERE id = 4;
|
|
UPDATE t1 SET b = 'b' WHERE a = 10000;
|
|
REPLACE INTO t1 VALUES (4,3,'a');
|
|
INSERT INTO t1 VALUES (3,1,'e') ON DUPLICATE KEY UPDATE b = 'b';
|
|
INSERT INTO t1 VALUES (5,5,'e');
|
|
DELETE FROM t1 WHERE a = 1 AND b='a';
|
|
DELETE FROM t1;
|
|
COMMIT;
|
|
UNLOCK TABLES;
|
|
SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
|
|
SELECT @end - @start;
|
|
@end - @start
|
|
0
|
|
# Records must not be S-locked if a table is S-locked.
|
|
SET @start = @end;
|
|
BEGIN;
|
|
LOCK TABLE t1 WRITE;
|
|
INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d');
|
|
DELETE FROM t1 WHERE a = 9999 AND b='b';
|
|
COMMIT;
|
|
UNLOCK TABLES;
|
|
BEGIN;
|
|
LOCK TABLE t1 READ;
|
|
SELECT * FROM t1 LOCK IN SHARE MODE;
|
|
id a b
|
|
1 1 a
|
|
4 4 d
|
|
3 10000 c
|
|
SELECT * FROM t1 WHERE a>=10000 LOCK IN SHARE MODE;
|
|
id a b
|
|
3 10000 c
|
|
COMMIT;
|
|
UNLOCK TABLES;
|
|
SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
|
|
SELECT @end - @start;
|
|
@end - @start
|
|
0
|
|
# Records must not be S-locked for foreign keys enforcement
|
|
SET @start = @end;
|
|
BEGIN;
|
|
LOCK TABLE fl0 READ, fl1 READ, fl2 WRITE;
|
|
INSERT INTO fl2 VALUES (300, 500), (700, 500), (1300, 1500), (1700, 1500);
|
|
SELECT * FROM fl1 LOCK IN SHARE MODE;
|
|
id fl0_id
|
|
500 1000
|
|
1500 1000
|
|
COMMIT;
|
|
UNLOCK TABLES;
|
|
SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
|
|
SELECT @end - @start;
|
|
@end - @start
|
|
0
|
|
# Records must not be X-locked for foreign keys cascade
|
|
SET @start = @end;
|
|
BEGIN;
|
|
LOCK TABLE fl0 READ, fl1 WRITE, fl2 WRITE;
|
|
DELETE FROM fl1 WHERE id = 1500;
|
|
UPDATE fl1 SET id = 2500 WHERE id = 500;
|
|
COMMIT;
|
|
UNLOCK TABLES;
|
|
SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME = 'lock_rec_lock_created');
|
|
SELECT @end - @start;
|
|
@end - @start
|
|
0
|
|
SET autocommit = default;
|
|
DROP TABLE t1;
|
|
DROP TABLE fl2;
|
|
DROP TABLE fl1;
|
|
DROP TABLE fl0;
|
|
set global innodb_monitor_disable = 'adaptive\\_hash\\_p%';
|
|
set global innodb_monitor_disable = 'adaptive\\_hash\\_r%';
|
|
set global innodb_monitor_disable = 'buffer\\_LRU\\_batch\\_n%';
|
|
set global innodb_monitor_disable = 'buffer\\_LRU\\_batch\\_s%';
|
|
set global innodb_monitor_disable = 'buffer\\_LRU\\_g%';
|
|
set global innodb_monitor_disable = 'buffer\\_LRU\\_s%';
|
|
set global innodb_monitor_disable = 'buffer\\_LRU\\_u%';
|
|
set global innodb_monitor_disable = 'buffer\\_f%';
|
|
set global innodb_monitor_disable = 'buffer\\_page\\_%';
|
|
set global innodb_monitor_disable = 'c%';
|
|
set global innodb_monitor_disable = 'ddl%';
|
|
set global innodb_monitor_disable = 'dml\\_reads%';
|
|
set global innodb_monitor_disable = 'icp%';
|
|
set global innodb_monitor_disable = 'index\\_p%';
|
|
set global innodb_monitor_disable = 'innodb\\_di%';
|
|
set global innodb_monitor_disable = 'innodb\\_l%';
|
|
set global innodb_monitor_disable = 'innodb\\_m%';
|
|
set global innodb_monitor_disable = 'lock\\_re%';
|
|
set global innodb_monitor_disable = 'lock\\_ta%';
|
|
set global innodb_monitor_disable = 'log%';
|
|
set global innodb_monitor_disable = 'm%';
|
|
set global innodb_monitor_disable = 'p%';
|
|
set global innodb_monitor_disable = 't%';
|
|
set global innodb_monitor_enable = 'log_padded';
|
|
set global innodb_monitor_enable = 'log\\_w%';
|
|
set global innodb_monitor_enable = 'trx_rseg_history_len';
|
|
set global innodb_monitor_enable = 'trx_undo_slots_cached';
|
|
set global innodb_monitor_enable=default;
|
|
Warnings:
|
|
Warning 1230 Default value is not defined for this set option. Please specify correct counter or module name.
|
|
set global innodb_monitor_disable=default;
|
|
Warnings:
|
|
Warning 1230 Default value is not defined for this set option. Please specify correct counter or module name.
|
|
set global innodb_monitor_reset_all=default;
|
|
Warnings:
|
|
Warning 1230 Default value is not defined for this set option. Please specify correct counter or module name.
|
|
select name, orig.enabled, new.enabled from
|
|
orig_innodb_metrics orig join information_schema.innodb_metrics new using(name)
|
|
where orig.enabled != new.enabled;
|
|
name enabled enabled
|