MySQL监视——sys Schema

MySQLSE

发布于 2023-08-31 14:47:23

2620

发布于 2023-08-31 14:47:23

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)
 MySQL  localhost:3306 ssl  sys  SQL >

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

MySQL  localhost:3306 ssl  sys  SQL > 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  localhost:3306 ssl  sys  SQL > 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  localhost:3306 ssl  sys  SQL > 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)

以上内容是关于sys Schema的一个简明介绍，感谢关注“MySQL解决方案工程师”！

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-06-26，如有侵权请联系 cloudcommunity@tencent.com 删除

latency