等待事件统计视图 | 全方位认识 sys 系统库
原创
在上一篇《内存分配统计视图 | 全方位认识 sys 系统库》中，我们介绍了sys 系统库如何查询内存事件统计信息和buffer pool统计信息，本期的内容先给大家介绍按照等待事件统计相关的视图（注意不要和《按 file 分组统计视图|全方位认识 sys 系统库》介绍的内容搞混了，这篇中介绍的等待事件仅针对文件IO等待事件，而本篇介绍的是所有的等待事件）。下面请跟随我们一起开始 sys 系统库的系统学习之旅吧～
01.wait_classes_global_by_avg_latency,x$wait_classes_global_by_avg_latency
按照事件大类(等待事件名称层级中前三层组件组成的名称前缀)分组（如：wait/io/table、wait/io/file、wait/lock/table）的等待事件平均延迟时间（总IO延迟时间/总IOS）等统计信息，默认按照平均延迟时间(执行时间)降序排序。数据来源：events_waits_summary_global_by_event_name
- 该视图会忽略空闲等待事件(idle事件)信息
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:58:11> select * from wait_classes_global_by_avg_latency limit 3;
+--------------------+-------+---------------+-------------+-------------+-------------+
| event_class | total | total_latency | min_latency | avg_latency | max_latency |
+--------------------+-------+---------------+-------------+-------------+-------------+
| wait/lock/metadata | 2 | 56.57 m | 12.94 m | 28.28 m | 43.63 m |
| wait/synch/cond | 7980 | 4.37 h | 0 ps | 1.97 s | 5.01 s |
| wait/io/socket | 28988 | 21.02 s | 0 ps | 725.29 us | 103.18 ms |
+--------------------+-------+---------------+-------------+-------------+-------------+
3 rows in set (0.05 sec)
# 带x$前缀的视图
admin@localhost : sys 12:58:22> select * from x$wait_classes_global_by_avg_latency limit 3;
+--------------------+-------+-------------------+-----------------+-----------------------+------------------+
| event_class | total | total_latency | min_latency | avg_latency | max_latency |
+--------------------+-------+-------------------+-----------------+-----------------------+------------------+
| wait/lock/metadata | 2 | 3393932470401750 | 776378395041375 | 1696966235200875.0000 | 2617554075360375 |
| wait/synch/cond | 7980 | 15739342570225500 | 0 | 1972348693010.7143 | 5006888904375 |
| wait/io/socket | 28990 | 21024710924250 | 0 | 725240114.6689 | 103181011500 |
+--------------------+-------+-------------------+-----------------+-----------------------+------------------+
3 rows in set (0.02 sec)视图字段含义如下：
- event_class：事件类别，事件名称层级中前三层组件组成的名称前缀，如'wait/io/file/sql/slow_log'，截取后保留'wait/io/file' 字符串作为事件类别
- total：对应事件大类的事件总次数
- total_latency：对应事件大类的事件总延迟时间(执行时间)
- min_latency：对应事件大类的单次事件最小延迟时间(执行时间)
- avg_latency：对应事件大类中，每个事件的平均延迟时间(执行时间)
- max_latency：对应事件大类的单次事件在最大延迟时间(执行时间)
02.wait_classes_global_by_latency,x$wait_classes_global_by_latency
按照事件大类(等待事件名称前三层前缀)分组（如：wait/io/table、wait/io/file、wait/lock/table）的等待事件平均延迟时间等统计信息，默认情况下按照总延迟时间(执行时间)降序排序。数据来源：events_waits_summary_global_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:58:26> select * from wait_classes_global_by_latency limit 3;
+--------------------+----------+---------------+-------------+-------------+-------------+
| event_class | total | total_latency | min_latency | avg_latency | max_latency |
+--------------------+----------+---------------+-------------+-------------+-------------+
| wait/synch/cond | 7983 | 4.38 h | 0 ps | 1.97 s | 5.01 s |
| wait/lock/metadata | 2 | 56.57 m | 12.94 m | 28.28 m | 43.63 m |
| wait/io/table | 16096791 | 4.59 m | 12.03 us | 17.11 us | 2.02 m |
+--------------------+----------+---------------+-------------+-------------+-------------+
3 rows in set (0.02 sec)
# 带x$前缀的视图
admin@localhost : sys 12:58:40> select * from x$wait_classes_global_by_latency limit 3;
+--------------------+----------+-------------------+-----------------+-----------------------+------------------+
| event_class | total | total_latency | min_latency | avg_latency | max_latency |
+--------------------+----------+-------------------+-----------------+-----------------------+------------------+
| wait/synch/cond | 7984 | 15759344050722375 | 0 | 1973865737815.9287 | 5006888904375 |
| wait/lock/metadata | 2 | 3393932470401750 | 776378395041375 | 1696966235200875.0000 | 2617554075360375 |
| wait/io/table | 16096791 | 275441586767625 | 12026625 | 17111583.7168 | 121243803313125 |
+--------------------+----------+-------------------+-----------------+-----------------------+------------------+
3 rows in set (0.02 sec)视图字段含义如下：
- 该视图字段含义和wait_classes_global_by_avg_latency,x$wait_classes_global_by_avg_latency 视图字段含义相同，只是排序字段不同而已
03.waits_by_host_by_latency,x$waits_by_host_by_latency
按照主机和事件名称分组的等待事件统计信息，默认情况下按照主机名和总的等待事件延迟时间降序排序，数据来源：events_waits_summary_by_host_by_event_name
- 该视图忽略空闲等待事件（idle事件）信息
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:58:43> select * from waits_by_host_by_latency limit 3;
+-------------+----------------------------------------------+-------+---------------+-------------+-------------+
| host | event | total | total_latency | avg_latency | max_latency |
+-------------+----------------------------------------------+-------+---------------+-------------+-------------+
| 10.10.20.14 | wait/io/socket/sql/client_connection | 24568 | 20.53 s | 835.48 us | 70.46 ms |
| 10.10.20.14 | wait/synch/mutex/innodb/trx_pool_mutex | 2326 | 14.59 s | 6.27 ms | 215.63 ms |
| 10.10.20.14 | wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done | 1707 | 13.74 s | 8.05 ms | 43.33 ms |
+-------------+----------------------------------------------+-------+---------------+-------------+-------------+
3 rows in set (0.00 sec)
# 带x$前缀的视图
admin@localhost : sys 12:59:04> select * from x$waits_by_host_by_latency limit 3;
+-------------+----------------------------------------------+-------+----------------+-------------+--------------+
| host | event | total | total_latency | avg_latency | max_latency |
+-------------+----------------------------------------------+-------+----------------+-------------+--------------+
| 10.10.20.14 | wait/io/socket/sql/client_connection | 24568 | 20526083640375 | 835480125 | 70457480625 |
| 10.10.20.14 | wait/synch/mutex/innodb/trx_pool_mutex | 2326 | 14586650782125 | 6271131000 | 215632752375 |
| 10.10.20.14 | wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done | 1707 | 13737760876125 | 8047897125 | 43332152250 |
+-------------+----------------------------------------------+-------+----------------+-------------+--------------+
3 rows in set (0.01 sec)视图字段含义如下：
- host：发起连接的主机名
- event：等待事件名称
- total：对应主机发生的等待事件总次数
- total_latency：对应主机的等待事件总延迟时间
- avg_latency：对应主机的等待事件的平均延迟时间
- max_latency：对应主机的单次等待事件的最大延迟时间
04.waits_by_user_by_latency,x$waits_by_user_by_latency
按照用户和事件名称分组的等待事件统计信息，默认情况下按照用户名和总的等待事件延迟事件降序排序，数据来源：events_waits_summary_by_user_by_event_name
- 该视图忽略空闲等待事件（idle事件）信息
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:59:07> select * from waits_by_user_by_latency limit 3;
+-------+---------------------------------------------------+----------+---------------+-------------+-------------+
| user | event | total | total_latency | avg_latency | max_latency |
+-------+---------------------------------------------------+----------+---------------+-------------+-------------+
| admin | wait/lock/metadata/sql/mdl | 2 | 56.57 m | 28.28 m | 43.63 m |
| admin | wait/synch/cond/sql/MDL_context::COND_wait_status | 3395 | 56.56 m | 999.66 ms | 1.00 s |
| admin | wait/io/table/sql/handler | 16096791 | 4.59 m | 17.11 us | 2.02 m |
+-------+---------------------------------------------------+----------+---------------+-------------+-------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 12:59:22> select * from x$waits_by_user_by_latency limit 3;
+-------+---------------------------------------------------+----------+------------------+------------------+------------------+
| user | event | total | total_latency | avg_latency | max_latency |
+-------+---------------------------------------------------+----------+------------------+------------------+------------------+
| admin | wait/lock/metadata/sql/mdl | 2 | 3393932470401750 | 1696966235200875 | 2617554075360375 |
| admin | wait/synch/cond/sql/MDL_context::COND_wait_status | 3395 | 3393839154564375 | 999658071750 | 1004173431750 |
| admin | wait/io/table/sql/handler | 16096791 | 275441586767625 | 17111250 | 121243803313125 |
+-------+---------------------------------------------------+----------+------------------+------------------+------------------+
3 rows in set (0.01 sec)视图字段含义如下：
- user：与该连接关联的用户名
- 其他字段与waits_by_host_by_latency,x$waits_by_host_by_latency 视图字段含义相同，不同的是waits_by_user_by_latency,x$waits_by_user_by_latency视图是按照用户名和事件名称分组
05.waits_global_by_latency,x$waits_global_by_latency
按照事件名称分组的等待事件统计信息，默认按照等待事件总延迟时间降序排序。数据来源：events_waits_summary_global_by_event_name
- 该视图忽略空闲等待事件（idle事件）信息
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 12:59:25> select * from waits_global_by_latency limit 3;
+---------------------------------------------------+-------+---------------+-------------+-------------+
| events | total | total_latency | avg_latency | max_latency |
+---------------------------------------------------+-------+---------------+-------------+-------------+
| wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond | 2891 | 3.45 h | 4.29 s | 5.01 s |
| wait/lock/metadata/sql/mdl | 2 | 56.57 m | 28.28 m | 43.63 m |
| wait/synch/cond/sql/MDL_context::COND_wait_status | 3395 | 56.56 m | 999.66 ms | 1.00 s |
+---------------------------------------------------+-------+---------------+-------------+-------------+
3 rows in set (0.02 sec)
# 带x$前缀的视图
admin@localhost : sys 12:59:40> select * from x$waits_global_by_latency limit 3;
+---------------------------------------------------+-------+-------------------+------------------+------------------+
| events | total | total_latency | avg_latency | max_latency |
+---------------------------------------------------+-------+-------------------+------------------+------------------+
| wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond | 2892 | 12411771548807250 | 4291760563125 | 5006888904375 |
| wait/lock/metadata/sql/mdl | 2 | 3393932470401750 | 1696966235200875 | 2617554075360375 |
| wait/synch/cond/sql/MDL_context::COND_wait_status | 3395 | 3393839154564375 | 999658071750 | 1004173431750 |
+---------------------------------------------------+-------+-------------------+------------------+------------------+
3 rows in set (0.02 sec)视图字段含义如下：
- events：等待事件名称
- 其他字段含义和waits_by_host_by_latency,x$waits_by_host_by_latency 视图字段含义相同，不同的是waits_global_by_latency,x$waits_global_by_latency视图只按照事件名称分组
本期内容就介绍到这里，本期内容参考链接如下：
https://dev.mysql.com/doc/refman/5.7/en/sys-waits-global-by-latency.html
https://dev.mysql.com/doc/refman/5.7/en/sys-wait-classes-global-by-latency.html
https://dev.mysql.com/doc/refman/5.7/en/sys-wait-classes-global-by-avg-latency.html
https://dev.mysql.com/doc/refman/5.7/en/sys-waits-by-host-by-latency.html
https://dev.mysql.com/doc/refman/5.7/en/sys-waits-by-user-by-latency.html
| 作者简介
罗小波·沃趣科技高级数据库技术专家
IT从业多年，历任运维工程师，高级运维工程师，运维经理，数据库工程师，曾参与版本发布系统，轻量级监控系统，运维管理平台，数据库管理平台的设计与编写，熟悉MySQL的体系结构时，InnoDB存储引擎，喜好专研开源技术，追求完美。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。