diff options
Diffstat (limited to 'storage/tokudb/mysql-test/tokudb/t/bf_insert_select_dup_key.test')
| -rw-r--r-- | storage/tokudb/mysql-test/tokudb/t/bf_insert_select_dup_key.test | 132 |
1 files changed, 55 insertions, 77 deletions
diff --git a/storage/tokudb/mysql-test/tokudb/t/bf_insert_select_dup_key.test b/storage/tokudb/mysql-test/tokudb/t/bf_insert_select_dup_key.test index 3200beeaba9..51c6d66d706 100644 --- a/storage/tokudb/mysql-test/tokudb/t/bf_insert_select_dup_key.test +++ b/storage/tokudb/mysql-test/tokudb/t/bf_insert_select_dup_key.test @@ -8,120 +8,98 @@ source include/have_tokudb.inc; source include/big_test.inc; set default_storage_engine='tokudb'; disable_warnings; -drop table if exists t,t1,t2; +drop table if exists t1,t2; enable_warnings; -let $maxq = 10; +let $debug = 0; -CREATE TABLE `t` ( +CREATE TABLE `t1` ( `num` int(10) unsigned auto_increment NOT NULL, `val` varchar(32) DEFAULT NULL, PRIMARY KEY (`num`) ); -# put 8M rows into t -INSERT INTO t values (null,null); -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -INSERT INTO t SELECT null,val FROM t; -SELECT count(*) FROM t; - -# Create first table from source table t -CREATE TABLE `t1` ( - `num` int(10) unsigned NOT NULL, - `val` varchar(32) DEFAULT NULL, - PRIMARY KEY (`num`) -) as select * from t; +# 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; # Create second table t2 that will serve as the target for the insert select statment CREATE TABLE `t2` ( `num` int(10) unsigned auto_increment NOT NULL, `count` bigint(20) NOT NULL, UNIQUE (num) - ) ENGINE=TokuDB DEFAULT CHARSET=latin1; +); +let $maxq = 20; -let $s = `select to_seconds(now())`; +set tokudb_bulk_fetch=ON; +let $s = `select unix_timestamp()`; let $i = 0; while ($i < $maxq) { - SELECT count(*) from t1; + INSERT into t2 (num,count) SELECT NULL,count(*) from t1 on DUPLICATE KEY UPDATE count=count+1; inc $i; } -let $time_elapsed_select = `select to_seconds(now()) - $s`; - -# The following line can be used to display the time elapsed data -# which could be useful for debugging. -#echo Index scans took $time_elapsed_select seconds.; +let $time_elapsed_on = `select unix_timestamp() - $s`; - -let $s = `select to_seconds(now())`; +set tokudb_bulk_fetch=OFF; +let $s = `select unix_timestamp()`; let $i = 0; while ($i < $maxq) { INSERT into t2 (num,count) SELECT NULL,count(*) from t1 on DUPLICATE KEY UPDATE count=count+1; inc $i; } -let $time_elapsed_insert_select = `select to_seconds(now()) - $s`; - -# The following line can be used to display the time elapsed data -# which could be useful for debugging. -#echo Index scans took $time_elapsed_insert_select seconds.; +let $time_elapsed_off = `select unix_timestamp() - $s`; -# This check evaluates whether the time elapsed during the insert select on duplicate key statement is on par -# with the select statement, which will confirm that bulk fetch is in fact being used. -let $verdict = `select abs($time_elapsed_insert_select - $time_elapsed_select) <= $time_elapsed_select`; +# 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 $maxrq = 30; +let $maxq = 20; -let $s = `select to_seconds(now())`; +set tokudb_bulk_fetch=ON; +let $s = `select unix_timestamp()`; let $i = 0; -while ($i < $maxrq) { - SELECT count(*) from t1 where num > 7000000; +while ($i < $maxq) { + INSERT into t2 (num,count) SELECT NULL,count(*) from t1 where num > 700000 on DUPLICATE KEY UPDATE count=count+1; inc $i; } -let $time_elapsed_select = `select to_seconds(now()) - $s`; - -# The following line can be used to display the time elapsed data -# which could be useful for debugging. -#echo Range scans took $time_elapsed_select seconds.; +let $time_elapsed_on = `select unix_timestamp() - $s`; - -let $s = `select to_seconds(now())`; +set tokudb_bulk_fetch=OFF; +let $s = `select unix_timestamp()`; let $i = 0; -while ($i < $maxrq) { - INSERT into t2 (num,count) SELECT NULL,count(*) from t1 where num > 7000000 on DUPLICATE KEY UPDATE count=count+1; +while ($i < $maxq) { + INSERT into t2 (num,count) SELECT NULL,count(*) from t1 where num > 700000 on DUPLICATE KEY UPDATE count=count+1; inc $i; } -let $time_elapsed_insert_select = `select to_seconds(now()) - $s`; +let $time_elapsed_off = `select unix_timestamp() - $s`; -# The following line can be used to display the time elapsed data -# which could be useful for debugging. -#echo Range scans took $time_elapsed_insert_select seconds.; - -# This check evaluates whether the time elapsed during the insert select on duplicate key statement is on par -# with the select statement, which will confirm that bulk fetch is in fact being used. -let $verdict = `select abs($time_elapsed_insert_select - $time_elapsed_select) <= $time_elapsed_select`; +# 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; } -enable_warnings; -drop table t,t1,t2; +drop table t1,t2; |