WL#5776: Add additional Metrics Counters to InnoDB metrics table
Status: Complete
This is a placeholder worklog that would add more metrics counter to the InnoDB storage engine. For original metrics counter design, please refer to worklog #5316 (InnoDB Information Schema Metrics Table) We already have about 111 metrics counters in the 5.6 server that covers a variety of modules. We will continue to go over those counters and see if they are appropriate and any additional counters would fit in the module. In addition, we will go over any existing customer requests on additional new counters to see if they would be added as metrics counters. We added a few new features in the 5.5 and 5.6 releases, these new features also require a review to see any valuable counters should be exposed to users and DBAs. There are also some requests from users in this area, such as one in BUG#59429. We would address those as metrics counters, and expose some non resettable values through status variables. In the worklog, we have made following updates: 1) Added additional 5 new monitor modules and 60+ counters. The total number of metrics counters now goes over 170. 5 modules added are: MONITOR_MODULE_PURGE MONITOR_MODULE_ADAPTIVE_HASH MONITOR_MODULE_IBUF_SYSTEM MONITOR_MODULE_SERVER MONITOR_MODULE_DDLSTATS 2) Simplify the monitor counter control system variable names, removed "_count" postfix, and made "innodb_monitor_" a prefix: innodb_enable_monitor_counter -> innodb_monitor_enable innodb_disable_monitor_counter -> innodb_monitor_disable innodb_reset_monitor_counter -> innodb_monitor_reset innodb_reset_all_monitor_counter -> innodb_monitor_reset_all To turn on the counter: mysql> set global innodb_monitor_enable = counter_name; To turn off the counter: mysql> set global innodb_monitor_disable = counter_name; To reset the counter: mysql> set global innodb_monitor_reset = counter_name; To reset all the values: mysql> set global innodb_monitor_reset_all = counter_name; Please note, we support wildcard match for the counter name, user has to include at least one wildcard match character "%"in the counter_name string. To turn on monitor counters through server configure file. Please use "loose-innodb_monitor_enable" option to specify the monitor counters/groups you want to turn on by default at server start up. The count name can be separated by ";", "," or space: So please refer to worklog 5316 for examples. In addition, we simplified following INNODB_METRICS table column name: count_since_reset -> count_reset max_count_since_reset -> max_count_reset min_count_since_reset -> min_count_reset avg_count_since_reset -> avg_count_reset We also added a "time_elapsed" column to record the time(in seconds) elapsed since monitor server turned on. 3) Counters with existing show innodb status variable counters are now turned on by default. This is to make it easier for users to transit from show innodb status to metrics table. A new monitor type "MONITOR_DEFAULT_ON" is added. When a monitor counter is marked as "MONITOR_DEFAULT_ON", the monitor will be turned on at server startup. These counters can still be turned off by using "innodb_disable_monitor_count". About 40 counters are related to existing "show status" status variables. Almost all current "show status" InnoDB related status variables have their corresponding metrics counters, for example: "innodb_row_lock_time_max" has its corresponding metrics counter named as "lock_row_lock_time_max". Our principle of naming these counters is to substitute the "innodb_" prefix in the status variable with our metrics counter module name, but keep the remaining to be the same. In above case, "innodb_" is substituted by the module name "lock_". And we will also put the status variable name in the metrics counter comments with parenthesis: {"lock_row_lock_time_max", "lock", "The maximum time to acquire a row lock, in milliseconds" " (innodb_row_lock_time_max)", MONITOR_EXISTING | MONITOR_DISPLAY_CURRENT | MONITOR_DEFAULT_ON, 0, MONITOR_OVLD_LOCK_MAX_WAIT_TIME}, 4) A new macro "MONITOR_INC_TIME" is added for any time based counter. A direct use is for counters such as "srv_background_drop_table_microseconds", which when turned on, count the time spent on background drop table activity. 5) Added a new concept called monitor set, which associate a set of monitors together. The "owner" monitor is marked as "MONITOR_SET_OWNER". We only check the on/off status of monitor owner for monitor set on/off. Other monitors are marked "MONITOR_SET_MEMBER". And "MONITOR_SET_OWNER" could be averaged by a "MONITOR_SET_MEMBER" if specified member name in owner's define: For example, in our case, following three counters are a set: buffer_flush_batch_scanned buffer_flush_batch_num_scan buffer_flush_batch_scanned_per_call And buffer_flush_batch_scanned is the set owner is averaged by buffer_flush_batch_num_scan. 6) A new monitor type flag "MONITOR_HIDDEN" is added if developer does not want a counter to show up in the innodb_metrics table for public use. So far, there is no monitor set with this flag. Following is a total list of monitor counters so far in the InnoDB metrics table: +-----------------------------------------+ | name | +-----------------------------------------+ | metadata_table_handles_opened | | metadata_table_handles_closed | | metadata_table_reference_count | | metadata_mem_pool_size | | lock_deadlocks | | lock_timeouts | | lock_rec_lock_waits | | lock_table_lock_waits | | lock_rec_lock_requests | | lock_rec_lock_created | | lock_rec_lock_removed | | lock_rec_locks | | lock_table_lock_created | | lock_table_lock_removed | | lock_table_locks | | lock_row_lock_current_waits | | lock_row_lock_time | | lock_row_lock_time_max | | lock_row_lock_waits | | lock_row_lock_time_avg | | buffer_pool_size | | buffer_pool_reads | | buffer_pool_read_requests | | buffer_pool_write_requests | | buffer_pool_pages_in_flush | | buffer_pool_wait_free | | buffer_pool_read_ahead | | buffer_pool_read_ahead_evicted | | buffer_pool_pages_total | | buffer_pool_pages_misc | | buffer_pool_pages_data | | buffer_pool_pages_dirty | | buffer_pool_pages_free | | buffer_pages_created | | buffer_pages_written | | buffer_pages_read | | buffer_data_reads | | buffer_data_written | | buffer_flush_adaptive_flushes | | buffer_flush_adaptive_pages | | buffer_flush_async_flushes | | buffer_flush_async_pages | | buffer_flush_sync_flushes | | buffer_flush_sync_pages | | buffer_flush_max_dirty_flushes | | buffer_flush_max_dirty_pages | | buffer_flush_free_margin_flushes | | buffer_flush_free_margin_pages | | buffer_flush_io_capacity_pct | | buffer_flush_batch_scanned | | buffer_flush_batch_num_scan | | buffer_flush_batch_scanned_per_call | | buffer_flush_batch_total_pages | | buffer_flush_batches | | buffer_flush_batch_pages | | buffer_flush_by_lru | | buffer_flush_by_list | | buffer_page_read_index_leaf | | buffer_page_read_index_non_leaf | | buffer_page_read_index_ibuf_leaf | | buffer_page_read_index_ibuf_non_leaf | | buffer_page_read_undo_log | | buffer_page_read_index_inode | | buffer_page_read_ibuf_free_list | | buffer_page_read_ibuf_bitmap | | buffer_page_read_system_page | | buffer_page_read_trx_system | | buffer_page_read_fsp_hdr | | buffer_page_read_xdes | | buffer_page_read_blob | | buffer_page_read_zblob | | buffer_page_read_zblob2 | | buffer_page_read_other | | buffer_page_written_index_leaf | | buffer_page_written_index_non_leaf | | buffer_page_written_index_ibuf_leaf | | buffer_page_written_index_ibuf_non_leaf | | buffer_page_written_undo_log | | buffer_page_written_index_inode | | buffer_page_written_ibuf_free_list | | buffer_page_written_ibuf_bitmap | | buffer_page_written_system_page | | buffer_page_written_trx_system | | buffer_page_written_fsp_hdr | | buffer_page_written_xdes | | buffer_page_written_blob | | buffer_page_written_zblob | | buffer_page_written_zblob2 | | buffer_page_written_other | | os_data_reads | | os_data_writes | | os_data_fsyncs | | os_pending_reads | | os_pending_writes | | os_log_bytes_written | | os_log_fsyncs | | os_log_pending_fsyncs | | os_log_pending_writes | | trx_commits | | trx_commits_insert_update | | trx_rollbacks | | trx_rollbacks_savepoint | | trx_rollback_active | | trx_active_transactions | | trx_rseg_history_len | | trx_undo_slots_used | | trx_undo_slots_cached | | trx_rseg_curent_size | | purge_del_mark_records | | purge_upd_exist_or_extern_records | | purge_undo_log_pages | | purge_dml_delay_usec | | log_checkpoints | | log_lsn_last_flush | | log_lsn_last_checkpoint | | log_lsn_current | | log_lsn_checkpoint_age | | log_lsn_buf_pool_oldest | | log_max_modified_age_async | | log_max_modified_age_sync | | log_pending_log_writes | | log_pending_checkpoint_writes | | log_num_log_io | | log_waits | | log_write_requests | | log_writes | | compress_pages_compressed | | compress_pages_decompressed | | index_splits | | index_merges | | adaptive_hash_searches | | adaptive_hash_searches_btree | | adaptive_hash_pages_added | | adaptive_hash_pages_removed | | adaptive_hash_rows_added | | adaptive_hash_rows_removed | | adaptive_hash_rows_remove_not_found | | adaptive_hash_rows_updated | | file_num_open_files | | ibuf_merges_insert | | ibuf_merges_delete_mark | | ibuf_merges_delete | | ibuf_merges_discard_insert | | ibuf_merges_discard_delete_mark | | ibuf_merges_discard_delete | | ibuf_merges | | ibuf_size | | innodb_master_thread_sleeps | | innodb_activity_count | | innodb_master_active_loops | | innodb_master_idle_loops | | innodb_background_drop_table_usec | | innodb_ibuf_merge_usec | | innodb_log_flush_usec | | innodb_mem_validate_usec | | innodb_master_purge_usec | | innodb_dict_lru_usec | | innodb_checkpoint_usec | | innodb_dblwr_writes | | innodb_dblwr_pages_written | | innodb_page_size | | innodb_rwlock_s_spin_waits | | innodb_rwlock_x_spin_waits | | innodb_rwlock_s_spin_rounds | | innodb_rwlock_x_spin_rounds | | innodb_rwlock_s_os_waits | | innodb_rwlock_x_os_waits | | dml_reads | | dml_inserts | | dml_deletes | | dml_updates | | ddl_background_drop_tables | +-----------------------------------------+ 172 rows in set (0.00 sec)
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.