host_summary_by_file_io,x$host_summary_by_file_io - xiaoboluo768/mysql-system-schema GitHub Wiki

• 按照主机分组的文件I/O摘要信息，默认按照总I/O等待时间降序排序。数据来源：events_waits_summary_by_host_by_event_name，调用了sys.format_time()自定义函数、sum()聚合函数对查询结果进行求和运算并转换时间单位

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

  • host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
  • ios：文件I/O事件总次数，即可以认为就是io总数
  • io_latency：文件I/O事件的总等待时间（执行时间）

• 视图定义语句

# host_summary_by_file_io
CREATE OR REPLACE
ALGORITHM = TEMPTABLE
DEFINER = 'root'@'localhost'
SQL SECURITY INVOKER
VIEW host_summary_by_file_io (
host,ios,io_latency
) AS
SELECT IF(host IS NULL, 'background', host) AS host,
SUM(count_star) AS ios,
sys.format_time(SUM(sum_timer_wait)) AS io_latency
FROM performance_schema.events_waits_summary_by_host_by_event_name
WHERE event_name LIKE 'wait/io/file/%'
GROUP BY IF(host IS NULL, 'background', host)
ORDER BY SUM(sum_timer_wait) DESC;

# x$host_summary_by_file_io
CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$host_summary_by_file_io (
  host,  ios,  io_latency
) AS
SELECT IF(host IS NULL, 'background', host) AS host,
      SUM(count_star) AS ios,
      SUM(sum_timer_wait) AS io_latency
  FROM performance_schema.events_waits_summary_by_host_by_event_name
WHERE event_name LIKE 'wait/io/file/%'
GROUP BY IF(host IS NULL, 'background', host)
ORDER BY SUM(sum_timer_wait) DESC;

• 视图查询信息示例

root@localhost : sys 12:39:10> select * from host_summary_by_file_io limit 3;
+---------------+------+------------+
| host          | ios  | io_latency |
+---------------+------+------------+
| background    | 4150 | 10.09 s    |
| localhost    |  68 | 148.33 ms  |
| 192.168.2.122 |  11 | 53.33 us  |
+---------------+------+------------+
3 rows in set (0.00 sec)

root@localhost : sys 12:39:25> select * from x$host_summary_by_file_io limit 3;
+---------------+------+----------------+
| host          | ios  | io_latency    |
+---------------+------+----------------+
| background    | 4153 | 10089276998367 |
| localhost    |  68 |  148334439921 |
| 192.168.2.122 |  11 |      53332848 |
+---------------+------+----------------+
3 rows in set (0.00 sec)

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

---

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

下一篇： host_summary_by_file_io_type,x$host_summary_by_file_io_type视图