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

在上一篇《配置表 | 全方位认识 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

| 作者简介

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

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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏PingCAP的专栏

TiDB 源码阅读系列文章(二)初识 TiDB 源码

本文为 TiDB 源码阅读系列文章的第二篇,第一篇文章介绍了 TiDB 整体的架构,知道 TiDB 有哪些模块,分别是做什么的,从哪里入手比较好,哪些可以忽略,...

805110
来自专栏更流畅、简洁的软件开发方式

【自然框架】之通用权限(六):权限到节点

      “直率没有错,但是也要考虑对方的承受能力呀!对方都承受不了了,你还直率,那就是你的错了!”  ——我的名言,呵呵。     ===========...

20050
来自专栏owent

给客户端写得LRU缓存

由于我们的客户端的元素和资源比较多,cocos框架的各种库质量参差不齐,导致了有些地方加载速度实在很慢。并且没有一个统一的内存管理机制导致了整个内存占用不太好控...

12110
来自专栏玄魂工作室

老司机教你下载tumblr上视频和图片的正确姿势

本文面向初学者。 很多同学问我:“我非常想学Python编程,但是找不到兴趣点”。 还有的同学呢,找到了很好的兴趣点,但是无从下手,“玄魂老师,我想下载tumb...

1.4K70
来自专栏kl的专栏

java解析xlxs、xls文件的利器sep4J

今天分享一个解析xls文件的项目,封装了Apache poi工具,想学习源码的可以看下面项目地址。此项目提供了强大的数据映射api接口,支持xls行数据映射到j...

35950
来自专栏智能大石头

XCode之第一次亲密接触

首先得说明,本教程仅用于让第一次接触XCode的朋友了解XCode,不具有任何别的实际意义,真正的项目也不会采取这种开发方式,而采用更先进、快速而强大的开发方式...

26690
来自专栏FreeBuf

Flash XSS检测脚本的简单实现

前言 这里主要是讲如何快速扫描到有问题的flash文件,用于批量,有时候很笨的方法也会有奇效,下面记录一下在实现过程中的一些思路和遇到的一些坑。 ? 第三方插件...

31850
来自专栏蓝天

super-smack

我有个办法,不过不是用LR,是用super-smack,如果只对数据库进行抗压力测试,应该管用。 Super-smack 现在是1.3版,源码下载地址如下: h...

10910
来自专栏pangguoming

Ubuntu安装nodeJS

安装环境 ubuntu12.04 64bit  nodejs-v0.8.14.tar.gz  Node.js是一个基于google v8+javascript的...

51740
来自专栏数据小魔方

构建自己的地理信息空间数据库及与客户端简单交互

最近研究了下postgresql数据库及其空间地理信息拓展插件——postgis。

23220

扫码关注云+社区

领取腾讯云代金券