mysql 5.6效能監控表innodb_metrics

wisdomone1發表於2019-11-07

前言
   oracle有dba_hist_active_session_history,mysql自5.6也提供效能監控指標表innodb_metrics,可以定製監控多種不同監控類別；如何使用得當，對於mysql
 效能評估，比如鎖的佔用分析，事務的吞吐量，後設資料的效能，作業系統讀寫的效能，伺服器的效能，不一而足。

操作明細
```scala
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 |

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-2663165/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

[MySQL5.6]Innodb新的監控表INNODB_METRICS
2016-05-10
MySql
mysql效能監控相關
2019-06-13
MySql
mysql效能監控指標
2012-06-01
MySql指標
Mysql效能監控視覺化
2021-01-10
MySql視覺化
MySQL 效能監控工具--mysqlreport
2010-12-20
MySql
MYSQL和SQLServer效能監控指標
2018-05-14
MySqlServer指標
zabbix企業應用之監控mysql5.6版本
2017-11-15
MySql
MySQL調優效能監控之show profile
2021-11-09
MySql
MySQL調優效能監控之performance schema
2021-11-09
MySqlORM
巧用Zabbix自定義監控Mysql效能狀態
2020-09-21
MySql
前端效能監控
2019-04-03
前端
php效能監控
2022-02-17
PHP
MySQL 5.6大查詢和大事務監控指令碼(Python 2)
2018-09-30
MySql指令碼Python
mytop安裝，使用mytop監控MySQL效能
2014-05-16
MySql
分享Mysql效能監控實踐報告的ppt~~~
2011-03-17
MySql
MySQL 5.6的表壓縮
2020-01-10
MySql
Performance --- 前端效能監控
2019-06-05
ORM前端
效能監控工具YourKit
2014-10-24
JVM 效能監控工具
2015-06-13
JVM
CentOS效能監控工具
2012-03-24
CentOS
MySQL監控工具
2019-07-30
MySql
MySQL監控--zabbix
2017-07-31
MySql
cacti監控mysql
2014-05-23
MySql
APM效能監控軟體的監控型別服務及監控流程
2022-06-08
型別
監控寶SQL Server效能監控的功能和配置
2014-03-14
SQLServer
iOS網路效能監控
2018-12-26
iOS
linux效能監控命令
2017-11-20
Linux
Chrome DevTools - 效能監控
2017-12-18
Chromedev
效能監控命令總結
2015-03-18
資料庫效能監控
2011-05-25
資料庫
Linux 效能監控工具
2024-05-03
Linux
MySQL監控利器-Innotop
2016-01-06
MySql
MySQL監控工具-orztop
2015-12-21
MySql
MySQL監控工具-orzdba
2015-12-21
MySql
mysql監控指令碼
2015-05-11
MySql指令碼
Mysql 監控系統
2009-02-05
MySql
Mysql監控快速指南
2012-05-26
MySql
mysql 的一個監控指令碼，監控heartbeat
2010-10-11
MySql指令碼