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

前阵子,我们的"全方位认识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

| 作者简介

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

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

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏思考的代码世界

Python网络数据采集之存储数据|第04天

存储媒体文件有两种主要的方式:只获取文件 URL 链接,或者直接把源文件下载下来。

4657
来自专栏数据和云

Library Cache优化与SQL游标

? 冷菠 冷菠,网名悠然(个人主页http://www.orasky.net),资深DBA,著有《Oracle高性能自动化运维》,有近10年的数据库运维、团队...

2905
来自专栏不想当开发的产品不是好测试

mysql 删表引出的问题

背景 将测试环境的表同步到另外一个数据库服务器中,但有些表里面数据巨大,(其实不同步该表的数据就行,当时没想太多),几千万的数据!! 步骤 1. 既然已经把数据...

2937
来自专栏铭毅天下

干货 | Elasticsearch索引生命周期管理探索

Elasticsearch上海Meetup中ebay工程师提了索引生命周期管理的概念。的确,在Demo级别的验证阶段我们数据量比较小,不太需要关注索引的生命周期...

2802
来自专栏数据和云

忘记SQL Server 管理员密码不可怕,学会这招就够了

作者 | 邹建,资深数据库专家,精通各项 SQL Server 技术,具有丰富的管理、维护、优化能力以及业务应用经验。他一直热心于技术知识的分享、传播,持续活跃...

2473
来自专栏idba

死锁案例之十

死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋...

1082
来自专栏Netkiller

数据库与图片完美解决方案

目录 1. 背景 2. 解决思路 3. 解决方案 4. plugin 的开发与使用 5. 在事务中使用该插件 6. 通过触发器调用图片处理函数 1. 背景 我以...

2945
来自专栏Debian社区

Postgres 10 开发者新特性

目前非常流行的RDBMS PostgresSQL已经在几周前发布了它的第10个版本。由于Postgres的可靠性、节约成本、成熟,当然还有它的开源,已经21岁的...

1202
来自专栏Java技术分享圈

杨老师课堂之JavaEE三大框架Hibernate入门第一课

741
来自专栏MySQL实战分享

【MySQL经典案例分析】 Waiting for table metadata lock

2018年某个周末,接到连续数据库的告警,看到too many connection的报错信息,基本上可以把问题定位在...

9506

扫码关注云+社区

领取腾讯云代金券