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

• 按照用户分组的文件I/O等待、IOS摘要信息，默认按照总文件I/O时间延迟时间(执行时间)降序排序。数据来源：events_waits_summary_by_user_by_event_name

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

  • user：客户端用户名。如果在performance_schema表中user列为NULL，则假定为后台线程，该字段为'background',如果为前台线程，则该字段对应具体的用户名
  • ios：对应用户的文件I/O事件总次数
  • io_latency：对应用户的文件I/O事件的总等待时间(执行时间)

• 视图定义语句

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

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

• 视图查询信息示例

admin@localhost : sys 12:56:18> select * from user_summary_by_file_io limit 3;
+------------+-------+------------+
| user      | ios  | io_latency |
+------------+-------+------------+
| admin      | 30331 | 15.53 s    |
| background | 10119 | 2.49 s    |
| qfsys      |  281 | 4.69 ms    |
+------------+-------+------------+
3 rows in set (0.01 sec)

admin@localhost : sys 12:56:21> select * from x$user_summary_by_file_io limit 3;
+------------+-------+----------------+
| user      | ios  | io_latency    |
+------------+-------+----------------+
| admin      | 30331 | 15526562924625 |
| background | 10122 |  2489231563125 |
| qfsys      |  281 |    4689150375 |
+------------+-------+----------------+
3 rows in set (0.00 sec)

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

---

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

下一篇： user_summary_by_file_io_type,x$user_summary_by_file_io_type视图