MySQL :: Further improvements on INFORMATION

Introduction

With the transactional data dictionary in MySQL 8.0, see MySQL 8.0: Data Dictionary Architecture and Design , INFORMATION_SCHEMA is reimplemented as views over data dictionary tables.

In an earlier post by Gopal Shankar, MySQL 8.0: Improvements to Information_schema , he describes how MySQL 8.0 dynamic table meta data will default to being cached. This is configurable via the setting information_schema_stats (default cached), and can be changed to information_schema_stats=latest in order to always retrieve the dynamic information directly from the storage engine (at the cost of slightly higher query execution).

All the above is done to preserve compatibility for applications using INFORMATION_SCHEMA, while fulfilling a requirement to boost performance for INFORMATION_SCHEMA queries significantly.

Community Feedback

The feedback from community and MySQL ACEs is the above settings can be confusing, since by default, you might end up seeing NULL values for dynamic values, if the statistics about the table has not been updated since server startup. We have received bugreports from Nicolai Plum bug#83957 and Peter Brawley bug#87548 as well, and we have therefore worked to find an improved solution.

Improvement in MySQL 8.0.3 RC1

In MySQL 8.0.3 RC1 we have replaced the option information_schema_stats with a new option information_schema_stats_expiry . The MySQL server will now store statistics about dynamic values in two tables, mysql.index_stats and mysql.table_stats. The information_schema_stats_expiry option controls the freshness of these cached values, and the default is set to 24hours. ANALYZE will update mysql.index_stats and mysql.table_stats .

If you set information_schema_stats_expiry=0 , the MySQL 8.0.3 server will always fetch fresh data from the storage engine and will not update the mysql.index_stats and mysql.table_stats tables, so not extra cycles are used to update information that is not used.

There are other scenarios where I_S queries will not store dynamic meta-data in mysql.index_stats and mysql.table_stats:

Server is started with –read-only
Server is started with –super-read-only
Server is started with –transaction-read-only
Server is started with –innodb-read-only
I_S query fetches performance_schema data

And more improvements..

The MySQL team is working to improve performance of INFORMATION_SCHEMA in general, and with the current architecture, improvements in the MySQL optimizer will affect INFORMATION_SCHEMA queries as well as these queries are handled as any other query by the optimizer.

We have also improved performance fetching dynamic data from SE (InnoDB). In MySQL 8.0.1 DMR, INFORMATION_QUERIES were typically 2-3 times faster than the same queries in MySQL 5.7. The MySQL dev team has done another improvement for MySQL 8.0.3 RC1, and queries are now typically 4-5 times faster than in MySQL 5.7.

The team continues to work on improved INFORMATION_SCHEMA performance, and wants your feedback if you experience slow INFORMATION_SCHEMA queries in MySQL 8.0.3 RC1!

Thank you for using MySQL !