The test main.index_merge_innodb is taking very much time, especially on later versions (10.2 and 10.3).
Some of this could be attributed to the use of INSERT…SELECT, which is time-consumingly creating explicit record locks in InnoDB.
In 10.3 and later, some is attributed to MDEV-12288, which makes the InnoDB purge thread spend time to reset transaction identifiers in the inserted records. If we prevent purge from running before all tables are dropped, the test seems to be 10% faster on an unoptimized debug build on 10.5. (A proper fix would be to implement MDEV-515 and stop writing row-level undo log records for inserts into an empty table or partition.)
At the same time, it should not hurt to make main.index_merge_myisam to use the sequence engine. Not only could it be a little faster, but the test would be slightly more readable.
- eval insert into t1 values ($1 div 10,$1 mod 100, $1/100,$1/100, $1/100,$1/100,$1/100,$1/100,$1/100, $1 mod 100, $1/1000,'filler-data-$1','filler2');
- dec $1;
-}
-commit;
---enable_query_log
+insert into t1
+select seq div 10,seq mod 100,seq/100,seq/100,seq/100,seq/100,seq/100,seq/100,
+seq/100,seq mod 100, seq/1000,concat('filler-data-',seq),'filler2'
+from seq_10000_to_1;
# Verify that range scan on CPK is ROR
# (use index_intersection because it is impossible to check that for index union)
For InnoDB, the only result difference was the addition of the statement. For MyISAM, we surprisingly get different EXPLAIN results:
+select seq div 10,seq mod 100,seq/100,seq/100,seq/100,seq/100,seq/100,seq/100,
+seq/100,seq mod 100, seq/1000,concat('filler-data-',seq),'filler2'
+from seq_10000_to_1;
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using index condition; Using where
@@ -1389,7 +1393,7 @@
95 50
explain select * from t1 where badkey=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref key1 key1 4 const 91 Using where
+1 SIMPLE t1 ref key1 key1 4 const 73 Using where
set @tmp_index_merge_ror_cpk=@@optimizer_switch;
set optimizer_switch='extended_keys=off';
explain select * from t1 where pk1 < 7500 and key1 = 10;
@@ -1398,22 +1402,22 @@
set optimizer_switch=@tmp_index_merge_ror_cpk;
explain select * from t1 where pktail1ok=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref key1,pktail1ok pktail1ok 4 const 76 Using where
+1 SIMPLE t1 ref key1,pktail1ok key1 4 const 73 Using where
explain select * from t1 where pktail2ok=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref key1,pktail2ok pktail2ok 4 const 82 Using where
+1 SIMPLE t1 ref key1,pktail2ok key1 4 const 73 Using where
explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 173 Using sort_union(pktail2ok,key1); Using where
+1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 154 Using sort_union(pktail2ok,key1); Using where
explain select * from t1 where pktail3bad=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref key1,pktail3bad pktail3bad 4 const 73 Using where
+1 SIMPLE t1 ref key1,pktail3bad key1 4 const 73 Using where
explain select * from t1 where pktail4bad=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref key1,pktail4bad pktail4bad 4 const 82 Using where
+1 SIMPLE t1 ref key1,pktail4bad key1 4 const 73 Using where
explain select * from t1 where pktail5bad=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref key1,pktail5bad pktail5bad 4 const 70 Using where
+1 SIMPLE t1 ref key1,pktail5bad key1 4 const 73 Using where
explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where
On a related note, div returns an integer result, while / is floating-point. If we change the first div to /, then there will also be result differences. And I guess it would be better to do something like
insert … select …, @h:=seq/100, @h, @h, …
to save some redundant typing and evaluation.
Marko Mäkelä
added a comment - psergey , I tried to include the following change in my 10.1 cleanup , but it would cause result differences:
diff --git a/mysql-test/include/index_merge_ror_cpk.inc b/mysql-test/include/index_merge_ror_cpk.inc
index 9ebca3cd22f..1ceeb159cc0 100644
--- a/mysql-test/include/index_merge_ror_cpk.inc
+++ b/mysql-test/include/index_merge_ror_cpk.inc
@@ -47,16 +47,10 @@ create table t1
primary key (pk1, pk2)
);
---disable_query_log
-begin;
-let $1=10000;
-while ($1)
-{
- eval insert into t1 values ($1 div 10,$1 mod 100, $1/100,$1/100, $1/100,$1/100,$1/100,$1/100,$1/100, $1 mod 100, $1/1000,'filler-data-$1','filler2');
- dec $1;
-}
-commit;
---enable_query_log
+insert into t1
+select seq div 10,seq mod 100,seq/100,seq/100,seq/100,seq/100,seq/100,seq/100,
+seq/100,seq mod 100, seq/1000,concat('filler-data-',seq),'filler2'
+from seq_10000_to_1;
# Verify that range scan on CPK is ROR
# (use index_intersection because it is impossible to check that for index union)
For InnoDB, the only result difference was the addition of the statement. For MyISAM, we surprisingly get different EXPLAIN results:
--- mysql-test/r/index_merge_myisam.result 2019-10-11 12:21:24.254070301 +0300
+++ mysql-test/r/index_merge_myisam.reject 2019-10-11 12:38:18.194391736 +0300
@@ -1357,6 +1357,10 @@
key (pktail5bad, pk1, pk2, pk2copy),
primary key (pk1, pk2)
);
+insert into t1
+select seq div 10,seq mod 100,seq/100,seq/100,seq/100,seq/100,seq/100,seq/100,
+seq/100,seq mod 100, seq/1000,concat('filler-data-',seq),'filler2'
+from seq_10000_to_1;
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using index condition; Using where
@@ -1389,7 +1393,7 @@
95 50
explain select * from t1 where badkey=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref key1 key1 4 const 91 Using where
+1 SIMPLE t1 ref key1 key1 4 const 73 Using where
set @tmp_index_merge_ror_cpk=@@optimizer_switch;
set optimizer_switch='extended_keys=off';
explain select * from t1 where pk1 < 7500 and key1 = 10;
@@ -1398,22 +1402,22 @@
set optimizer_switch=@tmp_index_merge_ror_cpk;
explain select * from t1 where pktail1ok=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref key1,pktail1ok pktail1ok 4 const 76 Using where
+1 SIMPLE t1 ref key1,pktail1ok key1 4 const 73 Using where
explain select * from t1 where pktail2ok=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref key1,pktail2ok pktail2ok 4 const 82 Using where
+1 SIMPLE t1 ref key1,pktail2ok key1 4 const 73 Using where
explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 173 Using sort_union(pktail2ok,key1); Using where
+1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 154 Using sort_union(pktail2ok,key1); Using where
explain select * from t1 where pktail3bad=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref key1,pktail3bad pktail3bad 4 const 73 Using where
+1 SIMPLE t1 ref key1,pktail3bad key1 4 const 73 Using where
explain select * from t1 where pktail4bad=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref key1,pktail4bad pktail4bad 4 const 82 Using where
+1 SIMPLE t1 ref key1,pktail4bad key1 4 const 73 Using where
explain select * from t1 where pktail5bad=1 and key1=10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref key1,pktail5bad pktail5bad 4 const 70 Using where
+1 SIMPLE t1 ref key1,pktail5bad key1 4 const 73 Using where
explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where
On a related note, div returns an integer result, while / is floating-point. If we change the first div to / , then there will also be result differences. And I guess it would be better to do something like
insert … select …, @h:=seq/100, @h, @h, …
to save some redundant typing and evaluation.
People
Marko Mäkelä
Marko Mäkelä
Votes:
0Vote for this issue
Watchers:
1Start watching this issue
Dates
Created:
Updated:
Resolved:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
Bug
Major
MDEV-515 innodb bulk insert
psergey, I tried to include the following change in my 10.1 cleanup, but it would cause result differences:
diff --git a/mysql-test/include/index_merge_ror_cpk.inc b/mysql-test/include/index_merge_ror_cpk.incindex 9ebca3cd22f..1ceeb159cc0 100644--- a/mysql-test/include/index_merge_ror_cpk.inc+++ b/mysql-test/include/index_merge_ror_cpk.inc@@ -47,16 +47,10 @@ create table t1primary key (pk1, pk2));---disable_query_log-begin;-let $1=10000;-while ($1)-{- eval insert into t1 values ($1 div 10,$1 mod 100, $1/100,$1/100, $1/100,$1/100,$1/100,$1/100,$1/100, $1 mod 100, $1/1000,'filler-data-$1','filler2');- dec $1;-}-commit;---enable_query_log+insert into t1+select seq div 10,seq mod 100,seq/100,seq/100,seq/100,seq/100,seq/100,seq/100,+seq/100,seq mod 100, seq/1000,concat('filler-data-',seq),'filler2'+from seq_10000_to_1;# Verify that range scan on CPK is RORFor InnoDB, the only result difference was the addition of the statement. For MyISAM, we surprisingly get different EXPLAIN results:
--- mysql-test/r/index_merge_myisam.result 2019-10-11 12:21:24.254070301 +0300+++ mysql-test/r/index_merge_myisam.reject 2019-10-11 12:38:18.194391736 +0300@@ -1357,6 +1357,10 @@key (pktail5bad, pk1, pk2, pk2copy),primary key (pk1, pk2));+insert into t1+select seq div 10,seq mod 100,seq/100,seq/100,seq/100,seq/100,seq/100,seq/100,+seq/100,seq mod 100, seq/1000,concat('filler-data-',seq),'filler2'+from seq_10000_to_1;explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using index condition; Using where@@ -1389,7 +1393,7 @@95 50explain select * from t1 where badkey=1 and key1=10;id select_type table type possible_keys key key_len ref rows Extra-1 SIMPLE t1 ref key1 key1 4 const 91 Using where+1 SIMPLE t1 ref key1 key1 4 const 73 Using whereset @tmp_index_merge_ror_cpk=@@optimizer_switch;set optimizer_switch='extended_keys=off';explain select * from t1 where pk1 < 7500 and key1 = 10;@@ -1398,22 +1402,22 @@set optimizer_switch=@tmp_index_merge_ror_cpk;explain select * from t1 where pktail1ok=1 and key1=10;id select_type table type possible_keys key key_len ref rows Extra-1 SIMPLE t1 ref key1,pktail1ok pktail1ok 4 const 76 Using where+1 SIMPLE t1 ref key1,pktail1ok key1 4 const 73 Using whereexplain select * from t1 where pktail2ok=1 and key1=10;id select_type table type possible_keys key key_len ref rows Extra-1 SIMPLE t1 ref key1,pktail2ok pktail2ok 4 const 82 Using where+1 SIMPLE t1 ref key1,pktail2ok key1 4 const 73 Using whereexplain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;id select_type table type possible_keys key key_len ref rows Extra-1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 173 Using sort_union(pktail2ok,key1); Using where+1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 154 Using sort_union(pktail2ok,key1); Using whereexplain select * from t1 where pktail3bad=1 and key1=10;id select_type table type possible_keys key key_len ref rows Extra-1 SIMPLE t1 ref key1,pktail3bad pktail3bad 4 const 73 Using where+1 SIMPLE t1 ref key1,pktail3bad key1 4 const 73 Using whereexplain select * from t1 where pktail4bad=1 and key1=10;id select_type table type possible_keys key key_len ref rows Extra-1 SIMPLE t1 ref key1,pktail4bad pktail4bad 4 const 82 Using where+1 SIMPLE t1 ref key1,pktail4bad key1 4 const 73 Using whereexplain select * from t1 where pktail5bad=1 and key1=10;id select_type table type possible_keys key key_len ref rows Extra-1 SIMPLE t1 ref key1,pktail5bad pktail5bad 4 const 70 Using where+1 SIMPLE t1 ref key1,pktail5bad key1 4 const 73 Using whereexplain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;id select_type table type possible_keys key key_len ref rows ExtraOn a related note, div returns an integer result, while / is floating-point. If we change the first div to /, then there will also be result differences. And I guess it would be better to do something like
to save some redundant typing and evaluation.