EVOLUTION-MANAGER

Edit File: bf_insert_select.test

# Verify that index and range scans are not slow
# on tables during insert 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) {
    INSERT 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) {
    INSERT into t2 SELECT count(*) from t1;
    inc $i;
}
let $time_elapsed_off = `select unix_timestamp() - $s`;

# check that the time with bulk fetch on is greater than with bulk fetch off
let $verdict = `select $time_elapsed_on > 0 && $time_elapsed_off > $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) {
    INSERT 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) {
    INSERT 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 > $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;