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:
None 
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

[6 Jan 2020 10:55] Qingda Hu
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);
   }
[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