关于 innodb_stats_on_metadata 的设置问题

 【问题背景】

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

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

执行时间超过3分钟

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

 

【原因分析】

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

 这个语句触发了读盘操作。原因是需要访问引擎的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更新统计，查询元数据的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

 

转载于:https://www.cnblogs.com/gsblog/p/3679952.html