| Bug #105705 | `optimize table` is not followed with `analyze` | ||
|---|---|---|---|
| Submitted: | 25 Nov 2021 3:44 | Modified: | 29 Nov 2021 7:45 |
| Reporter: | Brian Yue (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 8.0.25 | OS: | Any (rhel-7.4) |
| Assigned to: | CPU Architecture: | Any (x86-64) | |
[26 Nov 2021 11:35]
MySQL Verification Team
Hello Brian Yue, Thank you for the report and feedback. Imho this is an expected behavior in 8.0 as default value of information_schema_stats_expiry is 86400 secs (= 1 day) https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_sc... ## bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.27 MySQL Community Server - GPL Copyright (c) 2000, 2021, 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> set session information_schema_stats_expiry=0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'information_schema_stats_expiry'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | information_schema_stats_expiry | 0 | +---------------------------------+-------+ 1 row in set (0.00 sec) mysql> drop database if exists test1; Query OK, 1 row affected (0.02 sec) mysql> create database test1; Query OK, 1 row affected (0.01 sec) mysql> use test1; Database changed mysql> mysql> create table t1 (id int, c1 varchar(10000)) charset=utf8mb4, engine=innodb; Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 values (1,repeat('A',10000)); Query OK, 1 row affected (0.01 sec) mysql> insert into t1 select * from t1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 8 rows affected (0.02 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 16 rows affected (0.03 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 32 rows affected (0.02 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 64 rows affected (0.01 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 128 rows affected (0.04 sec) Records: 128 Duplicates: 0 Warnings: 0 mysql> analyze table t1; +----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+---------+----------+----------+ | test1.t1 | analyze | status | OK | +----------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int DEFAULT NULL, `c1` varchar(10000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> delete from t1; Query OK, 256 rows affected (0.02 sec) mysql> show table status like 't1'\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 3145728 Auto_increment: NULL Create_time: 2021-11-26 12:28:07 Update_time: 2021-11-26 12:28:23 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> optimize table t1; +----------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+----------+----------+-------------------------------------------------------------------+ | test1.t1 | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test1.t1 | optimize | status | OK | +----------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.04 sec) mysql> show table status like 't1'\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-11-26 12:28:39 Update_time: NULL Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> analyze table t1; +----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+---------+----------+----------+ | test1.t1 | analyze | status | OK | +----------+---------+----------+----------+ 1 row in set (0.01 sec) mysql> show table status like 't1'\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-11-26 12:28:39 Update_time: NULL Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) regards, Umesh
[26 Nov 2021 11:39]
MySQL Verification Team
Adding missed note here: Could you please it at your end as well? Is there anything else I'm missing? Please let me know. Thank you!
[29 Nov 2021 7:45]
Brian Yue
Hello Umesh, Thanks for your quick response. After modifying configuration information_schema_stats_expiry as 0, my server behaves same with yours. Not a bug, now status of this issue is changed as closed.
[29 Nov 2021 7:51]
MySQL Verification Team
Thank you, Brian for confirming that it is working as expected. regards, Umesh
Description: Hello, `optimize table` upon a table of InnoDB SE shows `Table does not support optimize, doing recreate + analyze instead`, but actually when checking status of table with `show table status like ` I find that table stats info are not changed, but if `analyze table` command is executed table stats will show changement. So I guess there is no analyzation procedure during `optimize table` or the analyzation is not complete, not like the output of `optimize table` command. How to repeat: mysql> create database test1; Query OK, 1 row affected (0.01 sec) mysql> use test1 Database changed mysql> mysql> mysql> create table t1 (id int, c1 varchar(10000)) charset=utf8mb4, engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (1,repeat('A',10000)); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 select * from t1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 32 rows affected (0.01 sec) Records: 32 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 64 rows affected (0.01 sec) Records: 64 Duplicates: 0 Warnings: 0 mysql> insert into t1 select * from t1; Query OK, 128 rows affected (0.03 sec) Records: 128 Duplicates: 0 Warnings: 0 mysql> analyze table t1; +----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+---------+----------+----------+ | test1.t1 | analyze | status | OK | +----------+---------+----------+----------+ 1 row in set (0.01 sec) mysql> show create table t1; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int DEFAULT NULL, `c1` varchar(10000) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> delete from t1; Query OK, 256 rows affected (0.02 sec) mysql> show table status like 't1'\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 256 Avg_row_length: 18496 Data_length: 4734976 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-11-25 11:49:59 Update_time: 2021-11-25 11:50:28 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> optimize table t1; +----------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+----------+----------+-------------------------------------------------------------------+ | test1.t1 | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test1.t1 | optimize | status | OK | +----------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.02 sec) # After optimize table, Data_length and Data_free are not changed, actually now the table is already recreated. mysql> show table status like 't1'\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 256 Avg_row_length: 18496 Data_length: 4734976 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-11-25 11:51:43 Update_time: 2021-11-25 11:50:28 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) # After analyze table, Data_length and Data_free are correct now. mysql> analyze table t1; +----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+---------+----------+----------+ | test1.t1 | analyze | status | OK | +----------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> show table status like 't1'\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2021-11-25 11:51:43 Update_time: NULL Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql>