关于 innodb_stats_on_metadata 的设置问题

转载 2013年12月01日 21:57:59

这个问题来自冷之同学测试时候碰到的一个“诡异现象”。

 

1、 测试现象       

测试的库有很多数据,但是重启之后,只对一个表的5w条记录作查询。查询条件客户端控制,确保查询范围。innodb_buffer_pool_size设置为35G。

现象1:查询性能会出现大幅度抖动;

现象2:介入追查后发现,Innodb_buffer_pool_pages_free = 0

 

         其中bp剩余量这个是最直观异常的,因为访问的5w行记录撑死也不可能把35G内存吃光的。在QA同学确认没有别人在使用这个库的情况下。

 

2、过程和原因

         其实几乎确定还是有别的查询在访问的。所以打开general_log。 发现除了QA同学压的语句外,这个Server上还有一些监控语句。

         其中一个语句如下

select constraint_schema,table_name,constraint_name,constraint_type from information_schema.table_constraints where table_schema not in ('information_schema', 'mysql', 'test');  

         这个语句访问了表 information_schema.table_constraints.

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

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

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

 

3、哪些表会触发

         不只是上面提到的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 ,也会触发这个操作,只是只处理单表,所以影响没那么明显。

 

4、修改

头痛医头的方法是把这些监控去掉。但实际上像TABLES、TABLE_CONSTRAINTS这些表,都是静态数据,访问时不作索引统计也没关系的。

另外一个方法就是把innodb_stats_on_metadata设置成off,这样上述说到的这些表访问都不会触发索引统计。

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

 

    之前有一篇文章也与此相关

from: http://dinglin.iteye.com/blog/1575840

MySQL索引统计信息更新相关的参数

MySQL统计信息相关的参数:   1. innodb_stats_on_metadata(是否自动更新统计信息),MySQL 5.7中默认为关闭状态     仅在统计信息配置为非持久化的...
  • meimeizhuzhuhua
  • meimeizhuzhuhua
  • 2017年04月20日 21:50
  • 118

MySQL统计信息收集及磁盘util指标问题

背景 在QQ群里,一位朋友问了如下的问题: 问题是:只查询一个information_schema.tables;表,为啥会有几百个Opening tables?原因:此操作会触发表统计信息的收集...
  • lijingkuan
  • lijingkuan
  • 2017年02月11日 13:12
  • 865

mysql如收集统计信息

查询优化器使用统计信息为sql选择执行计划 Mysql没有直方图信息,也无法手工删除统计信息   如何收集统计信息 Analyze table收集表和索引统计信息,适用于MyISAM和InnoDB; ...
  • jumewo
  • jumewo
  • 2015年07月16日 10:46
  • 939

MySQL统计信息相关参数

innodb_stats_persistent这个参数控制着统计信息是否写入到磁盘上,否则频繁计算的统计信息可能导致执行计划发生改变。 innodb_stats_persistent_sample_...
  • aoerqileng
  • aoerqileng
  • 2017年03月04日 16:48
  • 1026

gazebo环境变量问题设置问题

profile, bashrc, source, setup.*sh 一. source: 命令是使该文件立刻执行,这样刚才做的修改就可以立即生效了,否则要重新启动系统修改才能生效。(执行其后命令使...
  • wujialing99
  • wujialing99
  • 2016年07月14日 17:44
  • 596

MFC常见函数详解:SetRegistryKey

在利用mfc框架的时候,在App应用类的InitInstance()函数中,初始化时总有一个 SetRegistryKey("字符串XXX"),不知道究竟有何用处,这天仔细查看了一下,发现如果你使用注...
  • wanglei9876
  • wanglei9876
  • 2014年11月09日 22:38
  • 755

MySQL统计信息

SQL执行过程图 1. 客户端发送一条查询给服务器 2. 服务器先检查查询缓存,如果命中缓存,则立即返回缓存结果,否则进入下一阶段 3. 服务器端进行SQL解析,预处理,再由优化器生成对应的...
  • u013983450
  • u013983450
  • 2016年12月29日 14:57
  • 317

MySQL统计信息

摘要 我们将一条查询SQL提交给MySQL之后,MySQL在进行真正的查询操作之前通常会经历两个阶段:SQL解析和查询优化。在SQL解析过程中,MySQL会将SQL解析为一个树状结构,而在查询优...
  • hevenue
  • hevenue
  • 2017年08月22日 00:56
  • 109

Duilib中Scrollbar的设置问题

如果切得图片只有前三个按钮的,如图。此时没有rail和bk属性。设置了rail属性的话,滚动条中间就会出现横线,如果把rail属性屏蔽掉的话,就会去掉中间的横线问题。 image中的corn...
  • wupengqiangqinli
  • wupengqiangqinli
  • 2015年06月29日 15:22
  • 1370

单片机串口设置的问题

最近测试涉及到底层串口代码的修改。经过这次修改,突然发现其实自己对于串口的一些特性以前并不是十分清楚。 首先遇到的一些问题: 1)在使用IO的数据位的时候,没有考虑校验位所占的位数。 2)在设置...
  • tietao
  • tietao
  • 2014年03月11日 23:44
  • 3321
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:关于 innodb_stats_on_metadata 的设置问题
举报原因:
原因补充:

(最多只允许输入30个字)