等待事件统计视图 | 全方位认识 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存储引擎,喜好专研开源技术,追求完美。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java3y

操作系统第七篇【设备管理】

1233
来自专栏北京马哥教育

性能调优攻略

关于性能优化这是一个比较大的话题,在《由12306.cn谈谈网站性能技术》中我从业务和设计上说过一些可用的技术以及那些技术的优缺点,今天,想从一些技术细节上谈...

3474
来自专栏Java进阶架构师

dubbo源码解析-详解router

估算了一下,dubbo里面涉及的东西还是比较多的.比如谈到框架的时候,设计模式都是一个老生常谈的话题,再比如我们开发中我们不常用的一些概念,spi、javass...

983
来自专栏鹅厂网事

高性能网关设备及服务实践

针对海量的网络流量,转发性能是我们最关键的一个方面,那构建高性能的后台服务器有哪些关键的技术和需要注意的地方。

7518
来自专栏Linyb极客之路

低延迟系统的最佳实践

低延迟意味着更快的响应时间,更快的性能,以下最佳实践大部分来自于Quora等问题提炼:

1112
来自专栏大数据和云计算技术

云存储产品浅析

云上存储产品主要有对象存储,块存储,网络文件系统(NAS),还有最赚钱的CDN,我们将针对这些主流产品,讲讲他们产品特点,有云上存储时候知道如何选型,当然我们是...

3583
来自专栏程序员八阿哥

年薪20万Python工程师进阶(7):Python资源大全,让你相见恨晚的Python库

用来访问第三方 API的库。 参见: List of Python API Wrappers and Libraries。

3372
来自专栏人工智能

深度学习环境搭建:tensorflow安装教程及常见错误解决

区别于其他入门教程的“手把手式”,本文更强调“因”而非“果”。我之所以加上“通用”字样,是因为在你了解了这个开发环境之后,那些很low的错误你就不会犯了。 大家...

3266
来自专栏维C果糖

GitHub 术语解释

为了大家进一步了解和使用 GitHub,在本文中,我们一起来看看 GitHub 的常用术语,也可以说是基本概念: Repository:简称Repo,可以理解为...

1925
来自专栏北京马哥教育

一秒内诊断 Linux 服务器的性能

60,000 毫秒内对 Linux 的性能诊断 当你为了解决一个性能问题登录到一台 Linux 服务器:在第一分钟你应该检查些什么? 在 Netflix,我们有...

3396

扫码关注云+社区