diff options
Diffstat (limited to 'sql-bench')
| -rw-r--r-- | sql-bench/Makefile.am | 5 | ||||
| -rw-r--r-- | sql-bench/bench-count-distinct.sh | 258 | ||||
| -rw-r--r-- | sql-bench/bench-init.pl.sh | 9 | ||||
| -rw-r--r-- | sql-bench/server-cfg.sh | 9 |
4 files changed, 273 insertions, 8 deletions
diff --git a/sql-bench/Makefile.am b/sql-bench/Makefile.am index 85880dd1989..73ba070717f 100644 --- a/sql-bench/Makefile.am +++ b/sql-bench/Makefile.am @@ -23,13 +23,14 @@ bench_SCRIPTS = test-ATIS test-connect test-create test-insert \ test-big-tables test-select test-wisconsin \ test-alter-table \ bench-init.pl compare-results run-all-tests \ - server-cfg crash-me copy-db + server-cfg crash-me copy-db bench-count-distinct CLEANFILES = $(bench_SCRIPTS) EXTRA_SCRIPTS = test-ATIS.sh test-connect.sh test-create.sh \ test-insert.sh test-big-tables.sh test-select.sh \ test-alter-table.sh test-wisconsin.sh \ bench-init.pl.sh compare-results.sh server-cfg.sh \ - run-all-tests.sh crash-me.sh copy-db.sh + run-all-tests.sh crash-me.sh copy-db.sh \ + bench-count-distinct.sh EXTRA_DIST = $(EXTRA_SCRIPTS) dist-hook: diff --git a/sql-bench/bench-count-distinct.sh b/sql-bench/bench-count-distinct.sh new file mode 100644 index 00000000000..1359a864ac1 --- /dev/null +++ b/sql-bench/bench-count-distinct.sh @@ -0,0 +1,258 @@ +#!@PERL@ +# Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB +# +# This library is free software; you can redistribute it and/or +# modify it under the terms of the GNU Library General Public +# License as published by the Free Software Foundation; either +# version 2 of the License, or (at your option) any later version. +# +# This library is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# Library General Public License for more details. +# +# You should have received a copy of the GNU Library General Public +# License along with this library; if not, write to the Free +# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, +# MA 02111-1307, USA +# +# Test of selecting on keys that consist of many parts +# +##################### Standard benchmark inits ############################## + +use DBI; +use Getopt::Long; +use Benchmark; + +$opt_loop_count=10000; +$opt_medium_loop_count=200; +$opt_small_loop_count=10; +$opt_regions=6; +$opt_groups=100; + +chomp($pwd = `pwd`); $pwd = "." if ($pwd eq ''); +require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; + +$columns=min($limits->{'max_columns'},500,($limits->{'query_size'}-50)/24, + $limits->{'max_conditions'}/2-3); + +if ($opt_small_test) +{ + $opt_loop_count/=10; + $opt_medium_loop_count/=10; + $opt_small_loop_count/=10; + $opt_groups/=10; +} + +print "Testing the speed of selecting on keys that consist of many parts\n"; +print "The test-table has $opt_loop_count rows and the test is done with $columns ranges.\n\n"; + +#### +#### Connect and start timeing +#### + +$dbh = $server->connect(); +$start_time=new Benchmark; + +#### +#### Create needed tables +#### + +goto select_test if ($opt_skip_create); + +print "Creating table\n"; +$dbh->do("drop table bench1" . $server->{'drop_attr'}); + +do_many($dbh,$server->create("bench1", + ["region char(1) NOT NULL", + "idn integer(6) NOT NULL", + "rev_idn integer(6) NOT NULL", + "grp integer(6) NOT NULL"], + ["primary key (region,idn)", + "unique (region,rev_idn)", + "unique (region,grp,idn)"])); +if ($opt_lock_tables) +{ + do_query($dbh,"LOCK TABLES bench1 WRITE"); +} + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(1,\$dbh); +} + +#### +#### Insert $opt_loop_count records with +#### region: "A" -> "E" +#### idn: 0 -> count +#### rev_idn: count -> 0, +#### grp: distributed values 0 - > count/100 +#### + +print "Inserting $opt_loop_count rows\n"; + +$loop_time=new Benchmark; +$query="insert into bench1 values ("; +$half_done=$opt_loop_count/2; +for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ; $id++,$rev_id--) +{ + $grp=$id*3 % $opt_groups; + $region=chr(65+$id%$opt_regions); + do_query($dbh,"$query'$region',$id,$rev_id,$grp)"); + if ($id == $half_done) + { # Test with different insert + $query="insert into bench1 (region,idn,rev_idn,grp) values ("; + } +} + +$end_time=new Benchmark; +print "Time to insert ($opt_loop_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +if ($opt_lock_tables) +{ + do_query($dbh,"UNLOCK TABLES"); +} + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(0,\$dbh,"bench1"); +} + +if ($opt_lock_tables) +{ + do_query($dbh,"LOCK TABLES bench1 WRITE"); +} + +#### +#### Do some selects on the table +#### + +select_test: + + + +if ($limits->{'group_distinct_functions'}) +{ + print "Testing count(distinct) on the table\n"; + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select count(distinct region) from bench1"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $opt_medium_loop_count)); + } + print_time($estimated); + print " for count_distinct_key_prefix ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select count(distinct grp) from bench1"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $opt_medium_loop_count)); + } + print_time($estimated); + print " for count_distinct ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select count(distinct grp),count(distinct rev_idn) from bench1"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $opt_medium_loop_count)); + } + print_time($estimated); + print " for count_distinct_2 ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select region,count(distinct idn) from bench1 group by region"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $opt_medium_loop_count)); + } + print_time($estimated); + print " for count_distinct_group_on_key ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select grp,count(distinct idn) from bench1 group by grp"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $opt_medium_loop_count)); + } + print_time($estimated); + print " for count_distinct_group_on_key_parts ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select grp,count(distinct rev_idn) from bench1 group by grp"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $opt_medium_loop_count)); + } + print_time($estimated); + print " for count_distinct_group ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + $test_count=$opt_medium_loop_count/10; + for ($i=0 ; $i < $test_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select idn,count(distinct region) from bench1 group by idn"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $test_count)); + } + print_time($estimated); + print " for count_distinct_big ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; +} + +#### +#### End of benchmark +#### + +if ($opt_lock_tables) +{ + do_query($dbh,"UNLOCK TABLES"); +} +if (!$opt_skip_delete) +{ + do_query($dbh,"drop table bench1" . $server->{'drop_attr'}); +} + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(0,\$dbh); +} + +$dbh->disconnect; # close connection + +end_benchmark($start_time); diff --git a/sql-bench/bench-init.pl.sh b/sql-bench/bench-init.pl.sh index c316bae1e4d..a30e9b3d9c9 100644 --- a/sql-bench/bench-init.pl.sh +++ b/sql-bench/bench-init.pl.sh @@ -51,11 +51,12 @@ $log_prog_args=join(" ", skip_arguments(\@ARGV,"comments","cmp","server", "user", "host", "database", "password", "use-old-results","skip-test", "machine", "dir", "suffix", "log")); -GetOptions("skip-test=s","comments=s","cmp=s","server=s","user=s","host=s","database=s","password=s","loop-count=i","row-count=i","skip-create","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force","field-count=i","regions=i","groups=i","time-limit=i","log","use-old-results","machine=s","dir=s","suffix=s","help","odbc","small-test","small-tables","small-key-tables","stage=i","old-headers","die-on-errors","create-options=s","hires","tcpip","silent") || usage(); +GetOptions("skip-test=s","comments=s","cmp=s","server=s","user=s","host=s","database=s","password=s","loop-count=i","row-count=i","skip-create","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force","field-count=i","regions=i","groups=i","time-limit=i","log","use-old-results","machine=s","dir=s","suffix=s","help","odbc","small-test","small-tables","small-key-tables","stage=i","old-headers","die-on-errors","create-options=s","hires","tcpip","silent", +"socket=s") || usage(); usage() if ($opt_help); $server=get_server($opt_server,$opt_host,$opt_database,$opt_odbc, - machine_part()); + machine_part(), $opt_socket); $limits=merge_limits($server,$opt_cmp); $date=date(); @estimated=(0.0,0.0,0.0); # For estimated time support @@ -486,6 +487,10 @@ All benchmarks takes the following options: --password='password' Password for the current user. +--socket='socket' + If the database supports connecting through a Unix socket, + use this socket to connect + --regions This is a test specific option that is only used when debugging a test. This usually means how AND levels should be tested. diff --git a/sql-bench/server-cfg.sh b/sql-bench/server-cfg.sh index d87966db5f0..0ed6926a297 100644 --- a/sql-bench/server-cfg.sh +++ b/sql-bench/server-cfg.sh @@ -33,10 +33,10 @@ sub get_server { - my ($name,$host,$database,$odbc,$machine)=@_; + my ($name,$host,$database,$odbc,$machine,$socket)=@_; my ($server); if ($name =~ /mysql/i) - { $server=new db_MySQL($host, $database, $machine); } + { $server=new db_MySQL($host, $database, $machine, $socket); } elsif ($name =~ /pg/i) { $server= new db_Pg($host,$database); } elsif ($name =~ /msql/i) @@ -106,13 +106,14 @@ package db_MySQL; sub new { - my ($type,$host,$database,$machine)= @_; + my ($type,$host,$database,$machine,$socket)= @_; my $self= {}; my %limits; bless $self; $self->{'cmp_name'} = "mysql"; - $self->{'data_source'} = "DBI:mysql:$database:$host"; + $self->{'data_source'} = "DBI:mysql:database=$database;host=$host"; + $self->{'data_source'} .= ";mysql_socket=$socket" if($socket); $self->{'limits'} = \%limits; $self->{'smds'} = \%smds; $self->{'blob'} = "blob"; |