mysql 5.7新数据库sys解析（一）

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