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:

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:

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:

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!

10 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
gggeek

Isn’t this eerily similar to noatime and its poerf improvements on disk access? 😀

0
Juan P.

Hello!

Thank you for the info. I’m using mysql 5.1 and I think that innodb_stats_on_metadata is set to ON by default. Do you know how to include this variable en my.cnf (file section, etc)?

Thank you!

0
Stephane Combaudon

@gggeek: you’re right. Except that I don’t really see any use case for innodb_stats_on_metadata = ON, while having noatime is sometimes useful.

@Juan P: you should include the setting somewhere in the mysqld section:
[mysqld]
innodb_stats_on_metadata = OFF

That will take effect after a MySQL restart. As the setting is dynamic, you can also change it at runtime with SET GLOBAL as shown in the post.

0
Morgan Tocker

@Stephane – there is a use case. Information_schema contains meta data with number of rows in the table, and average row length etc. These values (although coarsely generated) will potentially be more out of date without innodb_stats_on_metadata = ON.

0
Stephane Combaudon

: True. I don’t think it will ever be a problem as the values are always far from being accurate, but it’s worth mentioning.

0
Morgan Tocker

Agree with you – especially in 5.6 where the persistent stats are more accurate.

0
Vinodkumar Saravana

Hi

after adding the below line in my.ini file, mysql not starting, please advise me…

[mysqld]
innodb_stats_on_metadata = OFF

0
Stephane Combaudon

@Vinodkumar: which version of MySQL are you using? and which message do you have in the error log?

0
Vinodkumar Saravana

I am using Mysql 5.0.45 and i didn’t see any error in the log file…:(

Do you want me to share my.ini file with you?

0
Vinodkumar Saravana

sorry this is applicable to MySQL 5.1 or 5.5, i didn’t read it….

0