InnoDB index dives do not detect concurrent tree changes, return bogus estimates
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
New
|
Medium
|
Unassigned | |||
5.6 |
Fix Released
|
Medium
|
Laurynas Biveinis | |||
5.7 |
Fix Released
|
Medium
|
Laurynas Biveinis |
Bug Description
On PS 5.6 trunk:
main.index_
Test ended at 2016-09-03 08:59:05
CURRENT_TEST: main.index_
--- /mnt/workspace/
+++ /mnt/workspace/
@@ -1134,7 +1134,7 @@
update t1 set key2=key1,
explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL # Using sort_union(i3,i2); Using where
+1 SIMPLE t1 ALL i2,i3 NULL NULL NULL # Using where
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
key1 key2 key3
31 31 31
[29 Dec 6:39] Laurynas Biveinis n_rows_ in_range, this goes undetected, making it return bogus row count for the range, resulting in bogus query plans.
Description:
If something changes the index tree between the two dives in btr_estimate_
How to repeat:
Apply
--- storage/ innobase/ btr/btr0cur. cc.orig 2016-12-29 07:16:06.000000000 +0200 innobase/ btr/btr0cur. cc 2016-12-29 07:16:08.000000000 +0200
+++ storage/
@@ -3708,6 +3708,9 @@
mtr_commit(&mtr);
+ if (!strcmp( index-> name, "iC")) C("btr_ estimate_ n_rows_ in_range_ between_ dives") ;
+ DEBUG_SYNC_
+
mtr_start(&mtr);
cursor.path_arr = path2;
Then use the following MTR testcase. It uses purge as the concurrent index tree writer.
--source include/ have_debug. inc have_debug_ sync.inc have_innodb. inc
--source include/
--source include/
create table t3
(
key1 int not null,
key2 int not null,
INDEX i1(key1),
INDEX i2(key2)
) engine=InnoDB;
--disable_query_log
insert into t3 values (1,1),(2,2);
let $1=9;
set @d=2;
while ($1)
{
eval insert into t3 select key1+@d, key2+@d from t3;
eval set @d=@d*2;
dec $1;
}
--enable_query_log
analyze table t3;
SET @@GLOBAL. innodb_ purge_stop_ now=TRUE;
alter table t3 add keyC int not null, add index iC(keyC);
update t3 set keyC=key1;
analyze table t3;
--connect( con1,localhost, root,,)
explain select * from t3 where
key1=1 or key2=2 or keyC=12;
SET DEBUG_SYNC = "btr_estimate_ n_rows_ in_range_ between_ dives SIGNAL estimate_ready WAIT_FOR estimate_finish";
send explain select * from t3 where
key1=1 or key2=2 or keyC=12;
connection default;
SET DEBUG_SYNC = "now WAIT_FOR estimate_ready";
SET @@GLOBAL. innodb_ purge_run_ now=TRUE; wait_innodb_ all_purged. inc
--source include/
SET DEBUG_SYNC = "now SIGNAL estimate_finish";
connection con1;
reap;
explain select * from t3 where
key1=1 or key2=2 or keyC=12;
disconnect con1;
connection default;
drop table t3;
The testcase above executes the same EXPLAIN SELECT three times: with the iC index tree not purged at all, with it being purged between the two dives, and with the fully clean tree. The first and the last execution use index_merge, and the middle one is a table scan instead.
This is reduced from an intermittent main.index_ merge_innodb failure:
main.index_ merge_innodb [ fail ]
Test ended at 2016-12-09 07:08:51
CURRENT_TEST: main.index_ merge_innodb percona- server- 5.6-param/ BUILD_TYPE/ release/ Host/centos6- 64/mysql- test/r/ index_merge_ innodb. result 2016-12-09 14:08:06.818000030 +0300 percona- server- 5.6-param/ BUILD_TYPE/ release/ Host/centos6- 64/build/ mysql-test/ var/log/ index_merge_ innodb. reject 2016-12-09 15:08:51.296000042 +0300 i4,i5,i6, i7,i8,i9, iA,iB,iC i1,i2,i3, i4,i5,i6, i7,i8,i9, iA,iB,iC 4,4,4,4, 4,4,4,4, 4,4,4,4 NULL 12 Using union(i1, i2,i3,i4, i5,i6,i7, i8,i9,iA, iB,iC); Using where i4,i5,i6, i7,i8,i9, iA,iB,iC NULL NULL NULL 1024 Using where
--- /mnt/workspace/
+++ /mnt/workspace/
@@ -299,7 +299,7 @@
key5=5 or key6=6 or key7=7 or key8=8 or
key9=9 or keyA=10 or keyB=11 or keyC=12;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 index_merge i1,i2,i3,
+1 SIMPLE t3 ALL i1,i2,i3,
select * from t3 where
key1=1 or key2=2 or key3=3 or key4=4 or
...