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

PS:本系列基于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系统库下的视图名称进行查询,就像查询某个库下的表一样操作,如下:


也可以使用db_name.view_name、db_name.procedure_name、db_name.func_name等方式在不指定默认数据库的情况下访问sys 系统库中的对象(这叫做名称限定对象引用),如下:


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

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


要查看sys 系统库对象定义语句,可以使用适当的SHOW语句或INFORMATION_SCHEMA库查询。例如,要查看session视图和format_bytes()函数的定义,可以使用如下语句:


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


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


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

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


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可以输出百分比进度信息(只有支持进度的事件才会被统计并打印进来)

查询示例


对于stage事件进度报告要求必须启用events_stages_current consumers,启用需要查看进度相关的instruments。例如:


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

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

================================================================================================================

================================================================================================================

mysql5.7增加了sys 系统数据库,通过这个库可以快速的了解系统的元数据信息
这个库确实可以方便DBA发现数据库的很多信息,解决性能瓶颈都提供了巨大帮助
这个库在mysql5.7中是默认存在的,在mysql5.6版本以上可以手动导入,数据库包请在github自行查找
这个库包括了哪些内容?
这个库是通过视图的形式把information_schema 和performance_schema结合起来,查询出更加令人容易理解的数据
存储过程可以可以执行一些性能方面的配置,也可以得到一些性能诊断报告内容
存储函数可以查询一些性能信息
分析每个视图和表之前先说明一下:关于带不带x$,去掉x$同名的视图他们的数据是相同的,区别在于不带x$的单位更加符合直接阅读经过了转换,而带x$是为了某些工具存在而使用的原始单位(多数应该是mysql默认的)
下面就结合mysql官方手册来详细分析sys库

1.表
1.1 sys_config 表
这是在这个系统库上存在的唯一一个表了
先看看表结构
CREATE TABLE `sys_config` (
`variable` varchar(128) NOT NULL,
`value` varchar(128) DEFAULT NULL,
`set_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`set_by` varchar(128) DEFAULT NULL,
PRIMARY KEY (`variable`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

variable 配置选项名称

value 配置选项值
set_time 该行配置修改的时间
set_by 该行配置信息修改者,如果从被安装没有修改过,那么这个数据应该为NULL
表中默认数据为
variable
value
set_time
set_by
diagnostics.allow_i_s_tables
OFF
2015-11-20 16:04:38
root@localhost
diagnostics.include_raw
OFF
2015-11-20 16:04:38
root@localhost
statement_performance_analyzer.limit
100
2015-11-20 16:04:38
root@localhost
statement_performance_analyzer.view
2015-11-20 16:04:38
root@localhost
statement_truncate_len
64
2016-01-22 17:00:16
root@localhost
以上值的会话变量为@sys.+表中variable字段,譬如:@sys.statement_truncate_len
可以set @sys.statement_truncate_len=32 临时改变值,在会话中会一直使用这个值,如果想要恢复使用表的默认值,只需要将这个会话值设置为null;set @sys.statement_truncate_len=null;
diagnostics.allow_i_s_tables
diagnostics.include_raw
这两个值默认为OFF ,前者如果开启表示允许diagnostics() 存储过程执行扫描information_schema.tables 表,如果表很多,那么可能会很耗性能,后者开启将会从metrics 视图输出未加工处理的数据 。diagnostics() 具体内容见下面对diagnostics()的解释。
statement_performance_analyzer.limit
视图在没有加limit限制时,返回的最大行数
statement_performance_analyzer.view
(略)
以上参数为mysql5.7.9加入
statement_truncate_len
通过format_statement()函数返回值的最大长度
这个表非默认选项还有一个@sys.debug参数
可以手动加入
INSERT INTO sys_config (variable, value) VALUES('debug', 'ON');
UPDATE sys_config SET value = 'OFF' WHERE variable = 'debug';
SET @sys.debug = NULL;
具体内容请参考官方文档,此处不做介绍
关于这个表有两个触发器
1.1.1 sys_config_insert_set_user触发器
如果加入新行通过insert语句,那么这个触发器会把set_by列设置为当前操作者
1.1.2 sys_config_update_set_user触发器
如果加入新行通过update语句,那么这个触发器会把set_by列设置为当前操作者
2.视图
以下部分只介绍不包含x$的视图内容
2.1 host_summary (主机概要)
有如下列:
? host
监听连接过的主机
? statements
当前主机执行的语句总数
? statement_latency
语句等待时间(延迟时间)
? statement_avg_latency
执行语句平均延迟时间
? table_scans
表扫描次数
? file_ios
io时间总数
? file_io_latency
文件io延迟
? current_connections
当前连接数
? total_connections
总链接数
? unique_users
该主机的唯一用户数
? current_memory
当前账户分配的内存
? total_memory_allocated
该主机分配的内存总数
2.2 The host_summary_by_file_io_type
?host
主机
?event_name
IO事件名称
?total
该主机发生的事件
?total_latency
该主机发生IO事件总延迟时间
?max_latency
该主机IO事件中最大的延迟时间
2.3 The host_summary_by_file_io
?host
主机
?ios
IO事件总数
?io_latency
IO总的延迟时间
2.4 The host_summary_by_stages
? host
主机
? event_name
stage event名称
? total
stage event发生的总数
? total_latency
stage event总的延迟时间
? avg_latency
stage event平均延迟时间
2.5 The host_summary_by_statement_latency
? host
主机
? total
这个主机的语句总数
? total_latency
这个主机总的延迟时间
? max_latency
主机最大的延迟时间
? lock_latency
等待锁的锁延迟时间
? rows_sent
该主机通过语句返回的总行数
? rows_examined
在存储引擎上通过语句返回的行数
? rows_affected
该主机通过语句影响的总行数
? full_scans
全表扫描的语句总数
2.6 The host_summary_by_statement_type
? host
主机
? statement
最后的语句事件名称
? total
sql语句总数
? total_latency
sql语句总延迟数
? max_latency
最大的sql语句延迟数
? lock_latency
锁延迟总数
? rows_sent
语句返回的行总数
? rows_examined
通过存储引擎的sql语句的读取的总行数
? rows_affected
语句影响的总行数
? full_scans
全表扫描的语句事件总数
2.7 The innodb_buffer_stats_by_schema
这个表是通过数据库统计innodb引擎的innodb缓存
? object_schema
数据库名称
? allocated
分配给当前数据库的总的字节数
? data
分配给当前数据库的数据字节数
? pages
分配给当前数据库的总页数
? pages_hashed
分配给当前数据库的hash页数
? pages_old
分配给当前数据库的旧页数
? rows_cached
当前数据库缓存的行数
2.8 The innodb_buffer_stats_by_table
这个表是通过每个表innodb引擎的innodb缓存
? object_schema
数据库名称
? object_name
表名称
? allocated
分配给表的总字节数
? data
分配该表的数据字节数
? pages
分配给表的页数
? pages_hashed
分配给表的hash页数
? pages_old
分配给表的旧页数
? rows_cached
表的行缓存数
2.9 The innodb_lock_waits
这个表其实从视图的语句来看就是information_schema这个数据库中的innodb_locks、innodb_trx这两个表的整合,能够更清晰的显示当前实例的锁情况
? wait_started
锁等待发生的时间
? wait_age
锁已经等待了多长时间
? wait_age_secs
以秒为单位显示锁已经等待的时间(5.7.9中添加此列)
? locked_table
被锁的表
? locked_index
被锁住的索引
? locked_type
锁类型
? waiting_trx_id
正在等待的事务ID
? waiting_trx_started
等待事务开始的时间
? waiting_trx_age
已经等待事务多长时间
? waiting_trx_rows_locked
正在等待的事务被锁的行数量
? waiting_trx_rows_modified
正在等待行重定义的数量
? waiting_pid
正在等待事务的线程id
? waiting_query
正在等待锁的查询
? waiting_lock_id
正在等待锁的ID
? waiting_lock_mode
等待锁的模式
? blocking_trx_id
阻塞等待锁的事务id
? blocking_pid
正在锁的线程id
? blocking_query
正在锁的查询
?blocking_lock_id
正在阻塞等待锁的锁id.
?blocking_lock_mode
阻塞锁模式
? blocking_trx_started
阻塞事务开始的时间
? blocking_trx_age
阻塞的事务已经执行的时间
? blocking_trx_rows_locked
阻塞事务锁住的行的数量
? blocking_trx_rows_modified
阻塞事务重定义行的数量
? sql_kill_blocking_query
kill 语句杀死正在运行的阻塞事务
在mysql5.7.9中被加入
? sql_kill_blocking_connection
kill 语句杀死会话中正在运行的阻塞事务
在mysql5.7.9中被加入
2.10 The io_by_thread_by_latency
这个视图主要信息是通过IO的消耗展示IO等待的时间
? user
对于当前线程来说,这个值是线程被分配的账户,对于后台线程来讲,就是线程的名称
? total
IO事件的总数
? total_latency
IO事件的总延迟
? min_latency
单个最小的IO事件延迟
? avg_latency
平均IO延迟
? max_latency
最大IO延迟
? thread_id
线程ID
? processlist_id
对于当前线程就是此时的ID,对于后台就是null

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

本着资源共享的原则,欢迎各位朋友在此基础上完善,并进一步分享,让我们的实现更加优雅。如果有任何疑问和需要进一步交流可以留言沟通 Testner创始人(testner.club) Sea