Description:
Updates to transient stats are not taken into affect for I_S views in the same session until the table is opened by another session or flush tables is executed. This can lead to in consistent query plans why querying information_schema.
If this is an expected behaviour, please mention this in MySQL public documentation.
How to repeat:
CREATE PROCEDURE DEF FOR REPRO:
###############################
DROP PROCEDURE create_random_databases;
DELIMITER $$
CREATE PROCEDURE create_random_databases(n INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= n DO
SET @dbname = CONCAT('db_', FLOOR(RAND() * 1000000));
SET @query = CONCAT('CREATE DATABASE IF NOT EXISTS ', @dbname);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
SESSION 1:
##########
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)
mysql> explain select schema_name from information_schema.schemata;
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
| 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index |
| 1 | SIMPLE | sch | NULL | ALL | catalog_id,default_collation_id | NULL | NULL | NULL | 2579 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> call test.create_random_databases(500);
Query OK, 0 rows affected (1.88 sec)
mysql> explain select schema_name from information_schema.schemata;
+----+-------------+-------+------------+--------+---------------------------------+------------+---------+--------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------+------------+---------+--------------------------------+------+----------+-------------+
| 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index |
| 1 | SIMPLE | sch | NULL | ref | catalog_id,default_collation_id | catalog_id | 8 | mysql.cat.id | 2579 | 100.00 | Using where |
| 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------------------------+------------+---------+--------------------------------+------+----------+-------------+
4 rows in set, 1 warning (0.00 sec)
<< KEEP THIS SESSION OPEN AND CREATE A NEW SESSION >>
SESSION 2:
##########
[test@ip-172-31-54-9 ~]$ /home/test/opt/mysql/8.0.32/bin/mysql -uroot -pxxxxxx -P8032 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> explain select schema_name from information_schema.schemata;
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
| 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index |
| 1 | SIMPLE | sch | NULL | ALL | catalog_id,default_collation_id | NULL | NULL | NULL | 3089 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
<<< BACK TO SESSION 1 >>>
SESSION 1:
##########
mysql> explain select schema_name from information_schema.schemata;
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
| 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index |
| 1 | SIMPLE | sch | NULL | ALL | catalog_id,default_collation_id | NULL | NULL | NULL | 3089 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> show global variables like '%stats%';
+--------------------------------------+---------------+
| Variable_name | Value |
+--------------------------------------+---------------+
| information_schema_stats_expiry | 86400 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_transient_sample_pages | 8 |
| myisam_stats_method | nulls_unequal |
+--------------------------------------+---------------+
9 rows in set (0.00 sec)
mysql> \q
Bye
FLUSH TABLES TEST:
##################
mysql> explain select schema_name from information_schema.schemata;
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
| 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index |
| 1 | SIMPLE | sch | NULL | ALL | catalog_id,default_collation_id | NULL | NULL | NULL | 3089 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> call test.create_random_databases(500);
Query OK, 0 rows affected (2.21 sec)
mysql> explain select schema_name from information_schema.schemata;
+----+-------------+-------+------------+--------+---------------------------------+------------+---------+--------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------+------------+---------+--------------------------------+------+----------+-------------+
| 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index |
| 1 | SIMPLE | sch | NULL | ref | catalog_id,default_collation_id | catalog_id | 8 | mysql.cat.id | 3089 | 100.00 | Using where |
| 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------------------------+------------+---------+--------------------------------+------+----------+-------------+
4 rows in set, 1 warning (0.00 sec)
mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)
mysql> explain select schema_name from information_schema.schemata;
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
| 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index |
| 1 | SIMPLE | sch | NULL | ALL | catalog_id,default_collation_id | NULL | NULL | NULL | 3546 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL |
| 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+
4 rows in set, 1 warning (0.01 sec)
Description: Updates to transient stats are not taken into affect for I_S views in the same session until the table is opened by another session or flush tables is executed. This can lead to in consistent query plans why querying information_schema. If this is an expected behaviour, please mention this in MySQL public documentation. How to repeat: CREATE PROCEDURE DEF FOR REPRO: ############################### DROP PROCEDURE create_random_databases; DELIMITER $$ CREATE PROCEDURE create_random_databases(n INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i <= n DO SET @dbname = CONCAT('db_', FLOOR(RAND() * 1000000)); SET @query = CONCAT('CREATE DATABASE IF NOT EXISTS ', @dbname); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET i = i + 1; END WHILE; END$$ DELIMITER ; SESSION 1: ########## mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.32 | +-----------+ 1 row in set (0.00 sec) mysql> explain select schema_name from information_schema.schemata; +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ | 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index | | 1 | SIMPLE | sch | NULL | ALL | catalog_id,default_collation_id | NULL | NULL | NULL | 2579 | 100.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL | | 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ 4 rows in set, 1 warning (0.00 sec) mysql> call test.create_random_databases(500); Query OK, 0 rows affected (1.88 sec) mysql> explain select schema_name from information_schema.schemata; +----+-------------+-------+------------+--------+---------------------------------+------------+---------+--------------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------------+------------+---------+--------------------------------+------+----------+-------------+ | 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index | | 1 | SIMPLE | sch | NULL | ref | catalog_id,default_collation_id | catalog_id | 8 | mysql.cat.id | 2579 | 100.00 | Using where | | 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL | | 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------------------------+------------+---------+--------------------------------+------+----------+-------------+ 4 rows in set, 1 warning (0.00 sec) << KEEP THIS SESSION OPEN AND CREATE A NEW SESSION >> SESSION 2: ########## [test@ip-172-31-54-9 ~]$ /home/test/opt/mysql/8.0.32/bin/mysql -uroot -pxxxxxx -P8032 -h127.0.0.1 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 32 Server version: 8.0.32 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> explain select schema_name from information_schema.schemata; +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ | 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index | | 1 | SIMPLE | sch | NULL | ALL | catalog_id,default_collation_id | NULL | NULL | NULL | 3089 | 100.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL | | 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ 4 rows in set, 1 warning (0.00 sec) <<< BACK TO SESSION 1 >>> SESSION 1: ########## mysql> explain select schema_name from information_schema.schemata; +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ | 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index | | 1 | SIMPLE | sch | NULL | ALL | catalog_id,default_collation_id | NULL | NULL | NULL | 3089 | 100.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL | | 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ 4 rows in set, 1 warning (0.00 sec) mysql> show global variables like '%stats%'; +--------------------------------------+---------------+ | Variable_name | Value | +--------------------------------------+---------------+ | information_schema_stats_expiry | 86400 | | innodb_stats_auto_recalc | ON | | innodb_stats_include_delete_marked | OFF | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_transient_sample_pages | 8 | | myisam_stats_method | nulls_unequal | +--------------------------------------+---------------+ 9 rows in set (0.00 sec) mysql> \q Bye FLUSH TABLES TEST: ################## mysql> explain select schema_name from information_schema.schemata; +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ | 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index | | 1 | SIMPLE | sch | NULL | ALL | catalog_id,default_collation_id | NULL | NULL | NULL | 3089 | 100.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL | | 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ 4 rows in set, 1 warning (0.00 sec) mysql> call test.create_random_databases(500); Query OK, 0 rows affected (2.21 sec) mysql> explain select schema_name from information_schema.schemata; +----+-------------+-------+------------+--------+---------------------------------+------------+---------+--------------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------------+------------+---------+--------------------------------+------+----------+-------------+ | 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index | | 1 | SIMPLE | sch | NULL | ref | catalog_id,default_collation_id | catalog_id | 8 | mysql.cat.id | 3089 | 100.00 | Using where | | 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL | | 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------------------------+------------+---------+--------------------------------+------+----------+-------------+ 4 rows in set, 1 warning (0.00 sec) mysql> flush tables; Query OK, 0 rows affected (0.01 sec) mysql> explain select schema_name from information_schema.schemata; +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ | 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index | | 1 | SIMPLE | sch | NULL | ALL | catalog_id,default_collation_id | NULL | NULL | NULL | 3546 | 100.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | col | NULL | eq_ref | PRIMARY,character_set_id | PRIMARY | 8 | mysql.sch.default_collation_id | 1 | 100.00 | NULL | | 1 | SIMPLE | cs | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.col.character_set_id | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------------------------+---------+---------+--------------------------------+------+----------+--------------------------------------------+ 4 rows in set, 1 warning (0.01 sec)