innodb_stats_on_metadata and slow queries on INFORMATION_SCHEMA

INFORMATION_SCHEMA is usually the place to go when you want to get facts about a system (how many tables do we have? what are the 10 largest tables? What is data size and index size for table t?, etc). However it is also quite common that such queries are very slow and create lots of I/O load. Here is a tip to avoid theses hassles: set innodb_stats_on_metadata to OFF.

This is a topic we already talked about, but given the number of systems suffering from INFORMATION_SCHEMA slowness, I think it is good to bring innodb_stats_on_metadata back on the table.

The problem

Let’s look at a system I’ve seen recently: MySQL 5.5, working set fitting in memory but not the whole dataset, around 4000 InnoDB tables.

The I/O load is very light as the server is an idle replica. You can see the I/O load from this partial pt-diskstats output:

#ts device    rd_s rd_avkb    wr_s wr_avkb  busy in_prg
1.0 sda2       0.0     0.0     0.0     0.0    0%      0
1.0 sda2       0.0     0.0    16.0     9.2    0%      0
1.0 sda2       0.0     0.0     0.0     0.0    0%      0
1.0 sda2       0.0     0.0     0.0     0.0    0%      0
1.0 sda2       0.0     0.0     2.0     4.0    0%      0

The customer wanted to know what could be improved from the schema so we started by finding the 10 largest tables:




mysql> SELECT table_schema as 'DB',
table_name as 'TABLE',
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') 'TOTAL'
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;
[...]
10 rows in set (1 min 32.23 sec)

 

1mn32s is slow, but it’s not really a problem. But the I/O load triggered by this query IS a problem:

#ts device    rd_s rd_avkb    wr_s wr_avkb busy in_prg
1.0 sda2       0.0     0.0    18.0     8.7   0%      0
1.0 sda2       0.0     0.0     0.0     0.0   0%      0
1.0 sda2     100.0    16.0     0.0     0.0  79%      1
1.0 sda2     184.0    16.0     5.0     4.8  96%      1
1.0 sda2      97.0    16.0     0.0     0.0  98%      1
1.0 sda2     140.0    16.0     0.0     0.0  98%      1
1.0 sda2     122.0    16.0    17.0     4.0  98%      1
1.0 sda2     147.0    16.0     0.0     0.0  98%      1
[...]
1.0 sda2     136.0    16.0     0.0     0.0  98%      1
1.0 sda2     139.0    16.0     0.0     0.0  98%      1
1.0 sda2     149.0    16.0     0.0     0.0  98%      1
1.0 sda2     114.0    16.0     0.0     0.0  98%      1
1.0 sda2     147.0    16.0     8.0     4.0  96%      1
1.0 sda2     192.0    16.0     0.0     0.0  97%      1
1.0 sda2     141.0    16.0     0.0     0.0  98%      1
1.0 sda2     167.0    16.0     0.0     0.0  98%      1
1.0 sda2      15.0    16.0     0.0     0.0   6%      0
1.0 sda2       0.0     0.0     0.0     0.0   0%      0
1.0 sda2       0.0     0.0    16.0     4.0   0%      0

The disks are 100% busy reading InnoDB pages for our query. No doubt that if the server was running queries from the application, they would have been negatively impacted.

Now let’s execute the same query with innodb_stats_on_metadata = OFF;

mysql> SET GLOBAL innodb_stats_on_metadata = OFF;

 

mysql> SELECT [...]
10 rows in set (0.45 sec)

 And let’s look at pt-diskstats:

#ts device    rd_s rd_avkb    wr_s wr_avkb busy in_prg
1.0 sda2       0.0     0.0    16.0     9.2   0%      0
1.0 sda2       0.0     0.0     0.0     0.0   0%      0
1.0 sda2       0.0     0.0     0.0     0.0   0%      0
1.0 sda2       0.0     0.0    16.0     4.0   1%      0
1.0 sda2       0.0     0.0     0.0     0.0   0%      0
1.0 sda2       0.0     0.0     0.0     0.0   0%      0
1.0 sda2       0.0     0.0     0.0     0.0   0%      0
1.0 sda2       0.0     0.0     0.0     0.0   0%      0

No read load this time (and a much faster query!).

What is innodb_stats_on_metadata?

When the option is set to ON, InnoDB index statistics are updated when running SHOW TABLE STATUS, SHOW INDEX or when querying INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS. These statistics include the cardinality and the number of entries, they are used by the optimizer to find an optimal execution plan.

So even if SELECT statements cannot change the real statistics, MySQL updates the statistics for InnoDB tables. This is counterintuitive.

Is it useful? Not really, because InnoDB will always compute statistics when you open a table for the first time and when significant portions of the table have been modified (and when you run ANALYZE TABLE).

Now why did we have such a high read load when innodb_stats_on_metadata was set to ON? For InnoDB, statistics are estimated from random index dives, which translates to random reads.

The problem was magnified in the example because the whole dataset was not fitting in memory, the number of tables was high and the I/O subsystem was not very powerful.

Conclusion

It’s worth mentioning that the default value is now OFF with MySQL 5.6. So if you’re using MySQL 5.6, there’s no need to change anything. If you’re using MySQL 5.1 or 5.5, set innodb_stats_on_metadata to OFF and show your boss how you were able to get a 200x performance boost on some queries! And if you’re using MySQL 5.0 or below, you’ve just found another reason to upgrade!

————————————————————————————————————————

关于innodb_stats_on_metadata的另外一篇文章：

————————————————————————————————————————

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

 

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）.

 

注：

1. Innodb引擎是通过抽样的方式来计算统计信息的，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。Innodb通过参数innodb_stats_sample_pages来设置样本页的数量。设置更大的值，理论上来说可以帮助生成更准确的索引信息。
2. Innodb会在表首次打开，或者执行analyze table，或表的大小发生非常大的变化时计算索引的统计信息。
3. Innodb在打开某些information_schema表，或者使用show table status 和show index,或在mysql客户端开启自动补全功能的时候都会触发索引统计信息的更新。
4. 可在配置文件中设置                                                                                                                                                                [mysqld]
   innodb_stats_on_metadata = OFF   

参考：

http://www.mysqlperformanceblog.com/2013/12/03/innodb_stats_on_metadata-slow-queries-information_schema/

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