set global innodb_monitor_disable = All; select name, if(enabled,'enabled','disabled') status from information_schema.innodb_metrics; name status metadata_table_handles_opened 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_index_sec_rec_cluster_reads disabled buffer_index_sec_rec_cluster_reads_avoided 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 disabled buffer_flush_adaptive_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_batch_evict_total_pages 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_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_pending_log_flushes disabled log_pending_checkpoint_writes disabled log_num_log_io disabled log_waits disabled log_write_requests disabled log_writes disabled log_padded disabled 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 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_log_flush_usec disabled innodb_dict_lru_usec disabled innodb_dict_lru_count_active disabled innodb_dict_lru_count_idle disabled innodb_dblwr_writes disabled innodb_dblwr_pages_written disabled innodb_page_size disabled dml_reads disabled dml_inserts disabled dml_deletes disabled dml_updates disabled dml_system_reads disabled dml_system_inserts disabled dml_system_deletes disabled dml_system_updates disabled 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 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 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 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 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='%'; INSERT INTO t1 VALUES (5), (6), (7), (8); FLUSH TABLES t1 FOR EXPORT; UNLOCK TABLES; SELECT 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 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 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 0 # 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_enable=default; SET GLOBAL innodb_monitor_disable=default; SET GLOBAL innodb_monitor_reset_all=default;