MySQL监视——sys Schema

MySQL的Performance Schema是一个非常好的监视工具，但是里面包含过多的表和探测项，对于普通的用户来说过于复杂，想弄清楚每一项的监测内容很困难，因此，MySQL提供了一套sys Schema，用于帮助DBA在典型的优化和诊断场景上快速使用Performance Schema。

sys Schema包含视图、存储过程和存储函数。视图中对Performance Schema的数据进行汇总，并使用易于理解的格式进行展现。存储过程帮助DBA配置Performance Schema并生成诊断报告。存储函数用于查询Performance Schema的配置，并采用格式化的输出。

sys Schema的使用非常简单。例如，DBA可以通过sys Schema查询哪个用户使用服务器的资源最多：

MySQL localhost:3306 ssl SQL > use sys
Default schema set to sys.
Fetching table and column names from sys for auto-completion… Press ^C to stop.

 MySQL  localhost:3306 ssl  sys  SQL > SHOW TABLES LIKE 'user%';
+-----------------------------------+
| Tables_in_sys (user%)             |
+-----------------------------------+
| user_summary                      |
| user_summary_by_file_io           |
| user_summary_by_file_io_type      |
| user_summary_by_stages            |
| user_summary_by_statement_latency |
| user_summary_by_statement_type    |
+-----------------------------------+
复制

6 rows in set (0.0013 sec)
“user_summary”视图中可以查看root用户的I/O事件等信息。

MySQL  localhost:3306 ssl  sys  SQL > SELECT * FROM user_summary\G
*************************** 1. row ***************************
                 user: root
           statements: 264
    statement_latency: 358.46 ms
statement_avg_latency: 1.36 ms
          table_scans: 20
             file_ios: 96
      file_io_latency: 43.13 ms
  current_connections: 1
    total_connections: 8
         unique_hosts: 1
       current_memory: 3.10 MiB
total_memory_allocated: 133.92 MiB
*************************** 2. row ***************************
                 user: background
           statements: 0
    statement_latency:   0 ps
statement_avg_latency:   0 ps
          table_scans: 0
             file_ios: 1707
      file_io_latency: 508.48 ms
  current_connections: 37
    total_connections: 53
         unique_hosts: 0
       current_memory: 1.89 MiB
total_memory_allocated: 156.13 MiB
*************************** 3. row ***************************
                 user: event_scheduler
           statements: 0
    statement_latency:   0 ps
statement_avg_latency:   0 ps
          table_scans: 0
             file_ios: 0
      file_io_latency:   0 ps
  current_connections: 1
    total_connections: 1
         unique_hosts: 1
       current_memory: 30.81 KiB
total_memory_allocated: 38.07 KiB
3 rows in set (0.0054 sec)
复制

如果DBA想知道哪个语句引起的延迟过大，可以执行下面的语句进行查看：

MySQL > SELECT * FROM user_summary_by_statement_type WHERE user = 'root'\G
*************************** 1. row ***************************
         user: root
    statement: show_fields
        total: 211
total_latency: 201.02 ms
  max_latency: 4.36 ms
 lock_latency: 599.00 us
  cpu_latency:   0 ps
    rows_sent: 2445
rows_examined: 10415
rows_affected: 0
   full_scans: 0
*************************** 2. row ***************************
         user: root
    statement: show_tables
        total: 4
total_latency: 63.04 ms
  max_latency: 33.95 ms
 lock_latency: 22.00 us
  cpu_latency:   0 ps
    rows_sent: 222
rows_examined: 768
rows_affected: 0
   full_scans: 0
*************************** 3. row ***************************
         user: root
    statement: select
        total: 28
total_latency: 58.42 ms
  max_latency: 32.30 ms
 lock_latency: 79.00 us
  cpu_latency:   0 ps
    rows_sent: 686
rows_examined: 686
rows_affected: 0
   full_scans: 7
*************************** 4. row ***************************
         user: root
    statement: show_status
        total: 7
total_latency: 24.75 ms
  max_latency: 17.55 ms
 lock_latency: 18.00 us
  cpu_latency:   0 ps
    rows_sent: 3344
rows_examined: 3344
rows_affected: 0
   full_scans: 7
*************************** 5. row ***************************
         user: root
    statement: show_databases
        total: 7
total_latency: 15.55 ms
  max_latency: 7.20 ms
 lock_latency: 26.00 us
  cpu_latency:   0 ps
    rows_sent: 35
rows_examined: 147
rows_affected: 0
   full_scans: 7
*************************** 6. row ***************************
         user: root
    statement: change_db
        total: 2
total_latency: 533.80 us
  max_latency: 321.20 us
 lock_latency: 2.00 us
  cpu_latency:   0 ps
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0
*************************** 7. row ***************************
         user: root
    statement: show_processlist
        total: 1
total_latency: 113.70 us
  max_latency: 113.70 us
 lock_latency:   0 ps
  cpu_latency:   0 ps
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0
*************************** 8. row ***************************
         user: root
    statement: Quit
        total: 4
total_latency: 50.00 us
  max_latency: 18.30 us
 lock_latency:   0 ps
  cpu_latency:   0 ps
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0
*************************** 9. row ***************************
         user: root
    statement: Statistics
        total: 1
total_latency: 47.40 us
  max_latency: 47.40 us
 lock_latency:   0 ps
  cpu_latency:   0 ps
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0
9 rows in set (0.0178 sec)
复制

sys Schema中包含两种类型视图，一种视图的名称前面带有“x”前缀，另外一种则不带“x”，区别在于，不带“x”视图输出的格式友好，便于人类阅读，带有“x”的输出则是原始数据，便于通过程序和工具处理。例如，DBA需要查看线程的延迟，可以执行如下查询：

 MySQL > SELECT * FROM io_by_thread_by_latency;
+-------------------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| user                          | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+-------------------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| root@localhost                |   140 | 60.98 ms      | 10.71 us    | 496.12 us   | 14.91 ms    |        61 |             21 |
| page_flush_coordinator_thread |   234 | 59.80 ms      | 5.43 us     | 1.32 ms     | 15.06 ms    |        14 |           NULL |
| srv_purge_thread              |   305 | 36.83 ms      | 32.32 us    | 120.76 us   | 14.70 ms    |        39 |           NULL |
| main                          |   149 | 22.81 ms      | 154.56 ns   | 157.96 us   | 1.52 ms     |         1 |           NULL |
| log_flusher_thread            |    14 | 11.26 ms      | 39.70 us    | 804.19 us   | 5.09 ms     |        17 |           NULL |
| buf_dump_thread               |    76 | 10.47 ms      | 36.56 us    | 137.82 us   | 732.52 us   |        37 |           NULL |
| io_write_thread               |     7 | 7.48 ms       | 186.99 us   | 1.07 ms     | 2.14 ms     |        10 |           NULL |
| io_write_thread               |     5 | 5.32 ms       | 418.72 us   | 1.06 ms     | 1.96 ms     |        13 |           NULL |
| io_write_thread               |     2 | 2.57 ms       | 390.17 us   | 1.29 ms     | 2.18 ms     |        11 |           NULL |
| io_write_thread               |     1 | 1.55 ms       | 1.55 ms     | 1.55 ms     | 1.55 ms     |        12 |           NULL |
| clone_gtid_thread             |     2 | 676.32 us     | 204.77 us   | 338.16 us   | 471.55 us   |        38 |           NULL |
| log_writer_thread             |    20 | 484.33 us     | 4.01 us     | 24.22 us    | 82.82 us    |        19 |           NULL |
| log_checkpointer_thread       |     5 | 372.41 us     | 40.35 us    | 74.48 us    | 93.21 us    |        15 |           NULL |
+-------------------------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+

复制

也可以通过“x$io_by_thread_by_latency”视图进行查询，输出与上面不同的格式：

 MySQL > SELECT * FROM x$io_by_thread_by_latency;
+-------------------------------+-------+---------------+-------------+-----------------+-------------+-----------+----------------+
| user                          | total | total_latency | min_latency | avg_latency     | max_latency | thread_id | processlist_id |
+-------------------------------+-------+---------------+-------------+-----------------+-------------+-----------+----------------+
| root@localhost                |   140 |   60984015344 |    10711008 |  496115704.0000 | 14905345868 |        61 |             21 |
| page_flush_coordinator_thread |   234 |   59797545486 |     5434072 | 1322577540.6667 | 15056769588 |        14 |           NULL |
| srv_purge_thread              |   305 |   36832653928 |    32322038 |  120762558.0000 | 14696030412 |        39 |           NULL |
| main                          |   149 |   22809418688 |      154560 |  157963475.6000 |  1521498300 |         1 |           NULL |
| log_flusher_thread            |    14 |   11258612792 |    39700668 |  804186628.0000 |  5088309366 |        17 |           NULL |
| buf_dump_thread               |    76 |   10474232062 |    36558270 |  137818576.0000 |   732521986 |        37 |           NULL |
| io_write_thread               |     7 |    7482454714 |   186986044 | 1068921826.0000 |  2135861742 |        10 |           NULL |
| io_write_thread               |     5 |    5323016776 |   418716242 | 1064603162.0000 |  1964329122 |        13 |           NULL |
| io_write_thread               |     2 |    2574735828 |   390174484 | 1287367914.0000 |  2184561344 |        11 |           NULL |
| io_write_thread               |     1 |    1554777322 |  1554777322 | 1554777322.0000 |  1554777322 |        12 |           NULL |
| clone_gtid_thread             |     2 |     676316886 |   204770426 |  338158282.0000 |   471546460 |        38 |           NULL |
| log_writer_thread             |    20 |     484325996 |     4006646 |   24216010.0000 |    82823552 |        19 |           NULL |
| log_checkpointer_thread       |     5 |     372411354 |    40351430 |   74482142.0000 |    93209018 |        15 |           NULL |
+-------------------------------+-------+---------------+-------------+-----------------+-------------+-----------+----------------+
13 rows in set (0.0048 sec)

复制

另外可以查看内存缓存了啥? 尤其是LRU

select TABLE_NAME,INDEX_NAME,PAGE_TYPE,IS_OLD,SUM(PAGE_NUMBER) AS PAGENUM,SUM(NUMBER_RECORDS) AS CACHERECORDS,ROUND(SUM(DATA_SIZE)/1024/1024,2) AS MEM_SIZE_MB 
from information_schema.innodb_buffer_page_lru
GROUP BY TABLE_NAME,INDEX_NAME,PAGE_TYPE,IS_OLD; --##内存页LRU

+------------------------------------------------------------------------------+-----------------------------------------------+-------------------+--------+-----------+--------------+-------------+
| TABLE_NAME                                                                   | INDEX_NAME                                    | PAGE_TYPE         | IS_OLD | PAGENUM   | CACHERECORDS | MEM_SIZE_MB |
+------------------------------------------------------------------------------+-----------------------------------------------+-------------------+--------+-----------+--------------+-------------+
| NULL                                                                         | NULL                                          | INDEX             | YES    |  25474844 |       772546 |      119.77 |
| `test`.`trans_online` /* Partition `request_date_time_20220511` */ | merchant_trans_no                             | INDEX             | YES    |  68203361 |       646627 |       27.02 |
| `test`.`trans_online` /* Partition `request_date_time_20220511` */ | bank_card_no_idx                              | INDEX             | YES    | 112698325 |      1412953 |       40.59 |
| `test`.`trans_online` /* Partition `request_date_time_20220511` */ | channel_tran_no_idx                           | INDEX             | YES    |  96949514 |      1450059 |       32.58 |
| `test`.`trans_online` /* Partition `request_date_time_20220511` */ | idx_dk_trans_online_pay_time                  | INDEX             | YES    |  89791952 |      1440915 |       28.57 |
| `test`.`trans_online` /* Partition `request_date_time_20220511` */ | request_date_time_idx                         | INDEX             | YES    |  82050795 |      1450327 |       24.90 |
| `test`.`trans_online` /* Partition `request_date_time_20220511` */ | channel_code_channel_product_code_dktrans_idx | INDEX             | YES    | 138391490 |      1458600 |       40.34 |
| NULL                                                                         | NULL                                          | IBUF_BITMAP       | YES    |     33236 |            0 |        0.00 |
| NULL                                                                         | NULL                                          | FILE_SPACE_HEADER | YES    |         0 |            0 |        0.00 |
| `aesygo_test`.`dk_trans_online` /* Partition `request_date_time_20220511` */ | merchant_no_dktrans_idx                       | INDEX             | YES    | 114332201 |      1229172 |       31.60 |
+------------------------------------------------------------------------------+-----------------------------------------------+-------------------+--------+-----------+--------------+-------------+
10 rows in set (16.74 sec)

复制