ITPub博客

首页 > 数据库 > MySQL > MySQL默认数据库之sys库

MySQL默认数据库之sys库

原创 MySQL 作者:lhrbest 时间:2019-07-21 15:26:49 0 删除 编辑

MySQL默认数据库之sys库


内容参考链接如下:

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

更多内容参考: http://blog.itpub.net/26736162/viewspace-2143988/



本系列基于MySQL 5.7.18 版本整理

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表,所以,需要打开相应的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。






About Me

........................................................................................................................

● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除

● 本文在itpub、博客园、CSDN和个人微 信公众号( xiaomaimiaolhr )上有同步更新

● 本文itpub地址: http://blog.itpub.net/26736162

● 本文博客园地址: http://www.cnblogs.com/lhrbest

● 本文CSDN地址: https://blog.csdn.net/lihuarongaini

● 本文pdf版、个人简介及小麦苗云盘地址: http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答: http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址: http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● QQ群号: 230161599 (满) 、618766405

● 微 信群:可加我微 信,我拉大家进群,非诚勿扰

● 联系我请加QQ好友 646634621 ,注明添加缘由

● 于 2019-07-01 06:00 ~ 2019-07-31 24:00 在西安完成

● 最新修改时间:2019-07-01 06:00 ~ 2019-07-31 24:00

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

........................................................................................................................

小麦苗的微店 https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

小麦苗出版的数据库类丛书 http://blog.itpub.net/26736162/viewspace-2142121/

小麦苗OCP、OCM、高可用网络班 http://blog.itpub.net/26736162/viewspace-2148098/

小麦苗腾讯课堂主页 https://lhr.ke.qq.com/

........................................................................................................................

使用 微 信客户端 扫描下面的二维码来关注小麦苗的微 信公众号( xiaomaimiaolhr )及QQ群(DBA宝典)、添加小麦苗微 信, 学习最实用的数据库技术。

........................................................................................................................

欢迎与我联系

 

 



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2651254/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1357
  • 访问量
    8267071