See you soon, why SHOW TABLE STATUS never updates

Did you tread a hole?

1. Problem Description 2. Problem Exploration 3. Summary 4. Extended Reading

1. Description of the problem

A few days ago, the QQ group was discussing a delayed update of MySQL table statistics.
I've come back to this question. Here's the detailed process:

#Create an empty table
[root@yejr.run]>create table ttxx like t1;

#The first time you execute show table status, you see Rows = 0, no problem
[root@yejr.run] [test]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2020-06-04 16:17:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

#Write nearly 800,000 pieces of data
[root@yejr.run]>insert into ttxx select id,name,c1 from t1;
Query OK, 799994 rows affected (8.25 sec)
Records: 799994  Duplicates: 0  Warnings: 0

#Execute show table status again, and find that the Rows value is still 0, and execute several more times after a few seconds, the result is still the same
[root@yejr.run] [test]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2020-06-04 16:17:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

In short, SHOW TABLE STATUS cannot be executed to keep up-to-date statistics on the table.

But at the same time, look directly atMysql.innodb_Table_Stats andMysql.innodb_Index_Stats, two tables, and you can see that the table's statistics have been updated:

[root@yejr.run] [test]>select * from mysql.innodb_table_stats where database_name ='test' and table_name ='ttxx';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | ttxx       | 2020-06-04 16:18:24 | 795064 |                 2788 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+

[root@yejr.run] [test]>select * from mysql.innodb_index_stats where database_name ='test' and table_name ='ttxx';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | ttxx       | PRIMARY    | 2020-06-04 16:18:24 | n_diff_pfx01 |     795064 |          20 | aid                               |
| test          | ttxx       | PRIMARY    | 2020-06-04 16:18:24 | n_leaf_pages |       2764 |        NULL | Number of leaf pages in the index |
| test          | ttxx       | PRIMARY    | 2020-06-04 16:18:24 | size         |       2788 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

Trying to run an SQL observation execution plan also seems correct:

[root@yejr.run] [test]>desc select count(*) from ttxx;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | ttxx  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 795064 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

Executing SHOW TABLE STATUS at this time still cannot see the information update.

What's wrong with the swelling?

2. Problem Exploration

As an old driver, the first thing I think about is checking the official manual.
The description in the official MySQL manual contains the following:

• Rows

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

The Rows value is NULL for INFORMATION_SCHEMA tables.

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

In short, the Rows of the MyISAM table are exact values, but the InnoDB table is approximate and may even be between 40% and 50% of the true values.

In addition, this information is from INFORMATION_SCHEMA.TABLES(hereinafter referred to as short)IFS.TABLES) from:

Table information is also available from the INFORMATION_SCHEMA TABLES table. See Section 25.36, "The INFORMATION_SCHEMA TABLES Table".

Let's look at the documentation about IFS.TABLES Description of:

25.36 The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

Columns in TABLES that represent table statistics hold cached values. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To update cached values at any time for a given table, use ANALYZE TABLE. To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry to 0. For more information, see Section 8.2.3, "Optimizing INFORMATION_SCHEMA Queries".

Seeing this, the truth is almost at your fingertips.
IFS.TABLES The data you see in the table is cached. The default cache length is 86400 seconds (that is, 1 day), modifying the parameter information_schema_stats_expiry adjusts the duration.That is, unless the cache expires or you manually perform the ANALYZE TABLE update of statistics, it will not be updated actively.
This parameter (function) was added after MySQL 8.0, so this problem did not exist before 8.0.
Parameter information_schema_stats_expiry also affects it IFS.STATISTICS Table.
In addition, this parameter can be dynamically modified at the session level.
We are trying to modify the session level configuration:

[root@yejr.run]>set session information_schema_stats_expiry = 0;

#After modification you can see that the Rows data has changed
[root@yejr.run]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 795064
 Avg_row_length: 57
...

[root@yejr.run]>set session information_schema_stats_expiry = 86400;
#Change session configuration back to default, awkward Rows value returned to 0
[root@yejr.run] [test]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
...

It seems that if there is a need to read table status summary information in the application, it is best to execute the ANALYZE TABLE manually or modify the parameter values first, or use SQL like this:

select /* set_var(information_schema_stats_expiry = 1) */ * from information_schema.tables where table_schema='test' and table_name = 'ttxx'\G

This is the new HINT syntax since MySQL 8.0.

There is also a comment in the document:

If the innodb_read_only system variable is enabled, ANALYZE TABLE may fail because it cannot update statistics tables in the data dictionary, which use InnoDB. For ANALYZE TABLE operations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is a MyISAM table). To obtain the updated distribution statistics, set information_schema_stats_expiry=0.

This means that when the parameter innodb_is enabledRead_Executing ANALYZE TABLE after only will fail, even if the table to update statistics is the MyISAM engine, because all InnoDB tables are set to read-only and cannot be rewritten back to the corresponding InnoDB dictionary table after updating statistics.

3. Summary

When you encounter bizarre problems, it is customary to consult the official manual first, usually with an answer. Be patient, then be patient.

4. Extended reading

  • The INFORMATION_SCHEMA TABLES Table,https://dev.mysql.com/doc/refman/8.0/en/tables-table.html

  • SHOW TABLE STATUS Statement,https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html

  • sys var information_schema_stats_expiry,https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_schema_stats_expiry

enjoy MySQL 8.0 :)

The full text is complete.

    The knowledge-based "MySQL Optimized Lessons" lectured by Mr. Ye has already been upgraded to MySQL version 8.0. Now it is just time to get on the bus. Let's start the practice journey of MySQL 8.0.

    In addition, Teacher Ye finished the first edition of the "MySQL Performance Optimization" in Tencent classes. This course explains several key elements of MySQL performance optimization: rational use of indexes, reduce lock impact, and improve transaction concurrency.

    Here are the two-dimensional codes for automatic puzzle to enjoy the group price directly

     

Tags: MySQL Session SQL

Posted on Sun, 07 Jun 2020 18:11:50 -0700 by TeamTJ