MySQL监视——sys Schema
MySQL监视——sys Schema
MySQLSE
发布于 2023-08-31 14:47:23
发布于 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 删除
本文分享自 MySQL解决方案工程师 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ，欢迎热爱写作的你一起参与！
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。