diff options
Diffstat (limited to 'sql-bench')
| -rw-r--r-- | sql-bench/bench-init.pl.sh | 31 | ||||
| -rw-r--r-- | sql-bench/test-insert.sh | 77 | ||||
| -rw-r--r-- | sql-bench/test-select.sh | 6 |
3 files changed, 107 insertions, 7 deletions
diff --git a/sql-bench/bench-init.pl.sh b/sql-bench/bench-init.pl.sh index adfa114f569..0c4b1cb1b6a 100644 --- a/sql-bench/bench-init.pl.sh +++ b/sql-bench/bench-init.pl.sh @@ -31,7 +31,7 @@ # $server Object for current server # $limits Hash reference to limits for benchmark -$benchmark_version="2.13"; +$benchmark_version="2.14"; use Getopt::Long; require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n"; @@ -295,6 +295,35 @@ sub do_query die "\nError executing '$query':\n$DBI::errstr\n"; } +# +# Run a query X times +# + +sub time_fetch_all_rows +{ + my($test_text,$result_text,$query,$dbh,$test_count)=@_; + my($i,$loop_time,$end_time,$count,$rows,$estimated); + + print $test_text . "\n" if (defined($test_text)); + $count=$rows=0; + $loop_time=new Benchmark; + for ($i=1 ; $i <= $test_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,$query) or die $DBI::errstr; + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i, + $test_count)); + } + $end_time=new Benchmark; + if ($estimated) + { print "Estimated time"; } + else + { print "Time"; } + print " for $result_text ($count:$rows) " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +} + # # Handle estimated time of the server is too slow diff --git a/sql-bench/test-insert.sh b/sql-bench/test-insert.sh index b8f20b123a5..655e38b1b0e 100644 --- a/sql-bench/test-insert.sh +++ b/sql-bench/test-insert.sh @@ -562,7 +562,7 @@ if ($limits->{'group_functions'}) fetch_all_rows($dbh,"select min(id) from bench1"); fetch_all_rows($dbh,"select max(id) from bench1"); fetch_all_rows($dbh,"select sum(id+0.0) from bench1"); - fetch_all_rows($dbh,"select min(id3),max(id3),sum(id3 +0.0) from bench1"); + fetch_all_rows($dbh,"select min(id3),max(id3),sum(id3-0.0) from bench1"); if ($limits->{'group_func_sql_min_str'}) { fetch_all_rows($dbh,"select min(dummy1),max(dummy1) from bench1"); @@ -579,7 +579,7 @@ if ($limits->{'group_functions'}) $count++; - $sth=$dbh->prepare($query="select count(*),sum(id+0.0),min(id),max(id),avg(id+0.0) from bench1") or die $DBI::errstr; + $sth=$dbh->prepare($query="select count(*),sum(id+0.0),min(id),max(id),avg(id-0.0) from bench1") or die $DBI::errstr; $sth->execute or die $DBI::errstr; @row=$sth->fetchrow_array; if ($row[0] != $total_rows || @@ -1007,6 +1007,43 @@ if ($server->small_rollback_segment()) $dbh = $server->connect(); } +### +### Test speed of IN( value list) +### + +if ($limits->{'left_outer_join'}) +{ + if ($opt_lock_tables) + { + $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; + } + print "\n"; + do_many($dbh,$server->create("bench2", + ["id int NOT NULL"], + ["primary key (id)"])); + + $max_tests=min(($limits->{'query_size'}-50)/6, $opt_loop_count); + + if ($opt_lock_tables) + { + $sth = $dbh->do("LOCK TABLES bench1 READ, bench2 WRITE") || + die $DBI::errstr; + } + test_where_in("bench1","bench2","id",1,10); + test_where_in("bench1","bench2","id",11,min(100,$max_tests)); + test_where_in("bench1","bench2","id",101,min(1000,$max_tests)); + if ($opt_lock_tables) + { + $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; + } + $sth = $dbh->do("DROP TABLE bench2" . $server->{'drop_attr'}) || + die $DBI::errstr; + if ($opt_lock_tables) + { + $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr; + } +} + #### #### Test INSERT INTO ... SELECT #### @@ -1067,7 +1104,6 @@ if ($limits->{'insert_select'}) } } - #### #### Do some deletes on the table #### @@ -1605,3 +1641,38 @@ sub check_or_range print " for $check ($count:$found): " . timestr(timediff($end_time, $loop_time),"all") . "\n"; } + +# +# General test of SELECT ... WHERE id in(value-list) +# + +sub test_where_in +{ + my ($t1,$t2,$id,$from,$to)= @_; + + return if ($from >= $to); + + $query="SELECT $t1.* FROM $t1 WHERE $id IN ("; + for ($i=1 ; $i <= $to ; $i++) + { + $query.="$i,"; + } + $query=substr($query,0,length($query)-1) . ")"; + + # Fill join table to have the same id's as 'query' + for ($i= $from ; $i <= $to ; $i++) + { + $dbh->do("insert into $t2 values($i)") or die $DBI::errstr; + } + if ($opt_fast && defined($server->{vacuum})) + { + $server->vacuum(1,\$dbh,"bench1"); + } + + time_fetch_all_rows("Testing SELECT ... WHERE id in ($to values)", + "select_in", $query, $dbh, + $range_loop_count); + time_fetch_all_rows(undef, "select_join_in", + "SELECT $t1.* FROM $t2 left outer join $t1 on ($t1.$id=$t2.$id)", + $dbh, $range_loop_count); +} diff --git a/sql-bench/test-select.sh b/sql-bench/test-select.sh index 3c7efe3c5c2..1ecad5804c5 100644 --- a/sql-bench/test-select.sh +++ b/sql-bench/test-select.sh @@ -136,12 +136,12 @@ if ($limits->{'group_functions'}) print "Test if the database has a query cache\n"; # First ensure that the table is read into memory - fetch_all_rows($dbh,"select sum(idn+$tmp),sum(rev_idn+$tmp) from bench1"); + fetch_all_rows($dbh,"select sum(idn+$tmp),sum(rev_idn-$tmp) from bench1"); $loop_time=new Benchmark; for ($tests=0 ; $tests < $opt_loop_count ; $tests++) { - fetch_all_rows($dbh,"select sum(idn+$tests),sum(rev_idn+$tests) from bench1"); + fetch_all_rows($dbh,"select sum(idn+$tests),sum(rev_idn-$tests) from bench1"); } $end_time=new Benchmark; print "Time for select_query_cache ($opt_loop_count): " . @@ -153,7 +153,7 @@ if ($limits->{'group_functions'}) $loop_time=new Benchmark; for ($tests=0 ; $tests < $opt_loop_count ; $tests++) { - fetch_all_rows($dbh,"select sum(idn+$tests),sum(rev_idn+$tests) from bench1"); + fetch_all_rows($dbh,"select sum(idn+$tests),sum(rev_idn-$tests) from bench1"); } $end_time=new Benchmark; print "Time for select_query_cache2 ($opt_loop_count): " . |