1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
# Verify that index and range scans are not slow
# on tables during replace select statements
# due to tokudb bulk fetch not being used
source include/have_tokudb.inc;
source include/big_test.inc;
set default_storage_engine='tokudb';
disable_warnings;
drop table if exists t1,t2;
enable_warnings;
let $debug = 0;
CREATE TABLE `t1` (
`num` int(10) unsigned auto_increment NOT NULL,
`val` varchar(32) DEFAULT NULL,
PRIMARY KEY (`num`)
);
# Create second table t2 that will serve as the target for the insert select statment
CREATE TABLE `t2` (
`count` bigint(20) NOT NULL
);
# put 1M rows into t1
INSERT INTO t1 values (null,null);
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
INSERT INTO t1 SELECT null,val FROM t1;
SELECT count(*) FROM t1;
let $maxq = 20;
set tokudb_bulk_fetch=ON;
let $s = `select unix_timestamp()`;
let $i = 0;
while ($i < $maxq) {
REPLACE into t2 SELECT count(*) from t1;
inc $i;
}
let $time_elapsed_on = `select unix_timestamp() - $s`;
set tokudb_bulk_fetch=OFF;
let $s = `select unix_timestamp()`;
let $i = 0;
while ($i < $maxq) {
REPLACE into t2 SELECT count(*) from t1;
inc $i;
}
let $time_elapsed_off = `select unix_timestamp() - $s`;
# check that bulk fetch on is at least 2 times faster than bulk fetch off
let $verdict = `select $time_elapsed_on > 0 && $time_elapsed_off >= 2 * $time_elapsed_on`;
echo $verdict;
if ($debug) { echo index $verdict $time_elapsed_off $time_elapsed_on; }
if (!$verdict) { echo index $time_elapsed_off $time_elapsed_on; }
let $maxq = 20;
set tokudb_bulk_fetch=ON;
let $s = `select unix_timestamp()`;
let $i = 0;
while ($i < $maxq) {
REPLACE into t2 SELECT count(*) from t1 where num > 700000;
inc $i;
}
let $time_elapsed_on = `select unix_timestamp() - $s`;
set tokudb_bulk_fetch=OFF;
let $s = `select unix_timestamp()`;
let $i = 0;
while ($i < $maxq) {
REPLACE into t2 SELECT count(*) from t1 where num > 700000;
inc $i;
}
let $time_elapsed_off = `select unix_timestamp() - $s`;
let $verdict = `select $time_elapsed_on > 0 && $time_elapsed_off >= 2 * $time_elapsed_on`;
echo $verdict;
if ($debug) { echo range $verdict $time_elapsed_off $time_elapsed_on; }
if (!$verdict) { echo range $time_elapsed_off $time_elapsed_on; }
drop table t1,t2;
|
