等待事件统计视图

在上一篇《内存分配统计视图 | 全方位认识 sys 系统库》中，我们介绍了sys 系统库如何查询内存事件统计信息和buffer pool统计信息，本期的内容先给大家介绍按照等待事件统计相关的视图（注意不要和《按 fie 分组统计视图|全方位认识 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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
 

 

视图字段含义如下：

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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
 

 

视图字段含义如下：

该视图字段含义和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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
 

 

视图字段含义如下：

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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
 

 

视图字段含义如下：

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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
 

 

 

视图字段含义如下：

events：等待事件名称

其他字段含义和waits_by_host_by_latency,x$waits_by_host_by_latency 视图字段含义相同，不同的是waits_global_by_latency,x$waits_global_by_latency视图只按照事件名称分组
-----------------------------------
等待事件统计视图 | 全方位认识 sys 系统库
https://blog.51cto.com/imysql/3171834