wait_classes_global_by_latency,x$wait_classes_global_by_latency - xiaoboluo768/mysql-system-schema GitHub Wiki
-
数据库事件大类(等待事件名称前三级前缀)分组（如：wait/io/table、wait/io/file、wait/lock/table）的等待事件平均延迟时间等摘要信息，默认情况下按照总延迟时间(执行时间)降序排序。数据来源：events_waits_summary_global_by_event_name
-
wait_classes_global_by_latency,x$wait_classes_global_by_latency视图字段含义如下：
- 该视图字段含义和2.3.48. wait_classes_global_by_avg_latency,x$wait_classes_global_by_avg_latency 视图字段含义相同，只是排序字段不同而已
-
视图定义语句
# wait_classes_global_by_latency
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW wait_classes_global_by_latency (
event_class, total, total_latency, min_latency, avg_latency, max_latency
) AS
SELECT SUBSTRING_INDEX(event_name,'/', 3) AS event_class,
SUM(COUNT_STAR) AS total,
sys.format_time(SUM(sum_timer_wait)) AS total_latency,
sys.format_time(MIN(min_timer_wait)) min_latency,
sys.format_time(IFNULL(SUM(sum_timer_wait) / NULLIF(SUM(COUNT_STAR), 0), 0)) AS avg_latency,
sys.format_time(MAX(max_timer_wait)) AS max_latency
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE sum_timer_wait > 0
AND event_name != 'idle'
GROUP BY SUBSTRING_INDEX(event_name,'/', 3)
ORDER BY SUM(sum_timer_wait) DESC;
# x$wait_classes_global_by_latency
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW x$wait_classes_global_by_latency (
event_class, total, total_latency, min_latency, avg_latency, max_latency
) AS
SELECT SUBSTRING_INDEX(event_name,'/', 3) AS event_class,
SUM(COUNT_STAR) AS total,
SUM(sum_timer_wait) AS total_latency,
MIN(min_timer_wait) AS min_latency,
IFNULL(SUM(sum_timer_wait) / NULLIF(SUM(COUNT_STAR), 0), 0) AS avg_latency,
MAX(max_timer_wait) AS max_latency
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE sum_timer_wait > 0
AND event_name != 'idle'
GROUP BY SUBSTRING_INDEX(event_name,'/', 3)
ORDER BY SUM(sum_timer_wait) DESC;
- 视图查询信息示例
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)
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视图 |