mysql5.6功能监控表innodb_metrics的用法
发布时间：2022-01-15 21:18:41 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍mysql5.6性能监控表innodb_metrics的用法，在日常操作中，相信很多人在mysql5.6性能监控表innodb_metrics的用法问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答mysql5.6性能监控表innodb_metrics的用法的
|
这篇文章主要介绍“mysql5.6性能监控表innodb_metrics的用法”，在日常操作中，相信很多人在mysql5.6性能监控表innodb_metrics的用法问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”mysql5.6性能监控表innodb_metrics的用法”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ 前言 oracle有dba_hist_active_session_history,mysql自5.6也提供性能监控指标表innodb_metrics,可以定制监控多种不同监控类别；如何使用得当，对于mysql 性能评估，比如锁的占用分析，事务的吞吐量，元数据的性能，操作系统读写的性能，服务器的性能，不一而足。 操作明细 1，innodb_metrics表 mysql> desc information_schema.innodb_metrics; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | NAME | varchar(193) | NO | | | | | SUBSYSTEM | varchar(193) | NO | | | | | COUNT | bigint(21) | NO | | 0 | | | MAX_COUNT | bigint(21) | YES | | NULL | | | MIN_COUNT | bigint(21) | YES | | NULL | | | AVG_COUNT | double | YES | | NULL | | | COUNT_RESET | bigint(21) | NO | | 0 | | | MAX_COUNT_RESET | bigint(21) | YES | | NULL | | | MIN_COUNT_RESET | bigint(21) | YES | | NULL | | | AVG_COUNT_RESET | double | YES | | NULL | | | TIME_ENABLED | datetime | YES | | NULL | | | TIME_DISABLED | datetime | YES | | NULL | | | TIME_ELAPSED | bigint(21) | YES | | NULL | | | TIME_RESET | datetime | YES | | NULL | | | STATUS | varchar(193) | NO | | | | | TYPE | varchar(193) | NO | | | | | COMMENT | varchar(193) | NO | | | | +-----------------+--------------+------+-----+---------+-------+ 17 rows in set (0.04 sec) 2，表的数据，非常有价值，可用于监控分析 mysql> select * from information_schema.innodb_metrics; +------------------------------------------+---------------------+-------------+-------------+-----------+-------------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+----------+----------------+-------------------------------------------------------------------------------------------------------------------+ | NAME | SUBSYSTEM | COUNT | MAX_COUNT | MIN_COUNT | AVG_COUNT | COUNT_RESET | MAX_COUNT_RESET | MIN_COUNT_RESET | AVG_COUNT_RESET | TIME_ENABLED | TIME_DISABLED | TIME_ELAPSED | TIME_RESET | STATUS | TYPE | COMMENT | +------------------------------------------+---------------------+-------------+-------------+-----------+-------------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+----------+----------------+-------------------------------------------------------------------------------------------------------------------+ | metadata_table_handles_opened | metadata | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of table handles opened | | metadata_table_handles_closed | metadata | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of table handles closed | | metadata_table_reference_count | metadata | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Table reference counter | | metadata_mem_pool_size | metadata | 8388608 | 8388608 | 8388608 | NULL | 8388608 | 8388608 | 8388608 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | Size of a memory pool InnoDB uses to store data dictionary and internal data structures in bytes | | lock_deadlocks | lock | 0 | NULL | NULL | 0 | 0 | NULL | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | counter | Number of deadlocks | | lock_timeouts | lock | 7 | 7 | NULL | 0.00015228647261019014 | 7 | 7 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | counter | Number of lock timeouts | | lock_rec_lock_waits | lock | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of times enqueued into record lock wait queue | | lock_table_lock_waits | lock | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of times enqueued into table lock wait queue | | lock_rec_lock_requests | lock | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of record locks requested | | lock_rec_lock_created | lock | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of record locks created | | lock_rec_lock_removed | lock | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of record locks removed from the lock queue | | lock_rec_locks | lock | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Current number of record locks on tables | | lock_table_lock_created | lock | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of table locks created | | lock_table_lock_removed | lock | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of table locks removed from the lock queue | | lock_table_locks | lock | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Current number of table locks on tables | | lock_row_lock_current_waits | lock | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of row locks currently being waited for (innodb_row_lock_current_waits) | | lock_row_lock_time | lock | 356363 | 356363 | NULL | 7.75275203411217 | 356363 | 356363 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Time spent in acquiring row locks, in milliseconds (innodb_row_lock_time) | | lock_row_lock_time_max | lock | 51062 | 51062 | 51062 | NULL | 51062 | 51062 | 51062 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | The maximum time to acquire a row lock, in milliseconds (innodb_row_lock_time_max) | | lock_row_lock_waits | lock | 7 | 7 | NULL | 0.00015228647261019014 | 7 | 7 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of times a row lock had to be waited for (innodb_row_lock_waits) | | lock_row_lock_time_avg | lock | 50909 | 50909 | 50909 | NULL | 50909 | 50909 | 50909 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | The average time to acquire a row lock, in milliseconds (innodb_row_lock_time_avg) | | buffer_pool_size | server | 134217728 | 134217728 | 134217728 | NULL | 134217728 | 134217728 | 134217728 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | Server buffer pool size (all buffer pools) in bytes | | buffer_pool_reads | buffer | 56917 | 56917 | NULL | 1.238241308793456 | 56917 | 56917 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of reads directly from disk (innodb_buffer_pool_reads) | | buffer_pool_read_requests | buffer | 688876467 | 688876467 | NULL | 14986.652460514293 | 688876467 | 688876467 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of logical read requests (innodb_buffer_pool_read_requests) | | buffer_pool_write_requests | buffer | 232689742 | 232689742 | NULL | 5062.214288822173 | 232689742 | 232689742 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of write requests (innodb_buffer_pool_write_requests) | | buffer_pool_wait_free | buffer | 148 | 148 | NULL | 0.003219771135186877 | 148 | 148 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of times waited for free buffer (innodb_buffer_pool_wait_free) | | buffer_pool_read_ahead | buffer | 281470 | 281470 | NULL | 6.123439063655746 | 281470 | 281470 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of pages read as read ahead (innodb_buffer_pool_read_ahead) | | buffer_pool_read_ahead_evicted | buffer | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Read-ahead pages evicted without being accessed (innodb_buffer_pool_read_ahead_evicted) | | buffer_pool_pages_total | buffer | 8191 | 8191 | 8191 | NULL | 8191 | 8191 | 8191 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | Total buffer pool size in pages (innodb_buffer_pool_pages_total) | | buffer_pool_pages_misc | buffer | 491 | 491 | 491 | NULL | 491 | 491 | 491 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | Buffer pages for misc use such as row locks or the adaptive hash index (innodb_buffer_pool_pages_misc) | | buffer_pool_pages_data | buffer | 6676 | 6676 | 6676 | NULL | 6676 | 6676 | 6676 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | Buffer pages containing data (innodb_buffer_pool_pages_data) | | buffer_pool_bytes_data | buffer | 109379584 | 109379584 | 109379584 | NULL | 109379584 | 109379584 | 109379584 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | Buffer bytes containing data (innodb_buffer_pool_bytes_data) | | buffer_pool_pages_dirty | buffer | 0 | 0 | 0 | NULL | 0 | 0 | 0 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | Buffer pages currently dirty (innodb_buffer_pool_pages_dirty) | | buffer_pool_bytes_dirty | buffer | 0 | 0 | 0 | NULL | 0 | 0 | 0 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | Buffer bytes currently dirty (innodb_buffer_pool_bytes_dirty) | | buffer_pool_pages_free | buffer | 1024 | 1024 | 1024 | NULL | 1024 | 1024 | 1024 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | Buffer pages currently free (innodb_buffer_pool_pages_free) | | buffer_pages_created | buffer | 258153 | 258153 | NULL | 5.616172823391202 | 258153 | 258153 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of pages created (innodb_pages_created) | | buffer_pages_written | buffer | 567903 | 567903 | NULL | 12.354849236392116 | 567903 | 567903 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of pages written (innodb_pages_written) | | buffer_pages_read | buffer | 338386 | 338386 | NULL | 7.361658617238828 | 338386 | 338386 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of pages read (innodb_pages_read) | | buffer_data_reads | buffer | 5544202240 | 5544202240 | NULL | 120615.2860810164 | 5544202240 | 5544202240 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Amount of data read in bytes (innodb_data_reads) | | buffer_data_written | buffer | 28202428416 | 28202428416 | NULL | 613549.7632162903 | 28202428416 | 28202428416 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Amount of data written in bytes (innodb_data_written) | | buffer_flush_batch_scanned | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_owner | Total pages scanned as part of flush batch | | buffer_flush_batch_num_scan | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Number of times buffer flush list flush is called | | buffer_flush_batch_scanned_per_call | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Pages scanned per flush batch scan | | buffer_flush_batch_rescan | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of times rescan of flush list forced | | buffer_flush_batch_total_pages | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_owner | Total pages flushed as part of flush batch | | buffer_flush_batches | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Number of flush batches | | buffer_flush_batch_pages | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Pages queued as a flush batch | | buffer_flush_neighbor_total_pages | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_owner | Total neighbors flushed as part of neighbor flush | | buffer_flush_neighbor | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Number of times neighbors flushing is invoked | | buffer_flush_neighbor_pages | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Pages queued as a neighbor batch | | buffer_flush_n_to_flush_requested | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of pages requested for flushing. | | buffer_flush_avg_page_rate | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Average number of pages at which flushing is happening | | buffer_flush_lsn_avg_rate | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Average redo generation rate | | buffer_flush_pct_for_dirty | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Percent of IO capacity used to avoid max dirty page limit | | buffer_flush_pct_for_lsn | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Percent of IO capacity used to avoid reusable redo space limit | | buffer_flush_sync_waits | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of times a wait happens due to sync flushing | | buffer_flush_adaptive_total_pages | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_owner | Total pages flushed as part of adaptive flushing | | buffer_flush_adaptive | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Number of adaptive batches | | buffer_flush_adaptive_pages | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Pages queued as an adaptive batch | | buffer_flush_sync_total_pages | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_owner | Total pages flushed as part of sync batches | | buffer_flush_sync | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Number of sync batches | | buffer_flush_sync_pages | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Pages queued as a sync batch | | buffer_flush_background_total_pages | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_owner | Total pages flushed as part of background batches | | buffer_flush_background | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Number of background batches | | buffer_flush_background_pages | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Pages queued as a background batch | | buffer_LRU_batch_scanned | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_owner | Total pages scanned as part of LRU batch | | buffer_LRU_batch_num_scan | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Number of times LRU batch is called | | buffer_LRU_batch_scanned_per_call | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Pages scanned per LRU batch call | | buffer_LRU_batch_total_pages | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_owner | Total pages flushed as part of LRU batches | | buffer_LRU_batches | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Number of LRU batches | | buffer_LRU_batch_pages | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Pages queued as an LRU batch | | buffer_LRU_single_flush_scanned | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_owner | Total pages scanned as part of single page LRU flush | | buffer_LRU_single_flush_num_scan | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Number of times single page LRU flush is called | | buffer_LRU_single_flush_scanned_per_call | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Page scanned per single LRU flush | | buffer_LRU_single_flush_failure_count | Buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of times attempt to flush a single page from LRU failed | | buffer_LRU_get_free_search | Buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of searches performed for a clean page | | buffer_LRU_search_scanned | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_owner | Total pages scanned as part of LRU search | | buffer_LRU_search_num_scan | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Number of times LRU search is performed | | buffer_LRU_search_scanned_per_call | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Page scanned per single LRU search | | buffer_LRU_unzip_search_scanned | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_owner | Total pages scanned as part of LRU unzip search | | buffer_LRU_unzip_search_num_scan | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Number of times LRU unzip search is performed | | buffer_LRU_unzip_search_scanned_per_call | buffer | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | set_member | Page scanned per single LRU unzip search | | buffer_page_read_index_leaf | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Index Leaf Pages read | | buffer_page_read_index_non_leaf | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Index Non-leaf Pages read | | buffer_page_read_index_ibuf_leaf | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Insert Buffer Index Leaf Pages read | | buffer_page_read_index_ibuf_non_leaf | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Insert Buffer Index Non-Leaf Pages read | | buffer_page_read_undo_log | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Undo Log Pages read | | buffer_page_read_index_inode | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Index Inode Pages read | | buffer_page_read_ibuf_free_list | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Insert Buffer Free List Pages read | | buffer_page_read_ibuf_bitmap | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Insert Buffer Bitmap Pages read | | buffer_page_read_system_page | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of System Pages read | | buffer_page_read_trx_system | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Transaction System Pages read | | buffer_page_read_fsp_hdr | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of File Space Header Pages read | | buffer_page_read_xdes | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Extent Descriptor Pages read | | buffer_page_read_blob | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Uncompressed BLOB Pages read | | buffer_page_read_zblob | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of First Compressed BLOB Pages read | | buffer_page_read_zblob2 | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Subsequent Compressed BLOB Pages read | | buffer_page_read_other | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of other/unknown (old version of InnoDB) Pages read | | buffer_page_written_index_leaf | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Index Leaf Pages written | | buffer_page_written_index_non_leaf | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Index Non-leaf Pages written | | buffer_page_written_index_ibuf_leaf | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Insert Buffer Index Leaf Pages written | | buffer_page_written_index_ibuf_non_leaf | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Insert Buffer Index Non-Leaf Pages written | | buffer_page_written_undo_log | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Undo Log Pages written | | buffer_page_written_index_inode | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Index Inode Pages written | | buffer_page_written_ibuf_free_list | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Insert Buffer Free List Pages written | | buffer_page_written_ibuf_bitmap | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Insert Buffer Bitmap Pages written | | buffer_page_written_system_page | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of System Pages written | | buffer_page_written_trx_system | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Transaction System Pages written | | buffer_page_written_fsp_hdr | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of File Space Header Pages written | | buffer_page_written_xdes | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Extent Descriptor Pages written | | buffer_page_written_blob | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Uncompressed BLOB Pages written | | buffer_page_written_zblob | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of First Compressed BLOB Pages written | | buffer_page_written_zblob2 | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Subsequent Compressed BLOB Pages written | | buffer_page_written_other | buffer_page_io | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of other/unknown (old version InnoDB) Pages written | | os_data_reads | os | 338399 | 338399 | NULL | 7.3619414349736765 | 338399 | 338399 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of reads initiated (innodb_data_reads) | | os_data_writes | os | 822181 | 822181 | NULL | 17.88672061958839 | 822181 | 822181 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of writes initiated (innodb_data_writes) | | os_data_fsyncs | os | 257327 | 257327 | NULL | 5.5982030196232 | 257327 | 257327 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of fsync() calls (innodb_data_fsyncs) | | os_pending_reads | os | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of reads pending | | os_pending_writes | os | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of writes pending | | os_log_bytes_written | os | 9592861184 | 9592861184 | NULL | 208694.71313579602 | 9592861184 | 9592861184 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Bytes of log written (innodb_os_log_written) | | os_log_fsyncs | os | 224155 | 224155 | NULL | 4.876539181133881 | 224155 | 224155 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of fsync log writes (innodb_os_log_fsyncs) | | os_log_pending_fsyncs | os | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of pending fsync write (innodb_os_log_pending_fsyncs) | | os_log_pending_writes | os | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of pending log file writes (innodb_os_log_pending_writes) | | trx_rw_commits | transaction | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of read-write transactions committed | | trx_ro_commits | transaction | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of read-only transactions committed | | trx_nl_ro_commits | transaction | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of non-locking auto-commit read-only transactions committed | | trx_commits_insert_update | transaction | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of transactions committed with inserts and updates | | trx_rollbacks | transaction | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of transactions rolled back | | trx_rollbacks_savepoint | transaction | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of transactions rolled back to savepoint | | trx_rollback_active | transaction | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of resurrected active transactions rolled back | | trx_active_transactions | transaction | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of active transactions | | trx_rseg_history_len | transaction | 923 | 923 | 923 | NULL | 923 | 923 | 923 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | Length of the TRX_RSEG_HISTORY list | | trx_undo_slots_used | transaction | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of undo slots used | | trx_undo_slots_cached | transaction | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of undo slots cached | | trx_rseg_current_size | transaction | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | value | Current rollback segment size in pages | | purge_del_mark_records | purge | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of delete-marked rows purged | | purge_upd_exist_or_extern_records | purge | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of purges on updates of existing records and updates on delete marked record with externally stored field | | purge_invoked | purge | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of times purge was invoked | | purge_undo_log_pages | purge | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of undo log pages handled by the purge | | purge_dml_delay_usec | purge | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | value | Microseconds DML to be delayed due to purge lagging | | purge_stop_count | purge | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | value | Number of times purge was stopped | | purge_resume_count | purge | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | value | Number of times purge was resumed | | log_checkpoints | recovery | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of checkpoints | | log_lsn_last_flush | recovery | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | value | LSN of Last flush | | log_lsn_last_checkpoint | recovery | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | value | LSN at last checkpoint | | log_lsn_current | recovery | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | value | Current LSN value | | log_lsn_checkpoint_age | recovery | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Current LSN value minus LSN at last checkpoint | | log_lsn_buf_pool_oldest | recovery | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | value | The oldest modified block LSN in the buffer pool | | log_max_modified_age_async | recovery | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | value | Maximum LSN difference; when exceeded, start asynchronous preflush | | log_max_modified_age_sync | recovery | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | value | Maximum LSN difference; when exceeded, start synchronous preflush | | log_pending_log_writes | recovery | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Pending log writes | | log_pending_checkpoint_writes | recovery | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Pending checkpoints | | log_num_log_io | recovery | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of log I/Os | | log_waits | recovery | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of log waits due to small log buffer (innodb_log_waits) | | log_write_requests | recovery | 18571124 | 18571124 | NULL | 404.0187094809207 | 18571124 | 18571124 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of log write requests (innodb_log_write_requests) | | log_writes | recovery | 225060 | 225060 | NULL | 4.896227646521342 | 225060 | 225060 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of log writes (innodb_log_writes) | | compress_pages_compressed | compression | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of pages compressed | | compress_pages_decompressed | compression | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of pages decompressed | | compression_pad_increments | compression | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of times padding is incremented to avoid compression failures | | compression_pad_decrements | compression | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of times padding is decremented due to good compressibility | | index_page_splits | index | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of index page splits | | index_page_merge_attempts | index | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of index page merge attempts | | index_page_merge_successful | index | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of successful index page merges | | index_page_reorg_attempts | index | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of index page reorganization attempts | | index_page_reorg_successful | index | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of successful index page reorganizations | | index_page_discards | index | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of index pages discarded | | adaptive_hash_searches | adaptive_hash_index | 48920480 | 48920480 | NULL | 1064.2753339424792 | 48920480 | 48920480 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of successful searches using Adaptive Hash Index | | adaptive_hash_searches_btree | adaptive_hash_index | 2550311 | 2550311 | NULL | 55.48255232128095 | 2550311 | 2550311 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of searches using B-tree on an index search | | adaptive_hash_pages_added | adaptive_hash_index | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of index pages on which the Adaptive Hash Index is built | | adaptive_hash_pages_removed | adaptive_hash_index | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of index pages whose corresponding Adaptive Hash Index entries were removed | | adaptive_hash_rows_added | adaptive_hash_index | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Adaptive Hash Index rows added | | adaptive_hash_rows_removed | adaptive_hash_index | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Adaptive Hash Index rows removed | | adaptive_hash_rows_deleted_no_hash_entry | adaptive_hash_index | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of rows deleted that did not have corresponding Adaptive Hash Index entries | | adaptive_hash_rows_updated | adaptive_hash_index | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of Adaptive Hash Index rows updated | | file_num_open_files | file_system | 6 | 6 | 6 | NULL | 6 | 6 | 6 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | Number of files currently open (innodb_num_open_files) | | ibuf_merges_insert | change_buffer | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of inserted records merged by change buffering | | ibuf_merges_delete_mark | change_buffer | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of deleted records merged by change buffering | | ibuf_merges_delete | change_buffer | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of purge records merged by change buffering | | ibuf_merges_discard_insert | change_buffer | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of insert merged operations discarded | | ibuf_merges_discard_delete_mark | change_buffer | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of deleted merged operations discarded | | ibuf_merges_discard_delete | change_buffer | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of purge merged operations discarded | | ibuf_merges | change_buffer | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of change buffer merges | | ibuf_size | change_buffer | 1 | 1 | NULL | 0.000021755210372884305 | 1 | 1 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Change buffer size in pages | | innodb_master_thread_sleeps | server | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of times (seconds) master thread sleeps | | innodb_activity_count | server | 29366418 | 29366418 | NULL | 638.8726014880564 | 29366418 | 29366418 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Current server activity count | | innodb_master_active_loops | server | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of times master thread performs its tasks when server is active | | innodb_master_idle_loops | server | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of times master thread performs its tasks when server is idle | | innodb_background_drop_table_usec | server | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Time (in microseconds) spent to process drop table list | | innodb_ibuf_merge_usec | server | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Time (in microseconds) spent to process change buffer merge | | innodb_log_flush_usec | server | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Time (in microseconds) spent to flush log records | | innodb_mem_validate_usec | server | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Time (in microseconds) spent to do memory validation | | innodb_master_purge_usec | server | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Time (in microseconds) spent by master thread to purge records | | innodb_dict_lru_usec | server | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Time (in microseconds) spent to process DICT LRU list | | innodb_checkpoint_usec | server | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Time (in microseconds) spent by master thread to do checkpoint | | innodb_dblwr_writes | server | 8627 | 8627 | NULL | 0.1876821998868729 | 8627 | 8627 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of doublewrite operations that have been performed (innodb_dblwr_writes) | | innodb_dblwr_pages_written | server | 567903 | 567903 | NULL | 12.354849236392116 | 567903 | 567903 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of pages that have been written for doublewrite operations (innodb_dblwr_pages_written) | | innodb_page_size | server | 16384 | 16384 | 16384 | NULL | 16384 | 16384 | 16384 | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | value | InnoDB page size in bytes (innodb_page_size) | | innodb_rwlock_s_spin_waits | server | 287836 | 287836 | NULL | 6.261932732889527 | 287836 | 287836 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of rwlock spin waits due to shared latch request | | innodb_rwlock_x_spin_waits | server | 32494 | 32494 | NULL | 0.7069138058565027 | 32494 | 32494 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of rwlock spin waits due to exclusive latch request | | innodb_rwlock_s_spin_rounds | server | 9534135 | 9534135 | NULL | 207.4171126484793 | 9534135 | 9534135 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of rwlock spin loop rounds due to shared latch request | | innodb_rwlock_x_spin_rounds | server | 1799257 | 1799257 | NULL | 39.143214549884696 | 1799257 | 1799257 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of rwlock spin loop rounds due to exclusive latch request | | innodb_rwlock_s_os_waits | server | 294666 | 294666 | NULL | 6.410520819736327 | 294666 | 294666 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of OS waits due to shared latch request | | innodb_rwlock_x_os_waits | server | 57256 | 57256 | NULL | 1.2456163251098638 | 57256 | 57256 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of OS waits due to exclusive latch request | | dml_reads | dml | 346466423 | 346466423 | NULL | 7537.4499195057215 | 346466423 | 346466423 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of rows read | | dml_inserts | dml | 26065444 | 26065444 | NULL | 567.059217682635 | 26065444 | 26065444 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of rows inserted | | dml_deletes | dml | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of rows deleted | | dml_updates | dml | 75506799 | 75506799 | NULL | 1642.6662968280903 | 75506799 | 75506799 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 45966 | NULL | enabled | status_counter | Number of rows updated | | ddl_background_drop_indexes | ddl | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of indexes waiting to be dropped after failed index creation | | ddl_background_drop_tables | ddl | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of tables in background drop table list | | ddl_online_create_index | ddl | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of indexes being created online | | ddl_pending_alter_table | ddl | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of ALTER TABLE, CREATE INDEX, DROP INDEX in progress | | icp_attempts | icp | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Number of attempts for index push-down condition checks | | icp_no_match | icp | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Index push-down condition does not match | | icp_out_of_range | icp | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Index push-down condition out of range | | icp_match | icp | 0 | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | disabled | counter | Index push-down condition matches | +------------------------------------------+---------------------+-------------+-------------+-----------+-------------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+----------+----------------+-------------------------------------------------------------------------------------------------------------------+ 214 rows in set (0.00 sec) mysql> 3，表的数据分为几个大类： buffer buffer_page_io server lock recovery transaction os adaptive_hash_index change_buffer purge index dml ddl compression metadata icp 不同的大类，又包括具体的监控指标，非常有价值 mysql> select subsystem,count(*) from information_schema.innodb_metrics group by subsystem order by 2 desc; +---------------------+----------+ | subsystem | count(*) | +---------------------+----------+ | buffer | 60 | | buffer_page_io | 32 | | server | 21 | | lock | 16 | | recovery | 14 | | transaction | 12 | | os | 9 | | adaptive_hash_index | 8 | | change_buffer | 8 | | purge | 7 | | index | 6 | | dml | 4 | | ddl | 4 | | compression | 4 | | metadata | 4 | | icp | 4 | | file_system | 1 | +---------------------+----------+ 17 rows in set (0.01 sec) 4， 压测前 mysql> select * from information_schema.innodb_metrics where subsystem='dml'; +-------------+-----------+-----------+-----------+-----------+-------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+ | NAME | SUBSYSTEM | COUNT | MAX_COUNT | MIN_COUNT | AVG_COUNT | COUNT_RESET | MAX_COUNT_RESET | MIN_COUNT_RESET | AVG_COUNT_RESET | TIME_ENABLED | TIME_DISABLED | TIME_ELAPSED | TIME_RESET | STATUS | TYPE | COMMENT | +-------------+-----------+-----------+-----------+-----------+-------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+ | dml_reads | dml | 346466423 | 346466423 | NULL | 7484.045945478896 | 346466423 | 346466423 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 46294 | NULL | enabled | status_counter | Number of rows read | | dml_inserts | dml | 26065444 | 26065444 | NULL | 563.041517259256 | 26065444 | 26065444 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 46294 | NULL | enabled | status_counter | Number of rows inserted | | dml_deletes | dml | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 46294 | NULL | enabled | status_counter | Number of rows deleted | | dml_updates | dml | 75506799 | 75506799 | NULL | 1631.027757376766 | 75506799 | 75506799 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 46294 | NULL | enabled | status_counter | Number of rows updated | +-------------+-----------+-----------+-----------+-----------+-------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+ 4 rows in set (0.00 sec) mysql> select * from information_schema.innodb_metrics where subsystem='dml'; +-------------+-----------+-----------+-----------+-----------+--------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+ | NAME | SUBSYSTEM | COUNT | MAX_COUNT | MIN_COUNT | AVG_COUNT | COUNT_RESET | MAX_COUNT_RESET | MIN_COUNT_RESET | AVG_COUNT_RESET | TIME_ENABLED | TIME_DISABLED | TIME_ELAPSED | TIME_RESET | STATUS | TYPE | COMMENT | +-------------+-----------+-----------+-----------+-----------+--------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+ | dml_reads | dml | 346466423 | 346466423 | NULL | 7482.429660504492 | 346466423 | 346466423 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 46304 | NULL | enabled | status_counter | Number of rows read | | dml_inserts | dml | 26065444 | 26065444 | NULL | 562.9199205252246 | 26065444 | 26065444 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 46304 | NULL | enabled | status_counter | Number of rows inserted | | dml_deletes | dml | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 46304 | NULL | enabled | status_counter | Number of rows deleted | | dml_updates | dml | 75506799 | 75506799 | NULL | 1630.6755139944714 | 75506799 | 75506799 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 46304 | NULL | enabled | status_counter | Number of rows updated | +-------------+-----------+-----------+-----------+-----------+--------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+----------- 压测 [root@standbygtid ~]# mysqlslap -uroot --auto-generate-sql --concurrency=80 --iterations=100 -psystem Warning: Using a password on the command line interface can be insecure. 可清晰看到DML不同操作类型的变化 mysql> select * from information_schema.innodb_metrics where subsystem='dml'; +-------------+-----------+-----------+-----------+-----------+--------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+ | NAME | SUBSYSTEM | COUNT | MAX_COUNT | MIN_COUNT | AVG_COUNT | COUNT_RESET | MAX_COUNT_RESET | MIN_COUNT_RESET | AVG_COUNT_RESET | TIME_ENABLED | TIME_DISABLED | TIME_ELAPSED | TIME_RESET | STATUS | TYPE | COMMENT | +-------------+-----------+-----------+-----------+-----------+--------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+ | dml_reads | dml | 347750604 | 347750604 | NULL | 7503.3574418504295 | 347750604 | 347750604 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 46346 | NULL | enabled | status_counter | Number of rows read | | dml_inserts | dml | 26070656 | 26070656 | NULL | 562.5222457169982 | 26070656 | 26070656 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 46346 | NULL | enabled | status_counter | Number of rows inserted | | dml_deletes | dml | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 46346 | NULL | enabled | status_counter | Number of rows deleted | | dml_updates | dml | 75506799 | 75506799 | NULL | 1629.1977516937816 | 75506799 | 75506799 | NULL | NULL | 2019-11-05 19:23:01 | NULL | 46346 | NULL | enabled | status_counter | Number of rows updated | +-------------+-----------+-----------+-----------+-----------+--------------------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+ 4 rows in set (0.00 sec) 7，重启mysql 8, 可见重启后不会持久化保存这些数据 mysql> select * from information_schema.innodb_metrics where subsystem='dml'; +-------------+-----------+-------+-----------+-----------+-----------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+ | NAME | SUBSYSTEM | COUNT | MAX_COUNT | MIN_COUNT | AVG_COUNT | COUNT_RESET | MAX_COUNT_RESET | MIN_COUNT_RESET | AVG_COUNT_RESET | TIME_ENABLED | TIME_DISABLED | TIME_ELAPSED | TIME_RESET | STATUS | TYPE | COMMENT | +-------------+-----------+-------+-----------+-----------+-----------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+ | dml_reads | dml | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-06 08:34:23 | NULL | 29 | NULL | enabled | status_counter | Number of rows read | | dml_inserts | dml | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-06 08:34:23 | NULL | 29 | NULL | enabled | status_counter | Number of rows inserted | | dml_deletes | dml | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-06 08:34:23 | NULL | 29 | NULL | enabled | status_counter | Number of rows deleted | | dml_updates | dml | 0 | 0 | NULL | 0 | 0 | 0 | NULL | NULL | 2019-11-06 08:34:23 | NULL | 29 | NULL | enabled | status_counter | Number of rows updated | +-------------+-----------+-------+-----------+-----------+-----------+-------------+-----------------+-----------------+-----------------+---------------------+---------------+--------------+------------+---------+----------------+-------------------------+ 4 rows in set (0.00 sec) 9，由下可知，innodb_metrics是内存表 mysql> show create table information_schema.innodb_metricsG; *************************** 1. row *************************** Table: INNODB_METRICS Create Table: CREATE TEMPORARY TABLE `INNODB_METRICS` ( `NAME` varchar(193) NOT NULL DEFAULT '', `SUBSYSTEM` varchar(193) NOT NULL DEFAULT '', `COUNT` bigint(21) NOT NULL DEFAULT '0', `MAX_COUNT` bigint(21) DEFAULT NULL, `MIN_COUNT` bigint(21) DEFAULT NULL, `AVG_COUNT` double DEFAULT NULL, `COUNT_RESET` bigint(21) NOT NULL DEFAULT '0', `MAX_COUNT_RESET` bigint(21) DEFAULT NULL, `MIN_COUNT_RESET` bigint(21) DEFAULT NULL, `AVG_COUNT_RESET` double DEFAULT NULL, `TIME_ENABLED` datetime DEFAULT NULL, `TIME_DISABLED` datetime DEFAULT NULL, `TIME_ELAPSED` bigint(21) DEFAULT NULL, `TIME_RESET` datetime DEFAULT NULL, `STATUS` varchar(193) NOT NULL DEFAULT '', `TYPE` varchar(193) NOT NULL DEFAULT '', `COMMENT` varchar(193) NOT NULL DEFAULT '' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified 10，所以需要对此表的数据进行持久化保存，便于重启数据库前后的性能纵向对比 到此，关于“mysql5.6性能监控表innodb_metrics的用法”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！ （编辑：应用网_丽江站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330468号