WL#5776: Add additional Metrics Counters to InnoDB metrics table

Status: Complete   —   Priority: Medium

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)