初相识 | 全方位认识 sys 系统库

妖狐艹你老母 2022-05-18 00:28 182阅读 0赞

前阵子，我们的"全方位认识performance\_schema"系列为大家完整的介绍了performance\_schema系统库。在我们的发布计划中为什么要把performance\_schema放在最前面呢？其中一个原因就是因为它是sys 系统库的数据来源，今天开始，我们将为大家逐步推出“全方位认识 sys 系统库”系列文章，下面我们将为大家带来系列第一篇《初相识|全方位认识 sys 系统库》，请跟随我们一起开始 sys 系统库的系统学习之旅吧～

PS：本系列基于MySQL 5.7.18 版本整理

### **|  初识sys系统库** ###

**1. sys系统库使用基础环境**

在使用sys系统库之前，你需要确保你的数据库环境满足如下条件：

1）sys系统库支持MySQL 5.6或更高版本，5.5.x及其以下版本不支持；

2）因为sys系统库提供了一些代替直接访问performance\_schema的视图，所以必须启用performance\_schema(performance\_schema系统参数设置为ON)之后sys系统库的大部分功能才能正常使用；

3）要完全访问sys系统库，用户必须具有以下权限：

\* 对所有sys表和视图具有SELECT权限   
\* 对所有sys存储过程和函数具有EXECUTE权限   
\* 对sys\_config表具有INSERT、UPDATE权限   
\* 对某些特定的sys系统库存储过程和函数需要额外权限，如，ps\_setup\_save()存储过程，需要临时表相关的权限

4）还有sys系统库执行访问的对象相关的权限：

\* 任何被sys系统库访问的performance\_schema表需要有SELECT权限，如果要使用sys系统库对performance\_schema相关表执行更新，则需要performance\_schema相关表的UPDATE权限   
\* INFORMATION\_SCHEMA.INNODB\_BUFFER\_PAGE表的PROCESS

5）如果要充分使用sys系统库的功能，则必须启用某些performance\_schema的instruments和consumers，如下：

\* 所有wait instruments   
\* 所有stage instruments   
\* 所有statement instruments   
\* 对于所启用的类型事件的instruments，还需要启用对应类型的consumers(xxx\_current和xxx\_history\_long)，要了解某存储过程具体做了什么事情可能通过show create procedure procedure\_name;语句查看

您可以使用sys系统库本身来启用所有需要的instruments和consumers：

\* 启用所有wait instruments：CALLsys.ps\_setup\_enable\_instrument('wait');

\* 启用所有stage instruments：CALLsys.ps\_setup\_enable\_instrument('stage');

\* 启用所有statement instruments：CALLsys.ps\_setup\_enable\_instrument('statement');

\* 启用所有事件类型的current表：CALLsys.ps\_setup\_enable\_consumer('current');

\* 启用所有事件类型的history\_long表：CALLsys.ps\_setup\_enable\_consumer('history\_long');

\* 注意：performance\_schema的默认配置就可以满足sys系统库的大部分数据收集功能。启用上述所提及的所有instruments和consumers会对性能产生一定影响，因此最好仅启用所需的配置。如果你在启用了一些默认配置之外的配置，则可以使用存储过程：CALLsys.ps\_setup\_reset\_to\_default(TRUE); 来快速恢复到performance\_schema的默认配置

PS：对于以上繁杂的权限要求，通常创建一个具有管理员权限的账号即可，当然如果你有明确的需求，那另当别论，但sys系统库通常都是提供给专业的DBA人员排查一些特定问题使用的，其下所涉及的各项查询或多或少都会对性能有一定影响（主要体现在performance\_schema功能实现的性能开销），在不明需求的情况下，不建议开放这些功能来作为常规的监控手段使用。

**2. sys系统库初体验**

当你使用了use语句切换默认数据库，那么就可以直接使用sys系统库下的视图名称进行查询，就像查询某个库下的表一样操作，如下：

# version视图可以查看sys 系统库和mysql server的版本号
    mysql> USE sys;
    mysql> SELECT * FROM version;
    + ------------- + ----------------- +
    | sys_version | mysql_version |
    + ------------- + ----------------- +
    | 1.5.0 | 5.7.9-debug-log |
    + ------------- + ----------------- +

也可以使用db\_name.view\_name、db\_name.procedure\_name、db\_name.func\_name等方式在不指定默认数据库的情况下访问sys 系统库中的对象(这叫做名称限定对象引用)，如下：

mysql> SELECT * FROM sys.version;
    + ------------- + ----------------- +
    | sys_version | mysql_version |
    + ------------- + ----------------- +
    | 1.5.0 | 5.7.9-debug-log |
    + ------------- + ----------------- +

PS：下文中的示例中，对于sys 系统库的访问都是假定指定了默认数据库为sys 系统库。

sys 系统库下包含许多视图，它们以各种方式对performance\_schema表进行聚合计算展示。这些视图中大部分都是成对出现，两个视图名称相同，但有一个视图是带'x$'字符前缀的，例如：host\_summary\_by\_file\_io和x$host\_summary\_by\_file\_io，代表按照主机进行汇总统计的文件I/O性能数据，两个视图访问数据源是相同的，但是创建视图的语句中，不带x$的视图是把相关数值数据经过单位换算再显示的(显示为毫秒、秒、分钟、小时、天等)，带x$前缀的视图显示的是原始的数据(皮秒)，如下：

# x$host_summary_by_file_io视图汇总数据，显示未格式化的皮秒单位延迟时间，没有x$前缀字符的视图输出的信息经过单位换算之后可读性更高
    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 |
    +------------+-------+---------------+

要查看sys 系统库对象定义语句，可以使用适当的SHOW语句或INFORMATION\_SCHEMA库查询。例如，要查看session视图和format\_bytes()函数的定义，可以使用如下语句：

mysql> SHOW CREATE VIEW session;
    mysql> SHOW CREATE FUNCTION format_bytes;

然而，这些语句文本是经过格式化的，可读性比较差。要查看更易读的格式对象定义语句，可以访问sys 系统库开发网站https://github.com/mysql/mysql-sys上的各个.sql文件，或者使用mysqldump与mysqlpump工具导出sys库，默认情况下，mysqldump和mysqlpump都不会导出sys 系统库。要生成包含sys 系统库的导出文件，可以使用如下命令显式指定sys 系统库（虽然可以导出视图定义，但是与原始的定义语句相比仍然缺失了相当一部分内容，只是可读性比直接show create view要好一些）：

mysqldump --databases --routines sys> sys_dump.sql
    mysqlpump sys> sys_dump.sql

如果要重新导入sys 系统库，可以使用如下命令：

mysql < sys_dump.sql

**3. sys 系统库的进度报告功能**

从MySQL 5.7.9开始，sys 系统库视图提供查看长时间运行的事务的进度报告，通过processlist和session以及x$前缀的视图进行查看，其中processlist包含了后台线程和前台线程当前的事件信息，session不包含后台线程和command为Daemon的线程，如下：

processlist
    session
    x$processlist
    x$session

session视图是直接调用processlist视图过滤了后台线程和command为Daemon的线程（所以两个视图输出结果的字段相同），而processlist线程联结查询了threads、events\_waits\_current、events\_stages\_current、events\_statements\_current、events\_transactions\_current、sys.x$memory\_by\_thread\_by\_current\_bytes、session\_connect\_attrs表，so，需要打开相应的instruments和consumers，否则谁没打开谁对应的信息字段列就为NULL，对于trx\_state字段为ACTIVE的线程，progress可以输出百分比进度信息(只有支持进度的事件才会被统计并打印进来)

查询示例

# 查看当前正在执行的语句进度信息
    admin@localhost : sys 06:57:21> select * from session where conn_id!=connection_id() and trx_state='ACTIVE'\G;
    *************************** 1. row ***************************
                thd_id: 47
              conn_id: 5
                  user: admin@localhost
                    db: sbtest
              command: Query
                state: alter table (merge sort)
                  time: 29
    current_statement: alter table sbtest1 add index i_c(c)
    statement_latency: 29.34 s
              progress: 49.70
          lock_latency: 4.34 ms
        rows_examined: 0
            rows_sent: 0
        rows_affected: 0
            tmp_tables: 0
      tmp_disk_tables: 0
            full_scan: NO
        last_statement: NULL
    last_statement_latency: NULL
        current_memory: 4.52 KiB
            last_wait: wait/io/file/innodb/innodb_temp_file
    last_wait_latency: 369.52 us
                source: os0file.ic:470
          trx_latency: 29.45 s
            trx_state: ACTIVE
        trx_autocommit: YES
                  pid: 4667
          program_name: mysql
    1 row in set (0.12 sec)
    # 查看已经执行完的语句相关统计信息
    admin@localhost : sys 07:02:21> select * from session where conn_id!=connection_id() and trx_state='COMMITTED'\G;
    *************************** 1. row ***************************
                thd_id: 47
              conn_id: 5
                  user: admin@localhost
                    db: sbtest
              command: Sleep
                state: NULL
                  time: 372
    current_statement: NULL
    statement_latency: NULL
              progress: NULL
          lock_latency: 4.34 ms
        rows_examined: 0
            rows_sent: 0
        rows_affected: 0
            tmp_tables: 0
      tmp_disk_tables: 0
            full_scan: NO
        last_statement: alter table sbtest1 add index i_c(c)
    last_statement_latency: 1.61 m
        current_memory: 4.52 KiB
            last_wait: idle
    last_wait_latency: Still Waiting
                source: socket_connection.cc:69
          trx_latency: 1.61 m
            trx_state: COMMITTED
        trx_autocommit: YES
                  pid: 4667
          program_name: mysql
    1 row in set (0.12 sec)

对于stage事件进度报告要求必须启用events\_stages\_current consumers，启用需要查看进度相关的instruments。例如：

stage/sql/Copying to tmp table
    stage/innodb/alter table (end)
    stage/innodb/alter table (flush)
    stage/innodb/alter table (insert)
    stage/innodb/alter table (log apply index)
    stage/innodb/alter table (log apply table)
    stage/innodb/alter table (merge sort)
    stage/innodb/alter table (read PK and internal sort)
    stage/innodb/buffer pool load

对于不支持进度的stage 事件，或者未启用所需的instruments或consumers的stage事件，则对应的进度信息列显示为NULL。

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

https://dev.mysql.com/doc/refman/5.7/en/sys-schema-progress-reporting.html

https://dev.mysql.com/doc/refman/5.7/en/sys-schema-prerequisites.html

https://dev.mysql.com/doc/refman/5.7/en/sys-schema-usage.html

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

### **| 作者简介** ###

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

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