io_by_thread_by_latency,x$io_by_thread_by_latency - xiaoboluo768/mysql-system-schema GitHub Wiki
-
按照线程ID、进程ID、用户名分组的 I/O等待时间开销统计信息，默认情况下按照总I/O等待时间降序排序。数据来源：events_waits_summary_by_thread_by_event_name、threads
-
io_by_thread_by_latency和x$io_by_thread_by_latency视图字段含义如下：
- user：对于前台线程，该列显示与线程关联的account名称，对于后台线程，该列显示后台线程的名称
- total：I/O事件总数
- total_latency：I/O事件的总等待时间（执行时间）
- min_latency：I/O事件的单次最小等待时间（执行时间）
- avg_latency：I/O事件的平均等待时间（执行时间）
- max_latency：I/O事件的单次最大等待时间（执行时间）
- thread_id：内部线程ID
- processlist_id：对于前台线程，该列显示为processlist ID，对于后台线程，该列显示为NULL
-
视图定义语句
# io_by_thread_by_latency
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW io_by_thread_by_latency (
user, total, total_latency, min_latency, avg_latency, max_latency, thread_id, processlist_id
)
AS
SELECT IF(processlist_id IS NULL,
SUBSTRING_INDEX(name, '/', -1),
CONCAT(processlist_user, '@', processlist_host)
) user,
SUM(count_star) total,
sys.format_time(SUM(sum_timer_wait)) total_latency,
sys.format_time(MIN(min_timer_wait)) min_latency,
sys.format_time(AVG(avg_timer_wait)) avg_latency,
sys.format_time(MAX(max_timer_wait)) max_latency,
thread_id,
processlist_id
FROM performance_schema.events_waits_summary_by_thread_by_event_name
LEFT JOIN performance_schema.threads USING (thread_id)
WHERE event_name LIKE 'wait/io/file/%'
AND sum_timer_wait > 0
GROUP BY thread_id, processlist_id, user
ORDER BY SUM(sum_timer_wait) DESC;
# x$io_by_thread_by_latency
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW x$io_by_thread_by_latency (
user, total, total_latency, min_latency, avg_latency, max_latency, thread_id, processlist_id
)
AS
SELECT IF(processlist_id IS NULL,
SUBSTRING_INDEX(name, '/', -1),
CONCAT(processlist_user, '@', processlist_host)
) user,
SUM(count_star) total,
SUM(sum_timer_wait) total_latency,
MIN(min_timer_wait) min_latency,
AVG(avg_timer_wait) avg_latency,
MAX(max_timer_wait) max_latency,
thread_id,
processlist_id
FROM performance_schema.events_waits_summary_by_thread_by_event_name
LEFT JOIN performance_schema.threads USING (thread_id)
WHERE event_name LIKE 'wait/io/file/%'
AND sum_timer_wait > 0
GROUP BY thread_id, processlist_id, user
ORDER BY SUM(sum_timer_wait) DESC;
- 视图查询信息示例
root@localhost : sys 12:42:44> select * from io_by_thread_by_latency limit 3;
+-----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| user | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+-----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
| buf_dump_thread | 880 | 4.67 s | 2.94 us | 5.30 ms | 27.33 ms | 40 | NULL |
| main | 2214 | 3.63 s | 409.05 ns | 2.28 ms | 35.48 ms | 1 | NULL |
| root@localhost | 21 | 88.87 ms | 527.22 ns | 2.03 ms | 21.31 ms | 49 | 7 |
+-----------------+-------+---------------+-------------+-------------+-------------+-----------+----------------+
3 rows in set (0.01 sec)
root@localhost : sys 12:43:24> select * from x$io_by_thread_by_latency limit 3;
+-----------------+-------+---------------+-------------+-----------------+-------------+-----------+----------------+
| user | total | total_latency | min_latency | avg_latency | max_latency | thread_id | processlist_id |
+-----------------+-------+---------------+-------------+-----------------+-------------+-----------+----------------+
| buf_dump_thread | 880 | 4667572388808 | 2938797 | 5304059238.0000 | 27331328412 | 40 | NULL |
| main | 2214 | 3626928831147 | 409050 | 2283656763.0000 | 35476899531 | 1 | NULL |
| root@localhost | 21 | 88867469637 | 527220 | 2026334846.2500 | 21312776994 | 49 | 7 |
+-----------------+-------+---------------+-------------+-----------------+-------------+-----------+----------------+
3 rows in set (0.01 sec)
上一篇： innodb_lock_waits,x$innodb_lock_waits视图 |
下一篇： io_global_by_file_by_bytes,x$io_global_by_file_by_bytes视图