数据库优化：Waiting for table metadata lock的解决

转载自http://blog.csdn.net/qiuyepiaoling/article/details/7821984

 

一个简单的DDL操作(表数据很少，访问高)执行很久，导致Waiting for table metadata lock。

版本：mysql5.5.17

查看：Innodb_buffer_pool_pages_free = 0

解决：set global innodb_stats_on_metadata=0

查看丁奇老大博客：原文http://dinglin.iteye.com/blog/1575840

 

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

 

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）