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
101
102
103
104
105
106
107
108
109
|
# Verify that index and range scans are not slow
# on tables during create select statements
# due to tokudb bulk fetch not being used
source include/have_tokudb.inc;
source include/have_partition.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`)
) PARTITION BY RANGE (num)
(PARTITION p0 VALUES LESS THAN (100000),
PARTITION p1 VALUES LESS THAN (200000),
PARTITION p2 VALUES LESS THAN (300000),
PARTITION p3 VALUES LESS THAN (400000),
PARTITION p4 VALUES LESS THAN (500000),
PARTITION p5 VALUES LESS THAN (600000),
PARTITION p6 VALUES LESS THAN (700000),
PARTITION p7 VALUES LESS THAN MAXVALUE);
# 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) {
CREATE TABLE t2 AS SELECT count(*) from t1;
DROP TABLE t2;
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) {
CREATE TABLE t2 AS SELECT count(*) from t1;
DROP TABLE t2;
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) {
CREATE TABLE t2 AS SELECT count(*) from t1 where num > 700000;
DROP TABLE t2;
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) {
CREATE TABLE t2 AS SELECT count(*) from t1 where num > 700000;
DROP TABLE t2;
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 range $verdict $time_elapsed_off $time_elapsed_on; }
if (!$verdict) { echo range $time_elapsed_off $time_elapsed_on; }
drop table t1;
|
