开发者社区> 稀奇古怪> 正文

在Oracle专家眼中,MySQL sys Schema是怎样一种存在?

简介:
+关注继续查看

作者介绍

杨建荣DBAplus社群联合发起人。现就职于搜狐畅游,Oracle ACE-A、YEP成员,超7年数据库开发和运维经验,擅长电信数据业务、数据库迁移和性能调优。持Oracle 10G OCP,OCM,MySQL OCP认证,《Oracle DBA工作笔记》作者

 

sys Schema的初衷 

 

MySQL的数据字典经历了几个阶段的演进,MySQL4.1 提供了information_schema 数据字典,一些基础元数据可以通过SQL来查询得到。


MySQL5.5 提供了performance_schema 性能引擎,可以通过参数performance_schema来开启/关闭,说实话,看起来是有些难度。

 

MySQL5.7 提供了 sys Schema,这个新特性包含了一系列的存储过程、自定义函数以及视图来帮助我们快速的了解系统的元数据信息,当然自MySQL 5.7.7推出以来,让很多MySQL DBA不大适应,而我看到这个sys库的时候,第一感觉是越发和Oracle像了,不是里面的内容像,而是很多设计的方式越来相似。所以按照这种方式,我感觉离AWR这样的工具推出也不远了。

 

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

 

sys Schema的借鉴意义 

 

对于sys Schema,我觉得对DBA来说,有几个地方值得借鉴。

 

  1. 原本需要结合information_schema,performance_schema查询的方式,现在有了视图的方式,把一些优化和诊断信息信息通过视图的方式汇总起来,显示更加直观

  2. sys Schema的有些功能在早期版本可能无从查起,或者很难查询,现在这些因为新版本的功能提炼都做出来了

  3. 如果想好好掌握这些视图的内涵,可以随时查看表的关联关系,对于理解MySQL的运行原理和问题的分析大有帮助,当然这个地方只能点到为止。

 

按照这种情况,没准以后会直接把sys完全独立出来,替代information_schema,performance_schema,没准以后还会出更丰富的功能,类似Oracle中的免费的statspack,还有闭源的AWR,实时的性能数据抓取,自动性能分析和诊断,自动优化任务等,当然只是我的猜想,根据我的认知,Oracle里也是这么走过来的。

 

对于sys Schema的学习,我是基于5.7.13-6这个版本,是用Oracle的眼光来学习的,准备好了吗,老司机开车了。

 

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

 

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

 

20161230100633415.jpg

 

sys下唯一的表 

 

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

 

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

 

20161230100648430.jpg

 

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

 

sys_config的作用其实和Oracle AWR里面的设置非常相似,Oracle中是使用dba_hist_wr_control来得到。

 

20161230100706882.jpg

 

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

 

x$视图 

 

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

 

20161230100717581.jpg

 

x$的视图的定义如下:

 

20161230100725176.jpg

 

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

 

sys下的session视图 

 

我们抽取一个视图来看,就session吧,输出和show processlist命令如出一辙,我们来看看它的实现。使用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` ,通过引用的这些视图其实可以看到也分了很多的层面。

 

而在Oracle中,因为主要是多进程多线程的实现方式(windows平台是单进程多线程),所以会有独立的v$session和v$process两个视图,两者通过内存地址的方式映射,所以在专用服务器模式下,就可以通过进程找到会话,或者通过会话找到进程,对于排查性能问题大有裨益。

 

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这三个方面,提升空间还很大。

 

20161230100734366.jpg

 

等待模型在Oracle中有一种流行的诊断方法论OWI,也就是Oracle Wait Interface。

 

OWI的信息会让调优变得更理性,更符合应用的场景。关于等待事件,Oracle的不同版本中也有着很显著的变化。

 

最初Oracle 7.0中有104个等待事件,8.0中有140多个等待事件,Oracle 8i中有220多个等待事件,9i中有400多个等待事件,10g中有800多个等待事件,11g有1 100多个。随着等待事件的逐步完善,也能够反映出对于问题的诊断粒度越来越细化。

 

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

 

里面InnoDB,schema,statement这三部分是格外需要关注的,我重点来说一下。

 

sys下的InnoDB视图 

 

比如InnoDB部分的视图innodb_lock_waits。

 

我们做个小测试来说明一下。我们开启两个会话。

 

会话1: start transaction;  update test set id=100;

会话2: update test set id=102;

 

这个时候如果在没有sys的情况下,我们需要查看information_schema.innodb_locks和innodb_trx,有的时候还会查看show engine innodb status来得到一些信息佐证。

 

查看Innodb_locks

 

20161230100748404.jpg

 

查看innodb_trx


20161230100801980.jpg

 

面对这些情况,该怎么处理,比如要杀掉会话,可能还会有些模棱两可。

 

我们来看看使用innodb_lock_waits的结果。这个过程语句都给你提供好了,只有1行信息,就是告诉你产生了阻塞,现在可以使用kill的方式终止会话,kill语句都给你提供好了。

 

20161230100813804.jpg

 

当然默认事务还是有一个超时的设置,可以看到确实是update test set id=102阻塞了。已经因为超时取消。

 

> update test set id=102;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

InnoDB相关的视图不多,只有3个,不过都蛮实用的。

 

sys下的schema视图 

 

我们继续看看schema层面的视图,这部分内容就很实用了。

 

schema_auto_increment_columns      
schema_index_statistics
schema_object_overview
schema_redundant_indexes
schema_table_lock_waits 
schema_table_statistics 
schema_table_statistics_with_buffer
schema_tables_with_full_table_scans
schema_unused_indexes

 

如果要查看一个列值溢出的情况,比如是否列的自增值会超出数据类型的限制,这个问题对很多MySQL DBA一直以来都是一个挑战,视图schema_auto_increment_columns就给你包装好了,直接用即可。以下输出略微做了调整。

 

20161230100823828.jpg


如果一个表的索引没有使用到,以前pt工具也可以做一些分析,现在查个视图就搞定了。当然索引的部分,一方面和采样率也有关系,不是一个绝对的结果。查看schema_unused_indexes的结果如下:

 

20161230100831899.jpg

 

如果要查看那些表走了全表扫描,性能情况,可以查看schema_tables_with_full_table_scans,查询结果如下,如果数据量本身很大,这个结果就会被放大,值得关注。

 

20161230100841233.jpg

 

如果查看一些冗余的索引,可以参考 schema_redundant_indexes,删除的SQL语句都给你提供好了。

 

20161230100850182.jpg

 

sys下的statement视图 

 

接下来是statement层面的视图,大体有下面的一些:

 

statement_analysis                            
statements_with_errors_or_warnings            
statements_with_full_table_scans              
statements_with_runtimes_in_95th_percentile   
statements_with_sorting                       
statements_with_temp_tables

 

这部分内容对于分析语句的性能还是尤其有用的。

 

比如查看语句的排序情况,资源使用情况,延时等都会提供出来。

 

20161230100900818.jpg

 

在这里SQL语句做了删减,不过大体能看出语句的信息,执行次数和 延时等都可以看到。

 

对于SQL语句中生成的临时表可以查看statements_with_temp_tables ,比如某一个语句生成的临时表情况,都做了统计。

 

20161230101035410.png
       

sys的备份和重建 

 

最后来说说sys的备份和重建工作,如果查看sys的版本,可以使用视图version来得到。可见是把它当做一个独立的组件一样来维护的。

 

20161230100942660.jpg

 

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

原文发布时间为:2016-12-30

本文来自云栖社区合作伙伴DBAplus

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
18580 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
27723 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,大概有三种登录方式:
12967 0
阿里云服务器安全组设置内网互通的方法
虽然0.0.0.0/0使用非常方便,但是发现很多同学使用它来做内网互通,这是有安全风险的,实例有可能会在经典网络被内网IP访问到。下面介绍一下四种安全的内网互联设置方法。 购买前请先:领取阿里云幸运券,有很多优惠,可到下文中领取。
21933 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
15287 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
19980 0
+关注
119
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载