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);
}
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); }