在上一篇《按 user 分组统计视图 | 全方位认识 sys 系统库》中，我们介绍了sys 系统库中按 user 分组统计的视图，类似地，本期的内容将为大家介绍按照 file 进行分类统计的视图。下面请跟随我们一起开始 sys 系统库的系统学习之旅吧。
01
io_by_thread_by_latency,x$io_by_thread_by_latency
按照thread ID、processlist ID、用户名分组的 I/O等待时间开销统计信息，默认情况下按照总I/O等待时间降序排序。数据来源：performance_schema.events_waits_summary_by_thread_by_event_name、performance_scgema.threads
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
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)
# 带x$前缀的视图
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)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
视图字段含义如下：
user：对于前台线程，该列显示与线程关联的account名称（user@host格式），对于后台线程，该列显示后台线程的名称
total：I/O事件总次数
total_latency：I/O事件的总延迟时间（执行时间）
min_latency：I/O事件的单次最小延迟时间（执行时间）
avg_latency：I/O事件的平均延迟时间（执行时间）
max_latency：I/O事件的单次最大延迟时间（执行时间）
thread_id：内部thread ID
processlist_id：对于前台线程，该列显示为processlist ID，对于后台线程，该列显示为NULL
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
02
io_global_by_file_by_bytes,x$io_global_by_file_by_bytes
按照文件路径+名称分组的全局I/O读写字节数、读写文件I/O事件数量进行统计，默认情况下按照总I/O(读写字节数)进行降序排序。数据来源：performance_schema.file_summary_by_instance
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
root@localhost : sys 12:43:27> select * from io_global_by_file_by_bytes limit 3;
+---------------------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
| file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct |
+---------------------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
| @@innodb_data_home_dir/ibtmp1 | 0 | 0 bytes | 0 bytes | 2798 | 55.53 MiB | 20.32 KiB | 55.53 MiB | 100.00 |
| @@innodb_undo_directory/undo002 | 874 | 13.66 MiB | 16.00 KiB | 0 | 0 bytes | 0 bytes | 13.66 MiB | 0.00 |
| @@innodb_data_home_dir/ibdata1 | 31 | 2.50 MiB | 82.58 KiB | 3 | 64.00 KiB | 21.33 KiB | 2.56 MiB | 2.44 |
+---------------------------------+------------+------------+-----------+-------------+---------------+-----------+-----------+-----------+
3 rows in set (0.00 sec)
# 带x$前缀的视图
root@localhost : sys 12:43:44> select * from x$io_global_by_file_by_bytes limit 3;
+-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+----------+-----------+
| file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct |
+-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+----------+-----------+
| /home/mysql/data/mysqldata1/innodb_ts/ibtmp1 | 0 | 0 | 0.0000 | 2798 | 58228736 | 20810.8420 | 58228736 | 100.00 |
| /home/mysql/data/mysqldata1/undo/undo002 | 874 | 14319616 | 16384.0000 | 0 | 0 | 0.0000 | 14319616 | 0.00 |
| /home/mysql/data/mysqldata1/innodb_ts/ibdata1 | 31 | 2621440 | 84562.5806 | 3 | 65536 | 21845.3333 | 2686976 | 2.44 |
+-----------------------------------------------+------------+------------+------------+-------------+---------------+------------+----------+-----------+
3 rows in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
视图字段含义如下：
file：文件路径+名称
count_read：读I/O事件总次数
total_read：读I/O事件的总字节数
avg_read：读I/O事件的平均字节数
count_write：写I/O事件总次数
total_written：写I/O事件的总字节数
avg_write：写I/O事件的平均字节数
total：读写I/O事件的总字节数
write_pct：写I/O事件字节数占文件读写I/O事件的总字节数（读和写总字节数）的百分比
03
io_global_by_file_by_latency,x$io_global_by_file_by_latency
按照文件路径+名称分组的全局I/O事件的时间开销统计信息，默认情况下按照文件总的I/O等待时间(读和写的I/O等待时间)进行降序排序。数据来源：performance_schema.file_summary_by_instance
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 09:34:01> admin@localhost : sys 09:34:01> select * from io_global_by_file_by_latency limit 3;
+------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| file | total | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
+------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| @@basedir/share/english/errmsg.sys | 5 | 268.13 ms | 3 | 119.31 ms | 0 | 0 ps | 2 | 148.82 ms |
| /data/mysqldata1/innodb_ts/ibtmp1 | 51 | 103.21 ms | 0 | 0 ps | 47 | 101.96 ms | 4 | 1.26 ms |
| /data/mysqldata1/undo/undo003 | 139 | 63.41 ms | 132 | 60.72 ms | 1 | 30.11 us | 6 | 2.65 ms |
+------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 09:34:07> select * from x$io_global_by_file_by_latency limit 3;
+----------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| file | total | total_latency | count_read | read_latency | count_write | write_latency | count_misc | misc_latency |
+----------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
| /home/mysql/program/share/english/errmsg.sys | 5 | 268129329000 | 3 | 119307156000 | 0 | 0 | 2 | 148822173000 |
| /data/mysqldata1/innodb_ts/ibtmp1 | 51 | 103214655750 | 0 | 0 | 47 | 101957648625 | 4 | 1257007125 |
| /data/mysqldata1/undo/undo003 | 139 | 63405483000 | 132 | 60724181625 | 1 | 30110625 | 6 | 2651190750 |
+----------------------------------------------+-------+---------------+------------+--------------+-------------+---------------+------------+--------------+
3 rows in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
视图字段含义如下：
file：文件路径+名称
total：I/O事件总次数
total_latency：I/O事件的总延迟时间（执行时间）
count_read：读I/O事件的总次数
read_latency：读I/O事件的总延迟时间（执行时间）
count_write：写I/O事件总次数
write_latency：写I/O事件的总延迟时间（执行时间）
count_misc：其他I/O事件总次数
misc_latency：其他I/O事件的总延迟时间（执行时间）
04
io_global_by_wait_by_bytes,x$io_global_by_wait_by_bytes
按照文件IO事件名称后缀进行分组的统计信息，默认情况下按照总I/O读写总字节数进行降序排序。数据来源：performance_schema.file_summary_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 09:35:20> select * from io_global_by_wait_by_bytes limit 1\G
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
total: 843
total_latency: 439.19 ms
min_latency: 0 ps
avg_latency: 520.99 us
max_latency: 9.52 ms
count_read: 627
total_read: 13.64 MiB
avg_read: 22.28 KiB
count_write: 60
total_written: 12.88 MiB
avg_written: 219.73 KiB
total_requested: 26.52 MiB
1 row in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 09:35:22> select * from x$io_global_by_wait_by_bytes limit 1\G;
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
total: 843
total_latency: 439194939750
min_latency: 0
avg_latency: 520990125
max_latency: 9521262750
count_read: 627
total_read: 14303232
avg_read: 22812.1722
count_write: 60
total_written: 13500416
avg_written: 225006.9333
total_requested: 27803648
1 row in set (0.00 sec)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
视图字段含义如下：
EVENT_NAME：文件IO事件全称去掉了'wait/io/file/'前缀的名称字符串
total：读写I/O事件发生的总次数
total_latency：I/O事件的总延迟时间(执行时间)
min_latency：I/O事件单次最短延迟时间（执行时间）
avg_latency：I/O事件的平均延迟时间（执行时间）
max_latency：I/O事件单次最大延迟时间（执行时间）
count_read：读I/O事件的请求次数
total_read：读I/O事件的总字节数
avg_read：读I/O事件的平均字节数
count_write：写I/O事件的请求次数
total_written：写I/O事件的总字节数
avg_written：写I/O事件的平均字节数
total_requested：读与写I/O事件的总字节数
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
05
io_global_by_wait_by_latency,x$io_global_by_wait_by_latency
按照事件名称后缀字符串分组、IO延迟时间排序的全局I/O等待时间统计信息，数据来源：performance_schema.file_summary_by_event_name
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 09:35:52> select * from io_global_by_wait_by_latency limit 1\G
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
total: 843
total_latency: 439.19 ms
avg_latency: 520.99 us
max_latency: 9.52 ms
read_latency: 317.18 ms
write_latency: 105.05 ms
misc_latency: 16.96 ms
count_read: 627
total_read: 13.64 MiB
avg_read: 22.28 KiB
count_write: 60
total_written: 12.88 MiB
avg_written: 219.73 KiB
1 row in set (0.01 sec)
# 带x$前缀的视图
admin@localhost : sys 09:35:55> select * from x$io_global_by_wait_by_latency limit 1\G;
*************************** 1. row ***************************
event_name: innodb/innodb_data_file
total: 843
total_latency: 439194939750
avg_latency: 520990125
max_latency: 9521262750
read_latency: 317177728125
write_latency: 105052561875
misc_latency: 16964649750
count_read: 627
total_read: 14303232
avg_read: 22812.1722
count_write: 60
total_written: 13500416
avg_written: 225006.9333
1 row 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.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
视图字段含义如下：
EVENT_NAME：文件IO事件全称去掉了'wait/io/file/'前缀的名称字符串
total：I/O事件的发生总次数
total_latency：I/O事件的总延迟时间（执行时间）
avg_latency：I/O事件的平均延迟时间（执行时间）
max_latency：I/O事件单次最大延迟时间（执行时间）
read_latency：读I/O事件的总延迟时间（执行时间）
write_latency：写I/O事件的总延迟时间（执行时间）
misc_latency：其他混杂I/O事件的总延迟时间（执行时间）
count_read：读I/O事件的总请求次数
total_read：读I/O事件的总字节数
avg_read：读I/O事件的平均字节数
count_write：写I/O事件的总请求次数
total_written：写I/O事件的总字节数
avg_written：写I/O事件的平均字节数
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
06
latest_file_io,x$latest_file_io
按照文件名称和线程名称分组、文件IO操作开始起始排序的最新的已经执行完成的I/O等待事件信息，数据来源：performance_schema.events_waits_history_long、performance_schema.threads、information_schema.processlist
由于等待事件相关的instruments和consumers默认没有开启，所以该视图需要打开相关的配置之后才能查询到数据，语句如下：
* 打开等待事件的instruments：update setup_instruments set enabled='yes',timed='yes' where name like '%wait/%';
* 打开等待事件的consumers：update setup_consumers set enabled='yes' where name like '%wait%';
下面我们看看使用该视图查询返回的结果。
# 不带x$前缀的视图
admin@localhost : sys 09:50:34> select * from latest_file_io limit 3;
+------------------------+-----------------------------------------+----------+-----------+-----------+
| thread | file | latency | operation | requested |
+------------------------+-----------------------------------------+----------+-----------+-----------+
| admin@localhost:7 | /data/mysqldata1/slowlog/slow-query.log | 69.24 us | write | 251 bytes |
| page_cleaner_thread:29 | /data/mysqldata1/innodb_ts/ibtmp1 | 93.30 us | write | 16.00 KiB |
| page_cleaner_thread:29 | /data/mysqldata1/innodb_ts/ibtmp1 | 16.89 us | write | 16.00 KiB |
+------------------------+-----------------------------------------+----------+-----------+-----------+
3 rows in set (0.02 sec)
# 带x$前缀的视图
admin@localhost : sys 09:50:36> select * from x$latest_file_io limit 3;
+------------------------+-----------------------------------------+----------+-----------+-----------+
| thread | file | latency | operation | requested |
+------------------------+-----------------------------------------+----------+-----------+-----------+
| admin@localhost:7 | /data/mysqldata1/slowlog/slow-query.log | 69240000 | write | 251 |
| page_cleaner_thread:29 | /data/mysqldata1/innodb_ts/ibtmp1 | 93297000 | write | 16384 |
| page_cleaner_thread:29 | /data/mysqldata1/innodb_ts/ibtmp1 | 16891125 | write | 16384 |
+------------------------+-----------------------------------------+----------+-----------+-----------+
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.
视图字段含义如下：
thread：对于前台线程，显示与线程关联的帐户名和processlist id。对于后台线程，显示后台线程名称和内部thread ID
file：文件路径+名称
latency：I/O事件的延迟时间(执行时间)
operation：I/O操作类型
requested：I/O事件请求的数据字节数
PS：该视图只统计文件IO等待事件信息("wait/io/file/%")
-----------------------------------
按 file 分组统计视图 | 全方位认识 sys 系统库
https://blog.51cto.com/imysql/3171740