Seriously Inconsistent Table Information in MySQL 8.0 Information_Schema

MySQL Information_SchemaIn MySQL 8, Information Schema was basically re-written to utilize the all-New Data Dictionary which made it faster and better in many ways. Yet it also introduces some very counterintuitive behaviors when it comes to how actual and consistent data is.

Here is a simple test.  Let’s create a table and insert a bunch of rows in it… and then check table size:

So we have a bunch of rows in the table but the information schema (queried for simplicity with SHOW TABLE STATUS command)  tells us the table is empty!

Any reasonable person would go ahead and file a bug at this point, but if you instead decide to spend some time googling you will find it is intended behavior.  There is information_schema_stats_expiry variable that controls caching for these statistics, which defaults to 86400 seconds – or one whole day!

If caching is indeed in order, would not be something like 5 minutes be a much more reasonable value, showing the “big picture”  correctly while also helping to improve performance? Such an approach would avoid having TB-sized tables on disk which report they have zero size, at least not for long.

In general, this looks like a classic case of premature optimization to me – with a moderate number of tables most users will have queries run pretty fast without caching (setting information_schema_stats_expiry to 0), and in my not-very-scientific-tests, I do not think I saw a performance difference more than 2x with and without caching. So why report confusing cached data when you can get the actual data fast enough? I do not know and a MySQL Engineering blog post on this topic (MySQL 8.0: Improvements to Information_schema) does not explain the reasoning of caching by default or the choice of such high expiration time.

If you read the MySQL manual you may also be under the impression you can just access mysql.index_stats and mysql.table_stats tables if you want the actual data from its source. But unfortunately, access to those tables is not available even to the  MySQL “root” user.

The good news, though, is that information_schema_stats_expiry is session-variable so you can easily set it to zero for the applications which need up to date table information!

Share this post

Comments (6)

  • Øystein Grøvlen Reply

    For InnoDB tables, you can also get statistics from mysql.innodb_table_stats and mysql.innodb_index_stats.

    January 3, 2020 at 1:37 pm
    • Peter Zaitsev Reply

      Thank you! Indeed One thing I see it specifies sizes for tables in pages which is of course 16K by default… but may be different. What is the best way to look up matching page size for the table ?

      January 3, 2020 at 1:44 pm
      • Øystein Grøvlen Reply

        I am not an InnoDB expert, but, AFAIU, page size may not be changed after you have initialized the instance. So the page size for all tables should be what is given by the Innodb_page_size status variable.

        January 3, 2020 at 5:15 pm
        • Peter Zaitsev Reply

          Yes. However Compressed Tables can have different page size. Though these do not get much use

          January 3, 2020 at 11:04 pm
  • billkarwin Reply

    I think this will cause a lot of confusion.

    I reported a bug about this against 8.0.1-dmr in May 2017!

    https://bugs.mysql.com/bug.php?id=86170

    January 3, 2020 at 1:54 pm
    • Peter Zaitsev Reply

      Yep Indeed. Instead of allowing to cache data for people who need extra performance lets confuse a hell out of everyone!

      January 3, 2020 at 2:10 pm

Leave a Reply