| Bug #103197 | INFORMATION_SCHEMA.STATISTICS/mysql.index_stats not updated by autostats recalc | ||
|---|---|---|---|
| Submitted: | 2 Apr 2021 22:24 | Modified: | 5 Apr 2021 16:13 |
| Reporter: | Marc Reilly | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[3 Apr 2021 6:19]
Øystein Grøvlen
I think this is expected behavior in 8.0. See description of information_schema_stats_expiry variable: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_sc...
[5 Apr 2021 5:57]
MySQL Verification Team
Thank you, Øystein Grøvlen. Hi Mark, Thank you for the report and feedback. Imho this is an expected behavior in 8.0because the default setting for information_schema_stats_expiry=86400 secs (= 1 day). This is introduced in 8.0 and not present in 5.7. - 8.0.23 Workaround is to set information_schema_stats_expiry=0 which will enable I_S to stop using the cached statistics value and always get latest statistics: mysql> show variables like 'information_schema_stats_expiry'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | information_schema_stats_expiry | 86400 | +---------------------------------+-------+ 1 row in set (0.01 sec) mysql> show global variables like 'information_schema_stats_expiry'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | information_schema_stats_expiry | 86400 | +---------------------------------+-------+ 1 row in set (0.00 sec) - mysql> # 1024 values mysql> SELECT COUNT(*) FROM t3; +----------+ | COUNT(*) | +----------+ | 1024 | +----------+ 1 row in set (0.00 sec) mysql> SHOW INDEX IN t3; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t3 | 0 | PRIMARY | 1 | i | A | 1 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) mysql> select * from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA='mysqltest-db1'; +---------------+---------------+------------+------------+---------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | IS_VISIBLE | EXPRESSION | +---------------+---------------+------------+------------+---------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+ | def | mysqltest-db1 | t3 | 0 | mysqltest-db1 | PRIMARY | 1 | i | A | 1 | NULL | NULL | | BTREE | | | YES | NULL | +---------------+---------------+------------+------------+---------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+ 1 row in set (0.00 sec) mysql> set information_schema_stats_expiry=0; Query OK, 0 rows affected (0.00 sec) mysql> SHOW INDEX IN t3; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t3 | 0 | PRIMARY | 1 | i | A | 1024 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) mysql> select * from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA='mysqltest-db1'; +---------------+---------------+------------+------------+---------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | IS_VISIBLE | EXPRESSION | +---------------+---------------+------------+------------+---------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+ | def | mysqltest-db1 | t3 | 0 | mysqltest-db1 | PRIMARY | 1 | i | A | 1024 | NULL | NULL | | BTREE | | | YES | NULL | +---------------+---------------+------------+------------+---------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+ 1 row in set (0.00 sec) regards, Umesh
[5 Apr 2021 16:13]
Marc Reilly
Ah yes, of course! Thanks for checking Øystein and Umesh.
Description: When using innodb persistent stats cardinality in INFORMATION_SCHEMA.STATISTICS/mysql.index_stats does not seem to get updated when statistics are auto recalculated. This leads to inaccurate cardinality values via INFORMATION_SCHEMA.STATISTICS and SHOW INDEX in <table>. Looking at innodb_index_stats the correct values are populated. This is a change in behavior from versions prior to mysql 8, see outputs below. The current workaround is to run analyze which will update the index stats in the information schema Outputs based on below repro: MySQL 8.0.23 Here mysql.innodb_index_stats and INFORMATION_SCHEMA.STATISTICS since INFORMATION_SCHEMA.STATISTICS is not getting updated ``` > select @@version; +-----------+ | @@version | +-----------+ | 8.0.23 | +-----------+ 1 row in set (0.00 sec) > SHOW INDEX IN t3; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t3 | 0 | PRIMARY | 1 | i | A | 1 | NULL | NULL | | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) > SELECT * FROM mysql.innodb_index_stats where database_name='mysqltest-db1'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | mysqltest-db1 | t3 | PRIMARY | 2021-04-02 22:00:17 | n_diff_pfx01 | 1024 | 3 | i | | mysqltest-db1 | t3 | PRIMARY | 2021-04-02 22:00:17 | n_leaf_pages | 3 | NULL | Number of leaf pages in the index | | mysqltest-db1 | t3 | PRIMARY | 2021-04-02 22:00:17 | size | 4 | NULL | Number of pages in the index | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 3 rows in set (0.00 sec) > select * from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA='mysqltest-db1'; +---------------+---------------+------------+------------+---------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | IS_VISIBLE | EXPRESSION | +---------------+---------------+------------+------------+---------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+ | def | mysqltest-db1 | t3 | 0 | mysqltest-db1 | PRIMARY | 1 | i | A | 1 | NULL | NULL | | BTREE | | | YES | NULL | +---------------+---------------+------------+------------+---------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+------------+------------+ 1 row in set (0.00 sec) ``` MySQL 5.7.33 Here mysql.innodb_index_stats and INFORMATION_SCHEMA.STATISTICS are in sync ``` > SHOW INDEX IN t3; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t3 | 0 | PRIMARY | 1 | i | A | 1024 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) > SELECT * FROM mysql.innodb_index_stats where database_name='mysqltest-db1'; +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | mysqltest-db1 | t3 | PRIMARY | 2021-04-02 22:02:10 | n_diff_pfx01 | 1024 | 3 | i | | mysqltest-db1 | t3 | PRIMARY | 2021-04-02 22:02:10 | n_leaf_pages | 3 | NULL | Number of leaf pages in the index | | mysqltest-db1 | t3 | PRIMARY | 2021-04-02 22:02:10 | size | 4 | NULL | Number of pages in the index | +---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ 3 rows in set (0.00 sec) > select * from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA='mysqltest-db1'; +---------------+---------------+------------+------------+---------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | +---------------+---------------+------------+------------+---------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ | def | mysqltest-db1 | t3 | 0 | mysqltest-db1 | PRIMARY | 1 | i | A | 1024 | NULL | NULL | | BTREE | | | +---------------+---------------+------------+------------+---------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ 1 row in set (0.00 sec) ``` How to repeat: CREATE DATABASE `mysqltest-db1`; USE `mysqltest-db1`; CREATE TABLE t3 (i int primary key auto_increment, j char(2)) stats_persistent=1; INSERT INTO t3(j) VALUES('a'); # one value, cardinality 1 SELECT * FROM t3; SHOW INDEX IN t3; INSERT INTO t3(j) SELECT j FROM t3; INSERT INTO t3(j) SELECT j FROM t3; INSERT INTO t3(j) SELECT j FROM t3; INSERT INTO t3(j) SELECT j FROM t3; INSERT INTO t3(j) SELECT j FROM t3; INSERT INTO t3(j) SELECT j FROM t3; INSERT INTO t3(j) SELECT j FROM t3; INSERT INTO t3(j) SELECT j FROM t3; INSERT INTO t3(j) SELECT j FROM t3; INSERT INTO t3(j) SELECT j FROM t3; SELECT SLEEP(15); # 1024 values SELECT COUNT(*) FROM t3; # Will show cardinality 1 SHOW INDEX IN t3; # Will show cardinality 1 select * from INFORMATION_SCHEMA.STATISTICS where TABLE_SCHEMA='mysqltest-db1'; # Will correctly show cardinality 1024 SELECT * FROM mysql.innodb_index_stats where database_name='mysqltest-db1'; Suggested fix: When innodb_index_stats is updated as part of automatic stats recalc, also update mysql.index_stats so the information schema is accurate.