From 7e9ffc69ecd1bddf731391b01e6be221efb6f8ef Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Wed, 29 Jul 2020 23:26:09 +0300 Subject: MDEV-21472: ALTER TABLE ... ANALYZE PARTITION ... with EITS reads and locks all rows Do not collect EITS statistics for this statement: ALTER TABLE t ANALYZE PARTITION p EITS stats are currently global, not per-partition. Collecting global stats when we are asked to process just one partition causes issues for DBAs. --- mysql-test/r/stat_tables_partition.result | 34 ++++++++++++++++++++++++++++++- mysql-test/r/stat_tables_rbr.result | 1 - mysql-test/t/stat_tables_partition.test | 30 +++++++++++++++++++++++++-- sql/sql_admin.cc | 10 +++++++++ 4 files changed, 71 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/stat_tables_partition.result b/mysql-test/r/stat_tables_partition.result index 12ae2570272..2dd63e858d4 100644 --- a/mysql-test/r/stat_tables_partition.result +++ b/mysql-test/r/stat_tables_partition.result @@ -9,5 +9,37 @@ ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK -SET use_stat_tables = DEFAULT; DROP TABLE t1; +# +# MDEV-21472: ALTER TABLE ... ANALYZE PARTITION ... with EITS reads and locks all rows +# +CREATE TABLE t1 ( +id int(11) auto_increment primary key, +c1 int(11) DEFAULT NULL +) PARTITION BY RANGE (id) ( +PARTITION p0 VALUES LESS THAN (4), +PARTITION p1 VALUES LESS THAN MAXVALUE +); +insert into t1(c1) values (1),(1),(1),(1), (1),(1),(1),(1); +insert into t1(c1) select c1 from t1; +insert into t1(c1) select c1 from t1; +select count(*) from t1; +count(*) +32 +select count(*) from t1 where id <4; +count(*) +3 +flush status; +set session use_stat_tables='preferably'; +# Must NOT show "Engine-independent statistics collected": +alter table t1 analyze partition p0; +Table Op Msg_type Msg_text +test.t1 analyze status OK +# Should not have Handler_read_rnd_next=34 +show session status like 'Handler_read_rnd%'; +Variable_name Value +Handler_read_rnd 0 +Handler_read_rnd_deleted 0 +Handler_read_rnd_next 0 +drop table t1; +SET use_stat_tables = DEFAULT; diff --git a/mysql-test/r/stat_tables_rbr.result b/mysql-test/r/stat_tables_rbr.result index 35e74e29b08..83f8e3bb7c6 100644 --- a/mysql-test/r/stat_tables_rbr.result +++ b/mysql-test/r/stat_tables_rbr.result @@ -13,7 +13,6 @@ SET use_stat_tables = PREFERABLY; CREATE TABLE t1 ( a INT ) ENGINE=MyISAM PARTITION BY HASH(a) PARTITIONS 2; ALTER TABLE t1 ANALYZE PARTITION p1; Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info diff --git a/mysql-test/t/stat_tables_partition.test b/mysql-test/t/stat_tables_partition.test index 1316e5cca11..11b74818d82 100644 --- a/mysql-test/t/stat_tables_partition.test +++ b/mysql-test/t/stat_tables_partition.test @@ -11,7 +11,33 @@ CREATE TABLE t1 (pk int PRIMARY KEY, a bit(1), INDEX idx(a) INSERT INTO t1 VALUES (1,1),(2,0),(3,0),(4,1); ANALYZE TABLE t1; +DROP TABLE t1; -SET use_stat_tables = DEFAULT; +--echo # +--echo # MDEV-21472: ALTER TABLE ... ANALYZE PARTITION ... with EITS reads and locks all rows +--echo # +CREATE TABLE t1 ( + id int(11) auto_increment primary key, + c1 int(11) DEFAULT NULL +) PARTITION BY RANGE (id) ( + PARTITION p0 VALUES LESS THAN (4), + PARTITION p1 VALUES LESS THAN MAXVALUE +); -DROP TABLE t1; +insert into t1(c1) values (1),(1),(1),(1), (1),(1),(1),(1); +insert into t1(c1) select c1 from t1; +insert into t1(c1) select c1 from t1; + +select count(*) from t1; +select count(*) from t1 where id <4; +flush status; +set session use_stat_tables='preferably'; + +--echo # Must NOT show "Engine-independent statistics collected": +alter table t1 analyze partition p0; + +--echo # Should not have Handler_read_rnd_next=34 +show session status like 'Handler_read_rnd%'; +drop table t1; + +SET use_stat_tables = DEFAULT; diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc index 4afaff58223..beecf3fae64 100644 --- a/sql/sql_admin.cc +++ b/sql/sql_admin.cc @@ -727,8 +727,18 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, { compl_result_code= result_code= HA_ADMIN_INVALID; } + + /* + The check for Alter_info::ALTER_ADMIN_PARTITION implements this logic: + do not collect EITS STATS for this syntax: + ALTER TABLE ... ANALYZE PARTITION p + EITS statistics is global (not per-partition). Collecting global stats + is much more expensive processing just one partition, so the most + appropriate action is to just not collect EITS stats for this command. + */ collect_eis= (table->table->s->table_category == TABLE_CATEGORY_USER && + !(lex->alter_info.flags &= Alter_info::ALTER_ADMIN_PARTITION) && (get_use_stat_tables_mode(thd) > NEVER || lex->with_persistent_for_clause)); -- cgit v1.2.1