waits_by_host_by_latency,x$waits_by_host_by_latency - xiaoboluo768/mysql-system-schema GitHub Wiki
-
按照主机和事件名称分组的等待事件摘要信息，默认情况下按照主机名和总的等待事件延迟时间降序排序，数据来源：events_waits_summary_by_host_by_event_name
- 该视图忽略空闲等待事件信息
-
waits_by_host_by_latency和x$waits_by_host_by_latency视图字段含义如下：
- host：发起连接的主机名
- event：等待事件名称
- total：对应主机发生的等待事件总次数
- total_latency：对应主机的等待事件总等待时间
- avg_latency：对应主机的等待事件的平均等待时间
- max_latency：对应主机的单次等待事件的最大等待时间
-
视图定义语句
# waits_by_host_by_latency
CREATE OR REPLACE
ALGORITHM = MERGE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW waits_by_host_by_latency (
host, event, total, total_latency, avg_latency, max_latency
) AS
SELECT IF(host IS NULL, 'background', host) AS host,
event_name AS event,
count_star AS total,
sys.format_time(sum_timer_wait) AS total_latency,
sys.format_time(avg_timer_wait) AS avg_latency,
sys.format_time(max_timer_wait) AS max_latency
FROM performance_schema.events_waits_summary_by_host_by_event_name
WHERE event_name != 'idle'
AND sum_timer_wait > 0
ORDER BY host, sum_timer_wait DESC;
# x$waits_by_host_by_latency
CREATE OR REPLACE
ALGORITHM = MERGE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW x$waits_by_host_by_latency (
host, event, total, total_latency, avg_latency, max_latency
) AS
SELECT IF(host IS NULL, 'background', host) AS host,
event_name AS event,
count_star AS total,
sum_timer_wait AS total_latency,
avg_timer_wait AS avg_latency,
max_timer_wait AS max_latency
FROM performance_schema.events_waits_summary_by_host_by_event_name
WHERE event_name != 'idle'
AND sum_timer_wait > 0
ORDER BY host, sum_timer_wait DESC;
- 视图查询信息示例
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)
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)
上一篇： wait_classes_global_by_latency,x$wait_classes_global_by_latency视图 |