1set global innodb_monitor_disable = All; 2select name, status from information_schema.innodb_metrics; 3name status 4metadata_table_handles_opened disabled 5metadata_table_handles_closed disabled 6metadata_table_reference_count disabled 7lock_deadlocks disabled 8lock_timeouts disabled 9lock_rec_lock_waits disabled 10lock_table_lock_waits disabled 11lock_rec_lock_requests disabled 12lock_rec_lock_created disabled 13lock_rec_lock_removed disabled 14lock_rec_locks disabled 15lock_table_lock_created disabled 16lock_table_lock_removed disabled 17lock_table_locks disabled 18lock_row_lock_current_waits disabled 19lock_row_lock_time disabled 20lock_row_lock_time_max disabled 21lock_row_lock_waits disabled 22lock_row_lock_time_avg disabled 23buffer_pool_size disabled 24buffer_pool_reads disabled 25buffer_pool_read_requests disabled 26buffer_pool_write_requests disabled 27buffer_pool_wait_free disabled 28buffer_pool_read_ahead disabled 29buffer_pool_read_ahead_evicted disabled 30buffer_pool_pages_total disabled 31buffer_pool_pages_misc disabled 32buffer_pool_pages_data disabled 33buffer_pool_bytes_data disabled 34buffer_pool_pages_dirty disabled 35buffer_pool_bytes_dirty disabled 36buffer_pool_pages_free disabled 37buffer_pages_created disabled 38buffer_pages_written disabled 39buffer_index_pages_written disabled 40buffer_non_index_pages_written disabled 41buffer_pages_read disabled 42buffer_index_sec_rec_cluster_reads disabled 43buffer_index_sec_rec_cluster_reads_avoided disabled 44buffer_data_reads disabled 45buffer_data_written disabled 46buffer_flush_batch_scanned disabled 47buffer_flush_batch_num_scan disabled 48buffer_flush_batch_scanned_per_call disabled 49buffer_flush_batch_total_pages disabled 50buffer_flush_batches disabled 51buffer_flush_batch_pages disabled 52buffer_flush_neighbor_total_pages disabled 53buffer_flush_neighbor disabled 54buffer_flush_neighbor_pages disabled 55buffer_flush_n_to_flush_requested disabled 56buffer_flush_n_to_flush_by_age disabled 57buffer_flush_adaptive_avg_time_slot disabled 58buffer_LRU_batch_flush_avg_time_slot disabled 59buffer_flush_adaptive_avg_time_thread disabled 60buffer_LRU_batch_flush_avg_time_thread disabled 61buffer_flush_adaptive_avg_time_est disabled 62buffer_LRU_batch_flush_avg_time_est disabled 63buffer_flush_avg_time disabled 64buffer_flush_adaptive_avg_pass disabled 65buffer_LRU_batch_flush_avg_pass disabled 66buffer_flush_avg_pass disabled 67buffer_LRU_get_free_loops disabled 68buffer_LRU_get_free_waits disabled 69buffer_flush_avg_page_rate disabled 70buffer_flush_lsn_avg_rate disabled 71buffer_flush_pct_for_dirty disabled 72buffer_flush_pct_for_lsn disabled 73buffer_flush_sync_waits disabled 74buffer_flush_adaptive_total_pages disabled 75buffer_flush_adaptive disabled 76buffer_flush_adaptive_pages disabled 77buffer_flush_sync_total_pages disabled 78buffer_flush_sync disabled 79buffer_flush_sync_pages disabled 80buffer_flush_background_total_pages disabled 81buffer_flush_background disabled 82buffer_flush_background_pages disabled 83buffer_LRU_batch_scanned disabled 84buffer_LRU_batch_num_scan disabled 85buffer_LRU_batch_scanned_per_call disabled 86buffer_LRU_batch_flush_total_pages disabled 87buffer_LRU_batches_flush disabled 88buffer_LRU_batch_flush_pages disabled 89buffer_LRU_batch_evict_total_pages disabled 90buffer_LRU_batches_evict disabled 91buffer_LRU_batch_evict_pages disabled 92buffer_LRU_single_flush_scanned disabled 93buffer_LRU_single_flush_num_scan disabled 94buffer_LRU_single_flush_scanned_per_call disabled 95buffer_LRU_single_flush_failure_count disabled 96buffer_LRU_get_free_search disabled 97buffer_LRU_search_scanned disabled 98buffer_LRU_search_num_scan disabled 99buffer_LRU_search_scanned_per_call disabled 100buffer_LRU_unzip_search_scanned disabled 101buffer_LRU_unzip_search_num_scan disabled 102buffer_LRU_unzip_search_scanned_per_call disabled 103buffer_page_read_index_leaf disabled 104buffer_page_read_index_non_leaf disabled 105buffer_page_read_index_ibuf_leaf disabled 106buffer_page_read_index_ibuf_non_leaf disabled 107buffer_page_read_undo_log disabled 108buffer_page_read_index_inode disabled 109buffer_page_read_ibuf_free_list disabled 110buffer_page_read_ibuf_bitmap disabled 111buffer_page_read_system_page disabled 112buffer_page_read_trx_system disabled 113buffer_page_read_fsp_hdr disabled 114buffer_page_read_xdes disabled 115buffer_page_read_blob disabled 116buffer_page_read_zblob disabled 117buffer_page_read_zblob2 disabled 118buffer_page_read_other disabled 119buffer_page_written_index_leaf disabled 120buffer_page_written_index_non_leaf disabled 121buffer_page_written_index_ibuf_leaf disabled 122buffer_page_written_index_ibuf_non_leaf disabled 123buffer_page_written_undo_log disabled 124buffer_page_written_index_inode disabled 125buffer_page_written_ibuf_free_list disabled 126buffer_page_written_ibuf_bitmap disabled 127buffer_page_written_system_page disabled 128buffer_page_written_trx_system disabled 129buffer_page_written_fsp_hdr disabled 130buffer_page_written_xdes disabled 131buffer_page_written_blob disabled 132buffer_page_written_zblob disabled 133buffer_page_written_zblob2 disabled 134buffer_page_written_other disabled 135os_data_reads disabled 136os_data_writes disabled 137os_data_fsyncs disabled 138os_pending_reads disabled 139os_pending_writes disabled 140os_log_bytes_written disabled 141os_log_fsyncs disabled 142os_log_pending_fsyncs disabled 143os_log_pending_writes disabled 144trx_rw_commits disabled 145trx_ro_commits disabled 146trx_nl_ro_commits disabled 147trx_commits_insert_update disabled 148trx_rollbacks disabled 149trx_rollbacks_savepoint disabled 150trx_active_transactions disabled 151trx_rseg_history_len disabled 152trx_undo_slots_used disabled 153trx_undo_slots_cached disabled 154trx_rseg_current_size disabled 155purge_del_mark_records disabled 156purge_upd_exist_or_extern_records disabled 157purge_invoked disabled 158purge_undo_log_pages disabled 159purge_dml_delay_usec disabled 160purge_stop_count disabled 161purge_resume_count disabled 162log_checkpoints disabled 163log_lsn_last_flush disabled 164log_lsn_last_checkpoint disabled 165log_lsn_current disabled 166log_lsn_checkpoint_age disabled 167log_lsn_buf_pool_oldest disabled 168log_max_modified_age_async disabled 169log_max_modified_age_sync disabled 170log_pending_log_flushes disabled 171log_pending_checkpoint_writes disabled 172log_num_log_io disabled 173log_waits disabled 174log_write_requests disabled 175log_writes disabled 176log_padded disabled 177compress_pages_compressed disabled 178compress_pages_decompressed disabled 179compression_pad_increments disabled 180compression_pad_decrements disabled 181compress_saved disabled 182compress_pages_page_compressed disabled 183compress_page_compressed_trim_op disabled 184compress_pages_page_decompressed disabled 185compress_pages_page_compression_error disabled 186compress_pages_encrypted disabled 187compress_pages_decrypted disabled 188index_page_splits disabled 189index_page_merge_attempts disabled 190index_page_merge_successful disabled 191index_page_reorg_attempts disabled 192index_page_reorg_successful disabled 193index_page_discards disabled 194adaptive_hash_searches disabled 195adaptive_hash_searches_btree disabled 196adaptive_hash_pages_added disabled 197adaptive_hash_pages_removed disabled 198adaptive_hash_rows_added disabled 199adaptive_hash_rows_removed disabled 200adaptive_hash_rows_deleted_no_hash_entry disabled 201adaptive_hash_rows_updated disabled 202file_num_open_files disabled 203ibuf_merges_insert disabled 204ibuf_merges_delete_mark disabled 205ibuf_merges_delete disabled 206ibuf_merges_discard_insert disabled 207ibuf_merges_discard_delete_mark disabled 208ibuf_merges_discard_delete disabled 209ibuf_merges disabled 210ibuf_size disabled 211innodb_master_thread_sleeps disabled 212innodb_activity_count disabled 213innodb_master_active_loops disabled 214innodb_master_idle_loops disabled 215innodb_background_drop_table_usec disabled 216innodb_ibuf_merge_usec disabled 217innodb_log_flush_usec disabled 218innodb_mem_validate_usec disabled 219innodb_master_purge_usec disabled 220innodb_dict_lru_usec disabled 221innodb_dict_lru_count_active disabled 222innodb_dict_lru_count_idle disabled 223innodb_checkpoint_usec disabled 224innodb_dblwr_writes disabled 225innodb_dblwr_pages_written disabled 226innodb_page_size disabled 227innodb_rwlock_s_spin_waits disabled 228innodb_rwlock_x_spin_waits disabled 229innodb_rwlock_sx_spin_waits disabled 230innodb_rwlock_s_spin_rounds disabled 231innodb_rwlock_x_spin_rounds disabled 232innodb_rwlock_sx_spin_rounds disabled 233innodb_rwlock_s_os_waits disabled 234innodb_rwlock_x_os_waits disabled 235innodb_rwlock_sx_os_waits disabled 236dml_reads disabled 237dml_inserts disabled 238dml_deletes disabled 239dml_updates disabled 240dml_system_reads disabled 241dml_system_inserts disabled 242dml_system_deletes disabled 243dml_system_updates disabled 244ddl_background_drop_indexes disabled 245ddl_background_drop_tables disabled 246ddl_online_create_index disabled 247ddl_pending_alter_table disabled 248ddl_sort_file_alter_table disabled 249ddl_log_file_alter_table disabled 250icp_attempts disabled 251icp_no_match disabled 252icp_out_of_range disabled 253icp_match disabled 254set global innodb_monitor_enable = all; 255select name from information_schema.innodb_metrics where status!='enabled'; 256name 257set global innodb_monitor_enable = aaa; 258ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of 'aaa' 259set global innodb_monitor_disable = All; 260select name from information_schema.innodb_metrics where status!='disabled'; 261name 262set global innodb_monitor_reset_all = all; 263select name from information_schema.innodb_metrics where count!=0; 264name 265set global innodb_monitor_enable = "%lock%"; 266select name from information_schema.innodb_metrics 267where status != IF(name like "%lock%", 'enabled', 'disabled'); 268name 269set global innodb_monitor_disable = "%lock%"; 270select name, status from information_schema.innodb_metrics 271where name like "%lock%"; 272name status 273lock_deadlocks disabled 274lock_timeouts disabled 275lock_rec_lock_waits disabled 276lock_table_lock_waits disabled 277lock_rec_lock_requests disabled 278lock_rec_lock_created disabled 279lock_rec_lock_removed disabled 280lock_rec_locks disabled 281lock_table_lock_created disabled 282lock_table_lock_removed disabled 283lock_table_locks disabled 284lock_row_lock_current_waits disabled 285lock_row_lock_time disabled 286lock_row_lock_time_max disabled 287lock_row_lock_waits disabled 288lock_row_lock_time_avg disabled 289innodb_rwlock_s_spin_waits disabled 290innodb_rwlock_x_spin_waits disabled 291innodb_rwlock_sx_spin_waits disabled 292innodb_rwlock_s_spin_rounds disabled 293innodb_rwlock_x_spin_rounds disabled 294innodb_rwlock_sx_spin_rounds disabled 295innodb_rwlock_s_os_waits disabled 296innodb_rwlock_x_os_waits disabled 297innodb_rwlock_sx_os_waits disabled 298set global innodb_monitor_enable = "%lock*"; 299ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '%lock*' 300set global innodb_monitor_enable="%%%%%%%%%%%%%%%%%%%%%%%%%%%"; 301select name from information_schema.innodb_metrics where status!='enabled'; 302name 303set global innodb_monitor_disable="%%%%%"; 304select name from information_schema.innodb_metrics where status!='disabled'; 305name 306set global innodb_monitor_enable="%"; 307select name from information_schema.innodb_metrics where status!='enabled'; 308name 309set global innodb_monitor_disable="%_%"; 310select name from information_schema.innodb_metrics where status!='disabled'; 311name 312set global innodb_monitor_enable="log%%%%"; 313select name from information_schema.innodb_metrics 314where status != IF(name like "log%", 'enabled', 'disabled'); 315name 316set global innodb_monitor_enable="os_%a_fs_ncs"; 317set global innodb_monitor_enable="os%pending%"; 318select name, status from information_schema.innodb_metrics 319where name like "os%"; 320name status 321os_data_reads disabled 322os_data_writes disabled 323os_data_fsyncs enabled 324os_pending_reads enabled 325os_pending_writes enabled 326os_log_bytes_written disabled 327os_log_fsyncs disabled 328os_log_pending_fsyncs enabled 329os_log_pending_writes enabled 330set global innodb_monitor_enable=""; 331ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '' 332set global innodb_monitor_enable="_"; 333ERROR 42000: Variable 'innodb_monitor_enable' can't be set to the value of '_' 334SET global innodb_monitor_disable = module_metadata; 335SET global innodb_monitor_reset_all = module_metadata; 336set global innodb_monitor_enable = metadata_table_handles_opened; 337create table monitor_test(col int) engine = innodb; 338select * from monitor_test; 339col 340select name, max_count, min_count, count, 341max_count_reset, min_count_reset, count_reset, status 342from information_schema.innodb_metrics 343where name = "metadata_table_handles_opened"; 344name max_count min_count count max_count_reset min_count_reset count_reset status 345metadata_table_handles_opened 1 NULL 1 1 NULL 1 enabled 346set global innodb_monitor_reset = metadata_table_handles_opened; 347select name, max_count, min_count, count, 348max_count_reset, min_count_reset, count_reset, status 349from information_schema.innodb_metrics 350where name = "metadata_table_handles_opened"; 351name max_count min_count count max_count_reset min_count_reset count_reset status 352metadata_table_handles_opened 1 NULL 1 NULL NULL 0 enabled 353drop table monitor_test; 354create table monitor_test(col int) engine = innodb; 355select * from monitor_test; 356col 357select name, max_count, min_count, count, 358max_count_reset, min_count_reset, count_reset, status 359from information_schema.innodb_metrics 360where name = "metadata_table_handles_opened"; 361name max_count min_count count max_count_reset min_count_reset count_reset status 362metadata_table_handles_opened 2 NULL 2 1 NULL 1 enabled 363set global innodb_monitor_reset_all = metadata_table_handles_opened; 364select name, max_count, min_count, count, 365max_count_reset, min_count_reset, count_reset, status 366from information_schema.innodb_metrics 367where name = "metadata_table_handles_opened"; 368name max_count min_count count max_count_reset min_count_reset count_reset status 369metadata_table_handles_opened 2 NULL 2 1 NULL 1 enabled 370set global innodb_monitor_disable = metadata_table_handles_opened; 371set global innodb_monitor_reset = metadata_table_handles_opened; 372select name, max_count, min_count, count, 373max_count_reset, min_count_reset, count_reset, status 374from information_schema.innodb_metrics 375where name = "metadata_table_handles_opened"; 376name max_count min_count count max_count_reset min_count_reset count_reset status 377metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled 378drop table monitor_test; 379create table monitor_test(col int) engine = innodb; 380select * from monitor_test; 381col 382select name, max_count, min_count, count, 383max_count_reset, min_count_reset, count_reset, status 384from information_schema.innodb_metrics 385where name = "metadata_table_handles_opened"; 386name max_count min_count count max_count_reset min_count_reset count_reset status 387metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled 388set global innodb_monitor_reset_all = metadata_table_handles_opened; 389select name, max_count, min_count, count, 390max_count_reset, min_count_reset, count_reset, status 391from information_schema.innodb_metrics 392where name = "metadata_table_handles_opened"; 393name max_count min_count count max_count_reset min_count_reset count_reset status 394metadata_table_handles_opened NULL NULL 0 NULL NULL 0 disabled 395set global innodb_monitor_enable = metadata_table_handles_opened; 396drop table monitor_test; 397create table monitor_test(col int) engine = innodb stats_persistent=0; 398select * from monitor_test; 399col 400select name, max_count, min_count, count, 401max_count_reset, min_count_reset, count_reset, status 402from information_schema.innodb_metrics 403where name = "metadata_table_handles_opened"; 404name max_count min_count count max_count_reset min_count_reset count_reset status 405metadata_table_handles_opened 1 NULL 1 1 NULL 1 enabled 406set global innodb_monitor_enable = metadata_table_handles_closed; 407create index idx on monitor_test(col); 408select name, max_count, min_count, count, 409max_count_reset, min_count_reset, count_reset, status 410from information_schema.innodb_metrics 411where name = "metadata_table_handles_closed"; 412name max_count min_count count max_count_reset min_count_reset count_reset status 413metadata_table_handles_closed 1 NULL 1 1 NULL 1 enabled 414select name, max_count, min_count, count, 415max_count_reset, min_count_reset, count_reset, status 416from information_schema.innodb_metrics 417where name like "metadata%"; 418name max_count min_count count max_count_reset min_count_reset count_reset status 419metadata_table_handles_opened 2 NULL 2 2 NULL 2 enabled 420metadata_table_handles_closed 1 NULL 1 1 NULL 1 enabled 421metadata_table_reference_count NULL NULL 0 NULL NULL 0 disabled 422set global innodb_monitor_disable = module_metadata; 423set global innodb_monitor_reset = module_metadata; 424select name, max_count, min_count, count, 425max_count_reset, min_count_reset, count_reset, status 426from information_schema.innodb_metrics 427where name like "metadata%"; 428name max_count min_count count max_count_reset min_count_reset count_reset status 429metadata_table_handles_opened 2 NULL 2 NULL NULL 0 disabled 430metadata_table_handles_closed 1 NULL 1 NULL NULL 0 disabled 431metadata_table_reference_count NULL NULL 0 NULL NULL 0 disabled 432set global innodb_monitor_reset_all = module_metadata; 433select name, max_count, min_count, count, 434max_count_reset, min_count_reset, count_reset, status 435from information_schema.innodb_metrics 436where name like "metadata%"; 437name max_count min_count count max_count_reset min_count_reset count_reset status 438metadata_table_handles_opened NULL NULL 0 NULL NULL 0 disabled 439metadata_table_handles_closed NULL NULL 0 NULL NULL 0 disabled 440metadata_table_reference_count NULL NULL 0 NULL NULL 0 disabled 441set global innodb_monitor_enable = module_trx; 442begin; 443insert into monitor_test values(9); 444commit; 445begin; 446insert into monitor_test values(9); 447rollback; 448select name, max_count, min_count, count, 449max_count_reset, min_count_reset, count_reset, status 450from information_schema.innodb_metrics 451where name like "trx_rollbacks" or name like "trx_active_transactions"; 452name max_count min_count count max_count_reset min_count_reset count_reset status 453trx_rollbacks 1 NULL 1 1 NULL 1 enabled 454trx_active_transactions 1 0 0 1 0 0 enabled 455set global innodb_monitor_disable = module_trx; 456set global innodb_monitor_enable = module_dml; 457insert into monitor_test values(9); 458update monitor_test set col = 10 where col = 9; 459select name, max_count, min_count, count, 460max_count_reset, min_count_reset, count_reset, status 461from information_schema.innodb_metrics 462where name like "dml%"; 463name max_count min_count count max_count_reset min_count_reset count_reset status 464dml_reads 2 NULL 2 2 NULL 2 enabled 465dml_inserts 1 NULL 1 1 NULL 1 enabled 466dml_deletes 0 NULL 0 0 NULL 0 enabled 467dml_updates 2 NULL 2 2 NULL 2 enabled 468dml_system_reads 0 NULL 0 0 NULL 0 enabled 469dml_system_inserts 0 NULL 0 0 NULL 0 enabled 470dml_system_deletes 0 NULL 0 0 NULL 0 enabled 471dml_system_updates 0 NULL 0 0 NULL 0 enabled 472delete from monitor_test; 473select name, max_count, min_count, count, 474max_count_reset, min_count_reset, count_reset, status 475from information_schema.innodb_metrics 476where name like "dml%"; 477name max_count min_count count max_count_reset min_count_reset count_reset status 478dml_reads 4 NULL 4 4 NULL 4 enabled 479dml_inserts 1 NULL 1 1 NULL 1 enabled 480dml_deletes 2 NULL 2 2 NULL 2 enabled 481dml_updates 2 NULL 2 2 NULL 2 enabled 482dml_system_reads 0 NULL 0 0 NULL 0 enabled 483dml_system_inserts 0 NULL 0 0 NULL 0 enabled 484dml_system_deletes 0 NULL 0 0 NULL 0 enabled 485dml_system_updates 0 NULL 0 0 NULL 0 enabled 486set global innodb_monitor_reset = module_dml; 487select name, max_count, min_count, count, 488max_count_reset, min_count_reset, count_reset, status 489from information_schema.innodb_metrics 490where name like "dml%"; 491name max_count min_count count max_count_reset min_count_reset count_reset status 492dml_reads 4 NULL 4 0 NULL 0 enabled 493dml_inserts 1 NULL 1 0 NULL 0 enabled 494dml_deletes 2 NULL 2 0 NULL 0 enabled 495dml_updates 2 NULL 2 0 NULL 0 enabled 496dml_system_reads 0 NULL 0 0 NULL 0 enabled 497dml_system_inserts 0 NULL 0 0 NULL 0 enabled 498dml_system_deletes 0 NULL 0 0 NULL 0 enabled 499dml_system_updates 0 NULL 0 0 NULL 0 enabled 500insert into monitor_test values(9); 501insert into monitor_test values(1); 502delete from monitor_test; 503select name, max_count, min_count, count, 504max_count_reset, min_count_reset, count_reset, status 505from information_schema.innodb_metrics 506where name like "dml%"; 507name max_count min_count count max_count_reset min_count_reset count_reset status 508dml_reads 6 NULL 6 2 NULL 2 enabled 509dml_inserts 3 NULL 3 2 NULL 2 enabled 510dml_deletes 4 NULL 4 2 NULL 2 enabled 511dml_updates 2 NULL 2 0 NULL 0 enabled 512dml_system_reads 0 NULL 0 0 NULL 0 enabled 513dml_system_inserts 0 NULL 0 0 NULL 0 enabled 514dml_system_deletes 0 NULL 0 0 NULL 0 enabled 515dml_system_updates 0 NULL 0 0 NULL 0 enabled 516set global innodb_monitor_reset_all = module_dml; 517select name, max_count, min_count, count, 518max_count_reset, min_count_reset, count_reset, status 519from information_schema.innodb_metrics 520where name like "dml%"; 521name max_count min_count count max_count_reset min_count_reset count_reset status 522dml_reads 6 NULL 6 2 NULL 2 enabled 523dml_inserts 3 NULL 3 2 NULL 2 enabled 524dml_deletes 4 NULL 4 2 NULL 2 enabled 525dml_updates 2 NULL 2 0 NULL 0 enabled 526dml_system_reads 0 NULL 0 0 NULL 0 enabled 527dml_system_inserts 0 NULL 0 0 NULL 0 enabled 528dml_system_deletes 0 NULL 0 0 NULL 0 enabled 529dml_system_updates 0 NULL 0 0 NULL 0 enabled 530set global innodb_monitor_disable = module_dml; 531select name, max_count, min_count, count, 532max_count_reset, min_count_reset, count_reset, status 533from information_schema.innodb_metrics 534where name like "dml%"; 535name max_count min_count count max_count_reset min_count_reset count_reset status 536dml_reads 6 NULL 6 2 NULL 2 disabled 537dml_inserts 3 NULL 3 2 NULL 2 disabled 538dml_deletes 4 NULL 4 2 NULL 2 disabled 539dml_updates 2 NULL 2 0 NULL 0 disabled 540dml_system_reads 0 NULL 0 0 NULL 0 disabled 541dml_system_inserts 0 NULL 0 0 NULL 0 disabled 542dml_system_deletes 0 NULL 0 0 NULL 0 disabled 543dml_system_updates 0 NULL 0 0 NULL 0 disabled 544set global innodb_monitor_reset_all = module_dml; 545select name, max_count, min_count, count, 546max_count_reset, min_count_reset, count_reset, status 547from information_schema.innodb_metrics 548where name like "dml%"; 549name max_count min_count count max_count_reset min_count_reset count_reset status 550dml_reads NULL NULL 0 NULL NULL 0 disabled 551dml_inserts NULL NULL 0 NULL NULL 0 disabled 552dml_deletes NULL NULL 0 NULL NULL 0 disabled 553dml_updates NULL NULL 0 NULL NULL 0 disabled 554dml_system_reads NULL NULL 0 NULL NULL 0 disabled 555dml_system_inserts NULL NULL 0 NULL NULL 0 disabled 556dml_system_deletes NULL NULL 0 NULL NULL 0 disabled 557dml_system_updates NULL NULL 0 NULL NULL 0 disabled 558set global innodb_monitor_enable = dml_inserts; 559insert into monitor_test values(9); 560insert into monitor_test values(1); 561delete from monitor_test; 562select name, max_count, min_count, count, 563max_count_reset, min_count_reset, count_reset, status 564from information_schema.innodb_metrics 565where name like "dml%"; 566name max_count min_count count max_count_reset min_count_reset count_reset status 567dml_reads NULL NULL 0 NULL NULL 0 disabled 568dml_inserts 2 NULL 2 2 NULL 2 enabled 569dml_deletes NULL NULL 0 NULL NULL 0 disabled 570dml_updates NULL NULL 0 NULL NULL 0 disabled 571dml_system_reads NULL NULL 0 NULL NULL 0 disabled 572dml_system_inserts NULL NULL 0 NULL NULL 0 disabled 573dml_system_deletes NULL NULL 0 NULL NULL 0 disabled 574dml_system_updates NULL NULL 0 NULL NULL 0 disabled 575set global innodb_monitor_disable = module_dml; 576drop table monitor_test; 577set global innodb_monitor_enable = file_num_open_files; 578select name, max_count, min_count, count, 579max_count_reset, min_count_reset, count_reset, status 580from information_schema.innodb_metrics 581where name like "file_num_open_files"; 582name max_count min_count count max_count_reset min_count_reset count_reset status 583file_num_open_files # # # # # # enabled 584set global innodb_monitor_disable = file_num_open_files; 585set global innodb_monitor_enable = "icp%"; 586create table monitor_test(a char(3), b int, c char(2), 587primary key (a(1), c(1)), key(b)) engine = innodb; 588insert into monitor_test values("13", 2, "aa"); 589select a from monitor_test where b < 1 for update; 590a 591select name, count from information_schema.innodb_metrics 592where name like "icp%"; 593name count 594icp_attempts 1 595icp_no_match 0 596icp_out_of_range 1 597icp_match 0 598select a from monitor_test where b < 3 for update; 599a 60013 601select name, count from information_schema.innodb_metrics 602where name like "icp%"; 603name count 604icp_attempts 2 605icp_no_match 0 606icp_out_of_range 1 607icp_match 1 608drop table monitor_test; 609set global innodb_monitor_disable = All; 610set global innodb_monitor_reset_all = all; 611select 1 from `information_schema`.`INNODB_METRICS` 612where case (1) when (1) then (AVG_COUNT_RESET) else (1) end; 6131 614set global innodb_monitor_enable = default; 615set global innodb_monitor_disable = default; 616set global innodb_monitor_reset = default; 617set global innodb_monitor_reset_all = default; 618# 619# Bug#22576241 SETTING INNODB_MONITOR_ENABLE TO ALL DOES NOT ENABLE ALL 620# MONITORS 621# 622CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; 623SELECT NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME 624LIKE 'buffer_page_written_index_leaf'; 625NAME COUNT > 0 626buffer_page_written_index_leaf 0 627SET GLOBAL innodb_monitor_enable='module_buffer_page'; 628INSERT INTO t1 VALUES (1), (2), (3), (4); 629FLUSH TABLES t1 FOR EXPORT; 630UNLOCK TABLES; 631SELECT NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME 632LIKE 'buffer_page_written_index_leaf'; 633NAME COUNT > 0 634buffer_page_written_index_leaf 1 635SET GLOBAL innodb_monitor_disable='module_buffer_page'; 636SET GLOBAL innodb_monitor_reset_all='module_buffer_page'; 637SELECT NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME 638LIKE 'buffer_page_written_index_leaf'; 639NAME COUNT > 0 640buffer_page_written_index_leaf 0 641SET GLOBAL innodb_monitor_enable='%'; 642INSERT INTO t1 VALUES (5), (6), (7), (8); 643FLUSH TABLES t1 FOR EXPORT; 644UNLOCK TABLES; 645SELECT NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME 646LIKE 'buffer_page_written_index_leaf'; 647NAME COUNT > 0 648buffer_page_written_index_leaf 1 649SET GLOBAL innodb_monitor_disable='%'; 650SET GLOBAL innodb_monitor_reset_all='%'; 651SELECT NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME 652LIKE 'buffer_page_written_index_leaf'; 653NAME COUNT > 0 654buffer_page_written_index_leaf 0 655SET GLOBAL innodb_monitor_enable='ALL'; 656INSERT INTO t1 VALUES (9), (10), (11), (12); 657FLUSH TABLES t1 FOR EXPORT; 658UNLOCK TABLES; 659SELECT NAME, COUNT > 0 FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME 660LIKE 'buffer_page_written_index_leaf'; 661NAME COUNT > 0 662buffer_page_written_index_leaf 1 663DROP TABLE t1; 664CREATE TABLE t1(id INT PRIMARY KEY, a INT, b CHAR(1), UNIQUE KEY u(a,b)) 665ENGINE=InnoDB; 666SET @start = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME 667= 'lock_rec_lock_created'); 668BEGIN; 669INSERT INTO t1 VALUES(1,1,'a'),(2,9999,'b'),(3,10000,'c'),(4,4,'d'); 670DELETE FROM t1 WHERE a = 9999 AND b='b'; 671COMMIT; 672SET @end = (SELECT COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME 673= 'lock_rec_lock_created'); 674SELECT @end - @start; 675@end - @start 6760 677DROP TABLE t1; 678SET GLOBAL innodb_monitor_enable=default; 679SET GLOBAL innodb_monitor_disable=default; 680SET GLOBAL innodb_monitor_reset_all=default; 681