An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB perform the updates aside from the first opening of the table or manually running
ANALYZE TABLE on it? The 2 instances below are documented from the MySQL and InnoDB plugin’s manual:
SHOW TABLE STATUSand
SHOW [FULL] TABLES(or their corresponding queries from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS)
SHOW [FULL] TABLESor
SHOW TABLE STATUSon many a large tables, this can affect your server especially if the random index dives had to be read from disk. Here is an example of
SHOW TABLE STATUS FROM schemataking away a couple of seconds on execution time.
# Time: 110923 1:48:17
# User@Host: user1[user1] @ [172.20.6.1]
# Thread_id: 10140441 Schema: db1 Last_errno: 0 Killed: 0
# Query_time: 12.277786 Lock_time: 0.000068 Rows_sent: 294 Rows_examined: 294 Rows_affected: 0 Rows_read: 294
# Bytes_sent: 34187 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0
show table status from `db1`;
As you can see db1 has about 294 tables and it took the server 12 seconds to update all the tables’ statistics. Luckily, this can be controlled with Percona Server with the variable
innodb_stats_auto_update (from MySQL Community 5.1.17 a similar variable exist called
innodb_stats_on_metadata, for Percona Server where both variables exist, both should be 0 if you want to disable the feature). When set to 0, automatic updates to the table statistics (items 1 and 2 above) is disabled unless
ANALYZE TABLE is ran or during first open of the table.
On my next post, I will discuss the effects of disabling the feature and how you or your application should compensate.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.