用Oracle的眼光来学习MySQL 5.7的sys(上)(r11笔记第24天)

sys的初衷

MySQL 5.7的sys自从推出以来,整体的反响似乎没有预期的那么高,而我看到这个sys库的时候,第一感觉是越发和Oracle像了,不是里面的内容像,而是很多设计的方式越来相似。所以按照这种方式,我感觉离AWR这样的工具推出也不远了。

对于实时全面的抓取性能信息,MySQL依旧还在不断进步的路上。因为开源,所以有很多非常不错的工具,产品推出。myawr算是其中的一个,现在看来当初的设计方式和现在sys库很有相似之处,感兴趣的可以自行搜索查看。

所以对于sys库的学习,我是用Oracle的眼光来学习的,准备好了吗,老司机开车了。

我的总结是基于5.7.13-6这个版本,首先sys是对于information_schema,performance_schema的一个补充,在sys里面是做了整合,把一些信息通过视图的方式汇总起来,让很多信息的展现更合理一些。

化繁为简,sys下的对象分布情况

sys下的对象分布其实信息量很大,除了我们关心关心的视图和表意外,还有函数,存储过程和触发器。这些信息可以通过sys下的视图schema_object_overview来查看。

> select * from schema_object_overview where db='sys';
+-----+---------------+-------+
| db  | object_type   | count |
+-----+---------------+-------+
| sys | VIEW          |   100 |
| sys | BASE TABLE    |     1 |
| sys | INDEX (BTREE) |     1 |
| sys | TRIGGER       |     2 |
| sys | FUNCTION      |    21 |
| sys | PROCEDURE     |    26 |
+-----+---------------+-------+
6 rows in set (0.10 sec)

sys下唯一的表

如果你观察仔细其实会发现里面的table只有一个,那就是sys_config,使用命令show tables显示出来的除了这个表都是视图。

这个视图有什么特别之处呢。

> select * from sys_config;
+--------------------------------------+-------+---------------------+--------+
| variable                             | value | set_time            | set_by |
+--------------------------------------+-------+---------------------+--------+
| diagnostics.allow_i_s_tables         | OFF   | 2016-11-09 11:21:12 | NULL   |
| diagnostics.include_raw              | OFF   | 2016-11-09 11:21:12 | NULL   |
| ps_thread_trx_info.max_length        | 65535 | 2016-11-09 11:21:12 | NULL   |
| statement_performance_analyzer.limit | 100   | 2016-11-09 11:21:12 | NULL   |
| statement_performance_analyzer.view  | NULL  | 2016-11-09 11:21:12 | NULL   |
| statement_truncate_len               | 64    | 2016-11-09 11:21:12 | NULL   |
+--------------------------------------+-------+---------------------+--------+
6 rows in set (0.00 sec)

可以看到里面是一个基础参数的设置,比如一些范围,基数的设置。而且值得一提的是这个表里设置了几个触发器,对这个表的DML操作都会触发里面的数据级联变化。

Oracle中类似的实现

这个和awr里面的设置非常相似,Oracle中是使用dba_hist_wr_control来得到。

select *from dba_hist_wr_control      DBID             SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
3645037571 +00000 01:00:00.0 +00008 00:00:00.0    DEFAULT

然后我们继续查看,还是使用show tables来看,会看到整个sys下的表/视图有101个,其中x$开头的对象有48个,所以简单换算一下,里面的表/视图有53个。

x$视图

x$的视图是什么意思,这个通过Oracle的角度来看,就很容易理解,不知道这样类比对不对,在Oracle中,数据字典分为两种类型,一类是数据字典表,像dba_tables这样的,基表都是tab$这种的表,数据是存放在sys下的,这些信息在MySQL中就有些类似information_schema下的数据字典,而另外一类数据字典是动态性能视图,Oracle是以v$开头的,比如v$session,它的基表是x$开头的“内存表”,在MySQL sys中也是类似的意思,只是这些信息MySQL都毫无保留的开放出来了。按照官方的说法,是x$的信息是没有经过格式化的,比如下面的两个视图对比。

# mysql -e "use sys; desc waits_global_by_latency\g"
Logging to file '/home/mysql/query.log'
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| events        | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | text                | YES  |     | NULL    |       |
| avg_latency   | text                | YES  |     | NULL    |       |
| max_latency   | text                | YES  |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+

x$的视图的定义如下:

# mysql -e "use sys; desc x\$waits_global_by_latency\g"
Logging to file '/home/mysql/query.log'
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| events        | varchar(128)        | NO   |     | NULL    |       |
| total         | bigint(20) unsigned | NO   |     | NULL    |       |
| total_latency | bigint(20) unsigned | NO   |     | NULL    |       |
| avg_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
| max_latency   | bigint(20) unsigned | NO   |     | NULL    |       |
+---------------+---------------------+------+-----+---------+-------+

可以看到数据类型也有一些差别。如果是时间字段,在x$视图中可能精度是picosecond(皮秒,万亿分之一秒),而在普通视图中,就会显示为秒。

sys下的session视图

我们抽取一个视图来看,就session吧,使用show create view session可以看到引用的基表为`sys`.`processlist`,我们继续查看sys.processlist,可以发现它的基表是performance_schema下的`events_waits_current`,`events_stages_current`,`events_statements_current`,`events_transactions_current`,`session_connect_attrs`和sys下的基表`x$memory_by_thread_by_current_bytes` ,通过引用的这些视图其实可以看到也分了很多的层面。

sys下的视图分类

sys下的视图分了哪些层面呢。我简单来总结一下,大体分为一下几个层面

host_summary,这个是服务器层面的,比如里面的视图host_summary_by_file_io

user_summary,这个是用户层级的,比如里面的视图user_summary_by_file_io

innodb,这个是innodb层面的,比如innodb_buffer_stats_by_schema

io,这个是I/O层的统计,比如视图 io_global_by_file_by_bytes

memory,关于内存的使用情况,比如视图memory_by_host_by_current_bytes

schema,关于schema级别的统计信息,比如schema_table_lock_waits

session,关于会话级别的,这个视图少一些,就两个,session和session_ssl_status

statement,关于语句级别的,比如statements_with_errors_or_warnings

wait,关于等待的,这个还是处于起步阶段,等待模型有待完善,目前只有基于io/file, lock/table, io/table这三个方面,提升空间还很大。

> select *from wait_classes_global_by_avg_latency\g
+-----------------+--------------+---------------+-------------+-------------+-------------+
| event_class     | total        | total_latency | min_latency | avg_latency | max_latency |
+-----------------+--------------+---------------+-------------+-------------+-------------+
| wait/io/file    |     12228640 | 25.66 m       | 0 ps        | 125.88 us   | 5.96 s      |
| wait/lock/table |      1320560 | 2.64 s        | 125.25 ns   | 2.00 us     | 228.54 us   |
| wait/io/table   | 144689737016 | 22.78 h       | 114.23 ns   | 566.77 ns   | 3.92 s      |
+-----------------+--------------+---------------+-------------+-------------+-------------+

这个sys的使用其实还是比较灵活的,在5.6及以上版本都可以,是完全独立的。和Oracle里面的statspack,awr非常相似。

sys的备份和重建

如果查看sys的版本,可以使用视图version来得到。可见是把它当做一个独立的组件一样来维护的。

[sys]> select *from version;
+-------------+---------------+
| sys_version | mysql_version |
+-------------+---------------+
| 1.5.0       | 5.7.13-6-log  |
+-------------+---------------+

如果要导出,可以使用 mysqlpump sys > sys_dump.sql 或者mysqldump --databases --routines sys > sys_dump.sql来得到sys的创建语句,如果需要重建则更简单 mysql<sys_dump.sql 即可。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2016-12-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Jerry的SAP技术分享

SAP CRM Survey调查问卷的存储模型

数据库表CRM_SVY_DB_SVS,通过如下的函数CRM_SVY_DB_SVS_CREATE插入:

1643
来自专栏Java后端技术栈

MySQL数据库开发常见问题及几点优化!

MySQL数据库是被广泛应用的关系型数据库,其体积小、支持多处理器、开源并免费的特性使其在Internet中小型网站中的使用率尤其高。在使用 MySQL的过程中...

781
来自专栏杨建荣的学习笔记

复杂SQL性能优化的剖析(一)(r11笔记第36天)

今天本来是处理一个简单的故障,但是发现是一环套一环,花了我快一天的时间。 开始是早上收到一条报警: 报警内容: CPUutilization is too hi...

36212
来自专栏文渊之博

列存储索引1:初识列存储索引

     2012以后提供了一种不同于传统B树结构的索引类型,就是内存列存储索引。这种索引应用了一种基于列的存储模式,也是一种新的查询执行的批处理模式,并且为特...

2245
来自专栏james大数据架构

聚合索引(clustered index) / 非聚合索引(nonclustered index)

以下我面试经常问的2道题..尤其针对觉得自己SQL SERVER 还不错的同志.. 呵呵 很难有人答得好.. 各位在我收集每个人擅长的东西时,大部分都把SQL...

5435
来自专栏数据和云

巧用复合索引,有效降低系统IO

我们知道索引至关重要,合理的索引使用能够在很大程度上改善数据库的性能。然而很多人都会走入这样一个误区:走索引的SQL语句的性能一定比全表扫描好。真的是这样吗?今...

2979
来自专栏程序你好

互联网MySQL数据库应用潜规则

1492
来自专栏令仔很忙

手把手教你-----巧用Excel批量生成SQL语句,处理大量数据

在做系统或者做项目的时候,经常会遇到这样的要求:用户给我们发过来一些数据,要求我们把这些数据导入到数

3113
来自专栏数据和云

【云和恩墨大讲堂】复合索引与绑定变量

讲师简介 ? 邓秋爽(小鱼) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting 今晚的...

3616
来自专栏Java架构沉思录

为什么我建议你这样实现MySQL分页

之前分享了关于MySQL分页实现方案的文章《如何优雅地实现分页查询》,有些读者觉得写得太浅显了,今天我们就继续探讨这个话题,当然由于能力有限,这篇文章也未必能够...

1462

扫码关注云+社区

领取腾讯云代金券