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

最新推荐文章于 2023-02-10 09:50:20 发布

老叶茶馆_

最新推荐文章于 2023-02-10 09:50:20 发布

阅读量352

点赞数

文章标签：数据库 mysql java 大数据 linux

原文链接：https://ke.qq.com/course/package/28037?saleToken=2203389\x26amp;from=pclink

版权

在上一篇《配置表|全方位认识 sys 系统库》中，我们介绍了sys 系统库的配置表，但实际上我们大部分人大多数时候并不需要去修改配置表，直接使用sys 系统库下的视图来获取所需的数据即可，sys 系统库下一共有100多视图，这些视图都能够给我们提供一些什么样的信息呢？本期的内容先给大家介绍按照host进行分类统计相关的视图。下面请跟随我们一起开始 sys 系统库的系统学习之旅吧。

在《初相识|全方位认识 sys 系统库》一文中，我们提到过 sys 系统库的很多视图是成对出现的（带x$的内部视图主要用于程序或者视图之间调用，不带x$的主要用于人工查询使用，返回的数值为经过单位转换的易读格式），按照host进行分类统计的视图应该有6对，这些视图提供的查询内容本质上就是用更易读的格式按照主机的维度进行分组统计等待事件、语句事件、阶段事件等。下面我们依次进行介绍。

01 host_summary_by_file_io,x$host_summary_by_file_io

按主机（与用户账号组成中的host值相同）分组统计的文件I/O的IO总数和IO延迟时间，默认按照总I/O等待时间降序排序。数据来源：performance_schema.events_waits_summary_by_host_by_event_name表，调用了sys.format_time()自定义函数、sum()聚合函数对查询结果进行求和运算并转换时间单位。

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

# 从查询的结果中可以看到，延迟时间带有单位秒，对人类来说更易读
mysql> SELECT * FROM host_summary_by_file_io;
+------------+-------+------------+
| host      | ios  | io_latency |
+------------+-------+------------+
| localhost  | 67570 | 5.38 s    |
| background |  3468 | 4.18 s    |
+------------+-------+------------+
# 带x$前缀的同名视图范围的时间值未经过可读格式装换，单位为皮秒（万亿分之一秒，可读性比较差）
mysql> SELECT * FROM x$host_summary_by_file_io;
+------------+-------+---------------+
| host      | ios  | io_latency    |
+------------+-------+---------------+
| localhost  | 67574 | 5380678125144 |
| background |  3474 | 4758696829416 |
+------------+-------+---------------+

视图字段含义如下：

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

PS：没有x$前缀的视图旨在提供对用户更加友好和更易于阅读的输出格式。而带x$前缀的视图输出的原始格式值更适用于一些工具类的程序使用。没有x$前缀的视图中将会调用如下函数中的一个或者多个进行数值单位转换再输出（后续其他视图的可读格式转换视图相同，下文不再赘述）：

字节值使用format_bytes()函数格式化并转换单位，详见后续章节
时间值使用format_time()函数格式化并转换单位。详见后续章节
使用format_statement()函数将SQL语句文本截断为statement_truncate_len配置选项设置的显示宽度。详见后续章节
路径名称使用format_path()函数截取并替换为相应的系统变量名称。详见后续章节
该视图只统计文件IO等待事件信息("wait/io/file/%")

02 host_summary,x$ host_summary

按照主机分组统计的语句延迟（执行）时间、次数、相关的文件I/O延迟、连接数和内存分配大小等摘要信息，数据来源：performance_schema.accounts、sys.x$host_summary_by_statement_latency、sys.x$host_summary_by_file_io

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

# 不带x$前缀的视图
root@localhost : sys 12:38:11> select * from host_summary limit 1\G
*************************** 1. row ***************************
              host: 192.168.2.122
        statements: 9
statement_latency: 13.22 ms
statement_avg_latency: 1.47 ms
      table_scans: 0
          file_ios: 11
  file_io_latency: 53.33 us
current_connections: 1
total_connections: 1
      unique_users: 1
    current_memory: 0 bytes
total_memory_allocated: 0 bytes
1 row in set (0.01 sec)
# 带x$前缀的视图
root@localhost : sys 12:38:14> select * from x$host_summary limit 1\G
*************************** 1. row ***************************
              host: 192.168.2.122
        statements: 9
statement_latency: 13218739000
statement_avg_latency: 1468748777.7778
      table_scans: 0
          file_ios: 11
  file_io_latency: 53332848
current_connections: 1
total_connections: 1
      unique_users: 1
    current_memory: 0
total_memory_allocated: 0
1 row in set (0.01 sec)

视图字段含义如下：

host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
statements：语句总执行次数
statement_latency：语句总延迟时间（执行时间）
statement_avg_latency：语句的平均延迟时间(执行时间)
table_scans：语句的表扫描总次数
file_ios：文件I/O事件总次数
file_io_latency：文件I/O事件总延迟时间（执行时间）
current_connections：当前连接数
total_connections：总历史连接数
unique_users：不同（去重）用户数量
current_memory：当前内存使用量
total_memory_allocated：总的内存分配量

PS：该视图只统计文件IO等待事件信息("wait/io/file/%")

03 host_summary_by_file_io_type,x$host_summary_by_file_io_type

按照主机和事件名称分组的文件I/O事件次数、延迟统计信息，默认按照主机和总I/O延迟时间降序排序。数据来源：performance_schema.events_waits_summary_by_host_by_event_name，调用了sys.format_time()自定义函数转换时间单位。

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

# 不带x$前缀的视图
root@localhost : sys 12:39:51> select * from host_summary_by_file_io_type limit 3;
+---------------+--------------------------------------+-------+---------------+-------------+
| host          | event_name                          | total | total_latency | max_latency |
+---------------+--------------------------------------+-------+---------------+-------------+
| 192.168.2.122 | wait/io/file/sql/binlog              |    11 | 53.33 us      | 24.33 us    |
| background    | wait/io/file/innodb/innodb_data_file |  1631 | 5.85 s        | 35.48 ms    |
| background    | wait/io/file/sql/FRM                |  2151 | 3.89 s        | 26.10 ms    |
+---------------+--------------------------------------+-------+---------------+-------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
root@localhost : sys 12:39:54> select * from x$host_summary_by_file_io_type limit 3;
+---------------+--------------------------------------+-------+---------------+-------------+
| host          | event_name                          | total | total_latency | max_latency |
+---------------+--------------------------------------+-------+---------------+-------------+
| 192.168.2.122 | wait/io/file/sql/binlog              |    11 |      53332848 |    24334839 |
| background    | wait/io/file/innodb/innodb_data_file |  1631 | 5851714703037 | 35476899531 |
| background    | wait/io/file/sql/FRM                |  2151 | 3894316306089 | 26099526756 |
+---------------+--------------------------------------+-------+---------------+-------------+
3 rows in set (0.00 sec)

视图字段含义如下：

host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
EVENT_NAME：文件I/O事件名称
total：文件I/O事件发生总次数
total_latency：文件I/O事件的总延迟时间（执行时间）
max_latency：文件I/O事件的单次最大延迟时间（执行时间）

PS：该视图只统计文件IO等待事件信息("wait/io/file/%")

04 host_summary_by_stages,x$host_summary_by_stages

按照主机和事件名称分组的阶段事件总次数、总执行时间、平均执行时间等统计信息，默认按照主机和总的延迟（执行）时间降序排序。数据来源：performance_schema.events_stages_summary_by_host_by_event_name，调用了sys.format_time()自定义函数转换时间单位。

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

# 不带x$前缀的视图
root@localhost : sys 12:39:57> select * from host_summary_by_stages limit 3;
+------------+-------------------------------+-------+---------------+-------------+
| host      | event_name                    | total | total_latency | avg_latency |
+------------+-------------------------------+-------+---------------+-------------+
| background | stage/innodb/buffer pool load |    1 | 4.68 s        | 4.68 s      |
+------------+-------------------------------+-------+---------------+-------------+
1 row in set (0.00 sec)
# 带x$前缀的视图
root@localhost : sys 12:40:15> select * from x$host_summary_by_stages limit 3;
+------------+-------------------------------+-------+---------------+---------------+
| host      | event_name                    | total | total_latency | avg_latency  |
+------------+-------------------------------+-------+---------------+---------------+
| background | stage/innodb/buffer pool load |    1 | 4678671071000 | 4678671071000 |
+------------+-------------------------------+-------+---------------+---------------+
1 row in set (0.00 sec)

视图字段含义如下：

host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
EVENT_NAME：阶段事件名称
total：阶段事件总发生次数
total_latency：阶段事件总延迟(执行)时间
avg_latency：阶段事件平均延迟(执行)时间

05 host_summary_by_statement_latency,x$host_summary_by_statement_latency

按照主机和事件名称分组的语句事件总次数、总执行时间、最大执行时间、锁时间以及数据行相关的统计信息，默认按照总延迟（执行）时间降序排序。数据来源：performance_schema.events_statements_summary_by_host_by_event_name

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

# 不带x$前缀的视图
root@localhost : sys 12:40:19> select * from host_summary_by_statement_latency limit 3;
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host          | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| localhost    |  3447 | 539.61 ms    | 89.37 ms    | 131.90 ms    |      3023 |        40772 |            0 |        108 |
| 192.168.2.122 |    9 | 13.22 ms      | 12.55 ms    | 0 ps        |        5 |            0 |            0 |          0 |
| background    |    0 | 0 ps          | 0 ps        | 0 ps        |        0 |            0 |            0 |          0 |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.01 sec)
# 带x$前缀的视图
root@localhost : sys 12:40:36> select * from x$host_summary_by_statement_latency limit 3;
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host          | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| localhost    |  3528 |  544883806000 | 89365202000 | 132140000000 |      3026 |        41351 |            0 |        109 |
| 192.168.2.122 |    9 |  13218739000 | 12550251000 |            0 |        5 |            0 |            0 |          0 |
| background    |    0 |            0 |          0 |            0 |        0 |            0 |            0 |          0 |
+---------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.01 sec)

视图字段含义如下：

host：客户端连接的主机名或IP。在Performance Schema表中的HOST列为NULL的行在这里假定为后台线程，且在该视图host列显示为background
total：语句总执行次数
total_latency：语句总延迟(执行)时间
max_latency：语句单个最大延迟(执行)时间
lock_latency：语句总锁延迟(执行）时间
rows_sent：语句返回给客户端的总数据行数
rows_examined：语句从存储引擎层读取的总数据行数
rows_affected：语句执行时受影响（DML会返回数据发生变更的受影响行数，select等不会产生数据变更的语句执行时不会有受影响行数返回）的总数据行数
full_scans：语句全表扫描总次数

06 host_summary_by_statement_type,x$host_summary_by_statement_type

按照主机和语句分组的当前语句事件总次数、总执行时间、最大执行时间、锁时间以及数据行相关的统计信息（与performance_schema.host_summary_by_statement_latency 视图比起来，该视图只返回执行时间不为0的统计信息，且多了一个statement字段显示语句事件名称层级中的最后一部分字符），数据来源:performance_schema.events_statements_summary_by_host_by_event_name

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

# 不带x$前缀的视图
root@localhost : sys 12:40:40> select * from host_summary_by_statement_type limit 3;
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host          | statement      | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| 192.168.2.122 | select        |    5 | 12.92 ms      | 12.55 ms    | 0 ps        |        5 |            0 |            0 |          0 |
| 192.168.2.122 | set_option    |    3 | 258.22 us    | 166.40 us  | 0 ps        |        0 |            0 |            0 |          0 |
| 192.168.2.122 | Register Slave |    1 | 37.68 us      | 37.68 us    | 0 ps        |        0 |            0 |            0 |          0 |
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.00 sec)
# 带x$前缀的视图
root@localhost : sys 12:41:00> select * from x$host_summary_by_statement_type limit 3;
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host          | statement      | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| 192.168.2.122 | select        |    5 |  12922834000 | 12550251000 |            0 |        5 |            0 |            0 |          0 |
| 192.168.2.122 | set_option    |    3 |    258224000 |  166400000 |            0 |        0 |            0 |            0 |          0 |
| 192.168.2.122 | Register Slave |    1 |      37681000 |    37681000 |            0 |        0 |            0 |            0 |          0 |
+---------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
3 rows in set (0.01 sec)

视图字段含义如下：

statement：显示语句事件名称层级中的最后一部分字符，如：statement/com/Prepare instruments，在statement字段中就显示Prepare
其他字段含义与performance_schema.host_summary_by_statement_latency 视图字段含义相同

PS：限于篇幅原因，本文在编辑时删除了视图的原始语句文本信息（后续文章类似处理），关于视图的原始语句文本信息，可以根据《初相识|全方位认识 sys 系统库》一文中提到的下载链接，下载相应的SQL文件进行查看，另外，有没有发现一些视图查询的内容相似度很高有点傻傻分不清，而且可能还不能完全覆盖我们想要查询的疏忽呢？是的，的确有这个问题，但没有关系，我们只要弄清楚sys 系统库预设了哪些视图，这些视图是怎么编写的，如果真的出现预设视图无法满足我们的要求，那么我们可以基于预设视图的语句文本进行修改(但要注意，不可直接修改sys系统库的原有视图，因为sys预设视图中相当一部分视图有相互调用关系，擅自修改可能会导致内部调用出错)，想怎么写就怎么写，是不是想想就很激动呢！

本期内容就介绍到这里，本期内容参考链接如下：

https://dev.mysql.com/doc/refman/5.7/en/sys-host-summary-by-statement-type.html
https://dev.mysql.com/doc/refman/5.7/en/sys-schema-views.html
https://dev.mysql.com/doc/refman/5.7/en/sys-host-summary-by-file-io.html
https://dev.mysql.com/doc/refman/5.7/en/sys-host-summary-by-file-io-type.html
https://dev.mysql.com/doc/refman/5.7/en/sys-host-summary-by-stages.html
https://dev.mysql.com/doc/refman/5.7/en/sys-host-summary-by-statement-latency.html
https://dev.mysql.com/doc/refman/5.7/en/sys-host-summary.html

"翻过这座山，你就可以看到一片海！"。坚持阅读我们的"全方位认识 sys 系统库"系列文章分享，你就可以系统地学完它。谢谢你的阅读，我们下期不见不散！

| 作者简介

罗小波·数据库技术专家

《千金良方——MySQL性能优化金字塔法则》、《数据生态：MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构，擅长数据库的整体调优，喜好专研开源技术，并热衷于开源技术的推广，在线上线下做过多次公开的数据库专题分享，发表过近100篇数据库相关的研究文章。

全文完。

Enjoy MySQL :)

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

老叶茶馆_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
按 host 分组统计视图 | 全方位认识 sys 系统库

在上一篇《配置表|全方位认识 sys 系统库》中，我们介绍了sys 系统库的配置表，但实际上我们大部分人大多数时候并不需要去修改配置表，直接使用sys 系统库下的视图来获取所需的数据即可...
复制链接

扫一扫