等待事件统计视图 | 全方位认识 sys 系统库

柔情只为你懂 2022-12-25 07:00 137阅读 0赞

在上一篇[《内存分配统计视图 | 全方位认识 sys 系统库》][_ _ sys]中，我们介绍了sys 系统库如何查询内存事件统计信息和buffer pool统计信息，本期的内容先给大家介绍按照等待事件统计相关的视图（注意不要和[《按 file 分组统计视图|全方位认识 sys 系统库》][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

"翻过这座山，你就可以看到一片海！"。坚持阅读我们的"全方位认识 sys 系统库"系列文章分享，你就可以系统地学完它。谢谢你的阅读，我们下期不见不散！

**| 作者简介**

### **罗小波·数据库技术专家** ###

《千金良方——MySQL性能优化金字塔法则》、《数据生态：MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构，擅长数据库的整体调优，喜好专研开源技术，并热衷于开源技术的推广，在线上线下做过多次公开的数据库专题分享，发表过近100篇数据库相关的研究文章。

全文完。

Enjoy MySQL :)

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

![format_png][]

[_ _ sys]: http://mp.weixin.qq.com/s?__biz=MzU0MTczNzA1OA%3D%3D&chksm=fb242a51cc53a347a0a5e32f265ce05439c2263670191e953f8f8bd2909436dd2c99f26bcb45&idx=1&mid=2247484252&scene=21&sn=dc4d795bee806aaa98d46232910e6c7a#wechat_redirect
[file _ sys]: http://mp.weixin.qq.com/s?__biz=MzU0MTczNzA1OA%3D%3D&chksm=fb242a49cc53a35f71b37855d720e96c93650fdca22d15cd925ed8aa1fe164149c16fba773c1&idx=1&mid=2247484228&scene=21&sn=4e838f88d0635630f1199ef292d29468#wechat_redirect
[format_png]: /images/20221120/9d96199ecc3c47428809e51fed0e6103.png