关于 innodb_stats_on_metadata 的设置问题

【问题背景】

线上使用osc进行表修改的时候出现SQL执行过长被kill的问题

关于 innodb_stats_on_metadata 的设置问题

由于数据库配置自动保护机制,导致一些执行时间比较长的SQL被kill掉

关于 innodb_stats_on_metadata 的设置问题

执行时间超过3分钟

同事建议修改一个innodb参数,set global innodb_stats_on_metadata=0,执行时间果然变短了

关于 innodb_stats_on_metadata 的设置问题

【原因分析】

以下引用于淘宝丁奇的分析:

这个语句触发了读盘操作。原因是需要访问引擎的info()接口,而InnoDB此时又“顺手”做了更新索引统计的操作dict_update_statistics。

更新索引统计的基本流程是随机读取部分demo行。所以这个操作实际上是访问了这个Server里面的所有表,因此不只是单单该表的记录

而且由于别的表事先没有被访问,就会导致读盘操作,也包括BP的LRU更新。

哪些表会触发

不只是上面提到的table_constraints,information_schema库下的一下几个表,访问时候都会触发这个“顺手”操作。

information_schema.TABLES

information_schema.STATISTICS

information_schema.PARTITIONS

information_schema.KEY_COLUMN_USAGE

information_schema.TABLE_CONSTRAINTS

information_schema.REFERENTIAL_CONSTRAINTS

其实还有 show table status ,也会触发这个操作,只是只处理单表,所以影响没那么明显。

修改方式:

把innodb_stats_on_metadata设置成off,这样上述说到的这些表访问都不会触发索引统计。

实际上这个动态统计的功能已经不推荐了,官方已经在6.0以后增加参数控制DML期间也不作动态统计了。因此这个参数配置成off更合理些(默认是on)。

官方文档说明

关于 innodb_stats_on_metadata 的设置问题

这个参数开启的时候,在进行元数据查询的时候会进行innodb更新统计,查询元数据的SQL包括show table status、show index,或者访问 INFORMATION_SCHEMA库的表

关闭这个参数,可以加快对于schema库表访问,同时也可以改善查询执行计划的稳定性(对于Innodb表的访问)。

在5.6中是默认关闭的。

【参考文献】

1、http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata

2、http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-statistics-estimation.html

3、http://dinglin.iteye.com/blog/1575840

4、http://www.woqutech.com/?p=378

上一篇:Android 音视频深入 十一 FFmpeg和AudioTrack播放声音(附源码下载)


下一篇:SQL Server和Oracle数据类型对应关系