| Bug #98132 | Analyze table leads to empty statistics during online rebuild DDL | ||
|---|---|---|---|
| Submitted: | 6 Jan 2020 10:55 | Modified: | 7 Jan 2020 12:18 |
| Reporter: | Qingda Hu | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.6, 5.7, 8.0, 5.7.28, 8.0.18 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 Jan 2020 12:18]
MySQL Verification Team
Hello Albert Hu, Thank you for the report. regards, Umesh
[15 Sep 2021 12:16]
MySQL Verification Team
There is a duplicate of this bug in the following one: https://bugs.mysql.com/bug.php?id=104933 which makes this bug a serious one.
[16 Sep 2021 15:39]
Steven Duan
Hi, It is unbelievable that this serious bug is still open in the latest release, 8.0.26. It must have caused many production issues! I guess the bug is not well understood, because the repeat steps do not tell the right point. Note that abnormal index statistics itself is not so important, given that index dive plays a big role. It is n_diff for the primary key that is critical, because it being zero cheats the optimizer that the table is empty. The suggested fix works for me. And I have a dedicated test for it(see the enclosed patch in the next comment). Please reconsider the issue.
[16 Sep 2021 15:40]
Steven Duan
testcase based on MySQL 5.7.35 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bugfix-Online_DDL-leads-to-empty-statistics-of-ANALYZE.patch (application/octet-stream, text), 20.13 KiB.
[4 Jan 2022 9:58]
WANG GUANGYOU
we encounter a similar problem even no online rebuild ddl.
[4 Jan 2022 12:51]
WANG GUANGYOU
I want to know the innodb_stats_persistent option in your server I check the code it only happens in innodb_stats_persistent=off But my server meet full scan in innodb_stats_persistent=on and no online ddl
[4 Jan 2022 12:51]
WANG GUANGYOU
I want to know the innodb_stats_persistent option in your server I check the code it only happens in innodb_stats_persistent=off But my server meet full scan in innodb_stats_persistent=on and no online ddl
[31 Dec 2025 17:29]
Jean-François Gagné
It looks like this was fixed in 9.5 in another more recent bug with a contribution: Bug#105224. It also looks like 8.0 is still impacted, so is 8.4.
Description: When a session performs analyze table while another session performs an online DDL that rebuilds the same table, it will leads to empty statistics. Note that analyze table can be performed by user, or background thread when the number of modified rows beyond the threshold during DDL. This problem may introduce a lot of slow DDLs due to empty statistics, which slows downs system performance significantly. Until the end of online DDL, it will generate new statistics in commit_inplace_alter_table_impl(). How to repeat: Create a mtr case like this: ========================================================= 1 connect (a,localhost,root,,); 2 connect (b,localhost,root,,); 3 4 connection a; 5 create table t1 (a int, b int); 6 insert into t1 values(1,1); 7 insert into t1 select * from t1; 8 insert into t1 select * from t1; 9 insert into t1 select * from t1; 10 insert into t1 select * from t1; 11 insert into t1 select * from t1; 12 insert into t1 select * from t1; 13 analyze table t1; 14 select * from mysql.innodb_index_stats where table_name = 't1' and stat_name = 'n_diff_pfx01'; 15 16 connection b; 17 SET DEBUG_SYNC = 'RESET'; 18 SET DEBUG_SYNC = "innodb_inplace_alter_table_enter signal ddl_in_progress wait_for finish_ddl"; 19 --send alter table t1 drop column b; 20 21 connection a; 22 SET DEBUG_SYNC = "now wait_for ddl_in_progress"; 23 analyze table t1; 24 select * from mysql.innodb_index_stats where table_name = 't1' and stat_name = 'n_diff_pfx01'; 25 SET DEBUG_SYNC = "now signal finish_ddl"; 26 27 connection b; 28 --reap 29 select * from mysql.innodb_index_stats where table_name = 't1' and stat_name = 'n_diff_pfx01'; 30 31 drop table t1; 32 disconnect a; 33 disconnect b; Then, we run the mtr case and get the result like this: ==================================================== 1 create table t1 (a int, b int); 2 insert into t1 values(1,1); 3 insert into t1 select * from t1; 4 insert into t1 select * from t1; 5 insert into t1 select * from t1; 6 insert into t1 select * from t1; 7 insert into t1 select * from t1; 8 insert into t1 select * from t1; 9 analyze table t1; 10 Table Op Msg_type Msg_text 11 test.t1 analyze status OK 12 select * from mysql.innodb_index_stats where table_name = 't1' and stat_name = 'n_diff_pfx01'; 13 database_name table_name index_name last_update stat_name stat_value sample_size stat_description 14 test t1 GEN_CLUST_INDEX 2020-01-06 12:50:55 n_diff_pfx01 64 1 DB_ROW_ID 15 SET DEBUG_SYNC = 'RESET'; 16 SET DEBUG_SYNC = "innodb_inplace_alter_table_enter signal ddl_in_progress wait_for finish_ddl"; 17 alter table t1 drop column b;; 18 SET DEBUG_SYNC = "now wait_for ddl_in_progress"; 19 analyze table t1; 20 Table Op Msg_type Msg_text 21 test.t1 analyze status OK 22 select * from mysql.innodb_index_stats where table_name = 't1' and stat_name = 'n_diff_pfx01'; 23 database_name table_name index_name last_update stat_name stat_value sample_size stat_description 24 test t1 GEN_CLUST_INDEX 2020-01-06 12:50:55 n_diff_pfx01 0 1 DB_ROW_ID 25 SET DEBUG_SYNC = "now signal finish_ddl"; 26 select * from mysql.innodb_index_stats where table_name = 't1' and stat_name = 'n_diff_pfx01'; 27 database_name table_name index_name last_update stat_name stat_value sample_size stat_description 28 test t1 GEN_CLUST_INDEX 2020-01-06 12:50:56 n_diff_pfx01 64 1 DB_ROW_ID 29 drop table t1; ==================================================== Note that after performing analyze table (line23 in test case), the stat_value of clustered index will be 0 (line24 in result). Suggested fix: This problem occurs only when rebuilding table by online DDL. What's more, dict_index_is_online_ddl(index) will be true only when it is a clustered index. When perform online DDL for adding/dropping a secondary index, analyze table does not need to generate its statistics because the index is not completed and the statistics will be generated at the end of DDL. ==================================================== diff --git a/storage/innobase/btr/btr0btr.cc b/storage/innobase/btr/btr0btr.cc index 8b729eb..3c1026f 100644 --- a/storage/innobase/btr/btr0btr.cc +++ b/storage/innobase/btr/btr0btr.cc @@ -480,7 +480,10 @@ ulint btr_get_size(dict_index_t *index, /*!< in: index */ index->table->is_intrinsic()); ut_ad(index->page >= FSP_FIRST_INODE_PAGE_NO); - if (index->page == FIL_NULL || dict_index_is_online_ddl(index) || + /* Add is_clustered() to avoid the failure of analyze table during online DDL + which rebuilds clustered index. */ + if (index->page == FIL_NULL || + (dict_index_is_online_ddl(index) && !(index->is_clustered())) || !index->is_committed()) { return (ULINT_UNDEFINED); }