按 file 分组统计视图 | 全方位认识 sys 系统库

秒速五厘米 2022-05-16 06:50 148阅读 0赞

在上一篇[《按 user 分组统计视图 | 全方位认识 sys 系统库》][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)

视图字段含义如下：

*  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)

视图字段含义如下：

*  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)

视图字段含义如下：

*  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)

视图字段含义如下：

*  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)

视图字段含义如下：

*  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%';

下面我们看看使用该视图查询返回的结果。

视图字段含义如下：

*  thread：对于前台线程，显示与线程关联的帐户名和processlist id。对于后台线程，显示后台线程名称和内部thread ID
 *  file：文件路径+名称
 *  latency：I/O事件的延迟时间(执行时间)
 *  operation：I/O操作类型
 *  requested：I/O事件请求的数据字节数

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

### **| 作者简介** ###

**罗小波·沃趣科技高级数据库技术专家**

IT从业多年，历任运维工程师，高级运维工程师，运维经理，数据库工程师，曾参与版本发布系统，轻量级监控系统，运维管理平台，数据库管理平台的设计与编写，熟悉MySQL的体系结构时，InnoDB存储引擎，喜好专研开源技术，追求完美。

[user _ _ _ sys]: http://mp.weixin.qq.com/s?__biz=MzU0MTczNzA1OA==&mid=2247484213&idx=1&sn=b8082f4e47a92e4b95d4093f0ba356f0&chksm=fb242a38cc53a32e9d1259173f6ce688ad491d0f6de8e931a2f509bd099b4fae6dca4b890173&scene=21#wechat_redirect