在上一篇《按 user 分组统计视图 | 全方位认识 sys 系统库》中,我们介绍了sys 系统库中按 user 分组统计的视图,类似地,本期的内容将为大家介绍按照 file 进行分类统计的视图。下面请跟随我们一起开始 sys 系统库的系统学习之旅吧。
按照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)
视图字段含义如下:
PS:该视图只统计文件IO等待事件信息("wait/io/file/%")
按照文件路径+名称分组的全局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)
视图字段含义如下:
按照文件路径+名称分组的全局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)
视图字段含义如下:
按照文件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)
视图字段含义如下:
PS:该视图只统计文件IO等待事件信息("wait/io/file/%")
按照事件名称后缀字符串分组、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)
视图字段含义如下:
PS:该视图只统计文件IO等待事件信息("wait/io/file/%")
按照文件名称和线程名称分组、文件IO操作开始起始排序的最新的已经执行完成的I/O等待事件信息,数据来源:performance_schema.events_waits_history_long、performance_schema.threads、information_schema.processlist
* 打开等待事件的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)
视图字段含义如下:
PS:该视图只统计文件IO等待事件信息("wait/io/file/%")
本期内容就介绍到这里,本期内容参考链接如下:
https://dev.mysql.com/doc/refman/5.7/en/sys-latest-file-io.html
https://dev.mysql.com/doc/refman/5.7/en/sys-io-by-thread-by-latency.html
https://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-file-by-latency.html
https://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-wait-by-bytes.html
https://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-wait-by-latency.html
https://dev.mysql.com/doc/refman/5.7/en/sys-io-global-by-file-by-bytes.html
"翻过这座山,你就可以看到一片海!"。坚持阅读我们的"全方位认识 sys 系统库"系列文章分享,你就可以系统地学完它。 谢谢你的阅读,我们下期不见不散!
| 作者简介
《千金良方——MySQL性能优化金字塔法则》、《数据生态:MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构,擅长数据库的整体调优,喜好专研开源技术,并热衷于开源技术的推广,在线上线下做过多次公开的数据库专题分享,发表过近100篇数据库相关的研究文章。
全文完。