latest_file_io,x$latest_file_io

• 按照文件和线程分组、文件IO操作开始时间排序的最新的已经执行完成的I/O等待时间信息，数据来源：events_waits_history_long、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%';

• latest_file_io和x$latest_file_io视图字段含义如下：

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

• 视图定义语句

# latest_file_io
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW latest_file_io (
  thread,  file,  latency,  operation,  requested
) AS
SELECT IF(id IS NULL,
            CONCAT(SUBSTRING_INDEX(name, '/', -1), ':', thread_id),
            CONCAT(user, '@', host, ':', id)
          ) thread,
      sys.format_path(object_name) file,
      sys.format_time(timer_wait) AS latency,
      operation,
      sys.format_bytes(number_of_bytes) AS requested
  FROM performance_schema.events_waits_history_long
  JOIN performance_schema.threads USING (thread_id)
  LEFT JOIN information_schema.processlist ON processlist_id = id
WHERE object_name IS NOT NULL
  AND event_name LIKE 'wait/io/file/%'
ORDER BY timer_start;

# x$latest_file_io
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$latest_file_io (
  thread,  file,  latency,  operation,  requested
) AS
SELECT IF(id IS NULL,
            CONCAT(SUBSTRING_INDEX(name, '/', -1), ':', thread_id),
            CONCAT(user, '@', host, ':', id)
          ) thread,
      object_name file,
      timer_wait AS latency,
      operation,
      number_of_bytes AS requested
  FROM performance_schema.events_waits_history_long
  JOIN performance_schema.threads USING (thread_id)
  LEFT JOIN information_schema.processlist ON processlist_id = id
WHERE object_name IS NOT NULL
  AND event_name LIKE 'wait/io/file/%'
ORDER BY timer_start;

• 视图查询信息示例

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)

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)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-latest-file-io.html

---

上一篇： io_global_by_wait_by_latency,x$io_global_by_wait_by_latency视图 |

下一篇： memory_by_host_by_current_bytes,x$memory_by_host_by_current_bytes视图