社区首页 >专栏 >MySQL运维的一款利器sys schema

MySQL运维的一款利器sys schema

jeanron100

发布于 2018-03-22 07:14:42

1.1K00

代码可运行

文章被收录于专栏：杨建荣的学习笔记杨建荣的学习笔记

运行总次数：0

代码可运行

MySQL运维中有很多的工具，就好比你进了一个杂货铺，各种运维中的小技巧几乎都有一些小工具，走一圈发现真是琳琅满目，可见MySQL充分享受了开源社区的红利。

而比较特别的一点是MySQL社区中，官方提供的工具在以前不会成为市面上的拳头产品，有些甚至维护一段之后就没有深入维护了。所以走一圈发现，MySQL很多实用成熟的工具都来自于社区，而官方推出的一些工具不是因为企业版的限制就是功能的不成熟而被有限使用。

当然不一而论，在早些年我可以说这些话，但是在5.7开始，你会明显发现官方的投入已经很大了，官方推出的社区版迭代很快，已经早早超越了两个流行的分支Percona和MariaDB,尤其是MGR的推出，算是让MySQL在这方面有了不少的亮点和话语权。

我们继续回到工具，还是工具，工具是DBA的一把瑞士军刀，其实对我们来说，我们也不希望有一大把的小工具，如果有太多的工具，太多的瑞士军刀，我们的行李箱就会很沉重，哪些都需要，哪些都要单独安装，所以我们有时候希望做减法。如果官方有而且做得不错，那毫无疑问是我们首选的方案了。

所以在此我要推荐MySQL 的sys schema。这是在5.7推出的一个特性。算是对以前的performance_schema和information_schema的一个抽象层。

有的同学可能会问，这都是MySQL 5.7的特性了，我们的很多环境使用的依旧是5.5,5.6，这个特性对我们来说有什么意义，如果只是这样想，只能说明你只是意识到了这个问题而已，但是对于解决问题没有实质性建议。

我们来简单说下sys schema里面的一些小细节。

之前发表在DBAplus社群的一篇文章可供参考：在Oracle专家眼中，MySQL sys Schema是怎样一种存在?

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

所以这确实是一个宝贝，让我们对原本难以入手的性能问题有了一些很清晰的定位方式。

比如我很看好的一个功能是自增列的溢出问题检测，如果能够提前发现，那么对于运维工作会从被动变为主动。

在MySQL 5.7中我会这样来用：

select *from schema_auto_increment_columns;

如果自增列值快要溢出（我们可以设置一个阈值，比如90%之类的），我们可以通过视图的方式来很容易定位到。

如果要查看这个视图的具体实现就会发现逻辑远比我们想象的复杂，视图的定义如下：

select `information_schema`.`COLUMNS`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`COLUMNS`.`TABLE_NAME` AS `table_name`,`information_schema`.`COLUMNS`.`COLUMN_NAME` AS `column_name`,`information_schema`.`COLUMNS`.`DATA_TYPE` AS `data_type`,`information_schema`.`COLUMNS`.`COLUMN_TYPE` AS `column_type`,(locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) = 0) AS `is_signed`,(locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0) AS `is_unsigned`,((case `information_schema`.`COLUMNS`.`DATA_TYPE` when 'tinyint' then 255 when 'smallint' then 65535 when 'mediumint' then 16777215 when 'int' then 4294967295 when 'bigint' then 18446744073709551615 end) >> if((locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0),0,1)) AS `max_value`,`information_schema`.`TABLES`.`AUTO_INCREMENT` AS `auto_increment`,(`information_schema`.`TABLES`.`AUTO_INCREMENT` / ((case `information_schema`.`COLUMNS`.`DATA_TYPE` when 'tinyint' then 255 when 'smallint' then 65535 when 'mediumint' then 16777215 when 'int' then 4294967295 when 'bigint' then 18446744073709551615 end) >> if((locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0),0,1))) AS `auto_increment_ratio`
 from (`INFORMATION_SCHEMA`.`COLUMNS` 
 join `INFORMATION_SCHEMA`.`TABLES` 
 on(((`information_schema`.`COLUMNS`.`TABLE_SCHEMA` = `information_schema`.`TABLES`.`TABLE_SCHEMA`) 
 and (`information_schema`.`COLUMNS`.`TABLE_NAME` = `information_schema`.`TABLES`.`TABLE_NAME`)))) 
 where ((`information_schema`.`COLUMNS`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','performance_schema')) and (`information_schema`.`TABLES`.`TABLE_TYPE` = 'BASE TABLE') and (`information_schema`.`COLUMNS`.`EXTRA` = 'auto_increment')) 
 order by (`information_schema`.`TABLES`.`AUTO_INCREMENT` / ((case `information_schema`.`COLUMNS`.`DATA_TYPE` when 'tinyint' then 255 when 'smallint' then 65535 when 'mediumint' then 16777215 when 'int' then 4294967295 when 'bigint' then 18446744073709551615 end) >> if((locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0),0,1))) desc,((case `information_schema`.`COLUMNS`.`DATA_TYPE` when 'tinyint' then 255 when 'smallint' then 65535 when 'mediumint' then 16777215 when 'int' then 4294967295 when 'bigint' then 18446744073709551615 end) >> if((locate('unsigned',`information_schema`.`COLUMNS`.`COLUMN_TYPE`) > 0),0,1))