diff options
Diffstat (limited to 'sql-bench')
| -rw-r--r-- | sql-bench/server-cfg.sh | 5 | ||||
| -rw-r--r-- | sql-bench/test-insert.sh | 37 | ||||
| -rw-r--r-- | sql-bench/test-wisconsin.sh | 54 |
3 files changed, 54 insertions, 42 deletions
diff --git a/sql-bench/server-cfg.sh b/sql-bench/server-cfg.sh index 86d62cea54e..8fc2f1319e9 100644 --- a/sql-bench/server-cfg.sh +++ b/sql-bench/server-cfg.sh @@ -145,7 +145,8 @@ sub new $limits{'join_optimizer'} = 1; # Can optimize FROM tables $limits{'left_outer_join'} = 1; # Supports left outer joins $limits{'like_with_column'} = 1; # Can use column1 LIKE column2 - $limits{'limit'} = 1; # supports the limit attribute + $limits{'limit'} = 1; # supports the limit attribute + $limits{'truncate_table'} = 1; $limits{'load_data_infile'} = 1; # Has load data infile $limits{'lock_tables'} = 1; # Has lock tables $limits{'max_column_name'} = 64; # max table and column name @@ -604,6 +605,7 @@ sub new $limits{'select_without_from'}= 1; $limits{'subqueries'} = 1; $limits{'table_wildcard'} = 1; + $limits{'truncate_table'} = 1; $limits{'unique_index'} = 1; # Unique index works or not $limits{'working_all_fields'} = 1; $limits{'working_blobs'} = 1; # If big varchar/blobs works @@ -1386,6 +1388,7 @@ sub new $limits{'max_index_parts'} = 16; # Max segments/key $limits{'max_column_name'} = 32; # max table and column name + $limits{'truncate_table'} = 1; $limits{'join_optimizer'} = 1; # Can optimize FROM tables $limits{'load_data_infile'} = 0; # Has load data infile $limits{'lock_tables'} = 0; # Has lock tables diff --git a/sql-bench/test-insert.sh b/sql-bench/test-insert.sh index 9dc3d9aa7c2..9da82fc8191 100644 --- a/sql-bench/test-insert.sh +++ b/sql-bench/test-insert.sh @@ -1173,7 +1173,7 @@ if (!$opt_skip_delete) } $end_time=new Benchmark; - print "Time for delete_all ($count): " . + print "Time for delete_range ($count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; if ($opt_lock_tables) @@ -1209,6 +1209,7 @@ print "Insert into table with $keys keys and with a primary key with $seg parts\ # Make keys on the most important types @types=(0,0,0,1,0,0,0,1,1,1,1,1,1,1,1,1,1); # A 1 for each char field push(@fields,"field1 tinyint not null"); +push(@fields,"field_search tinyint not null"); push(@fields,"field2 mediumint not null"); push(@fields,"field3 smallint not null"); push(@fields,"field4 char(16) not null"); @@ -1228,9 +1229,10 @@ for ($i= 1 ; $i <= $seg ; $i++) } substr($query,-1)=")"; push (@keys,$query); +push (@keys,"index index2 (field_search)"); #Create other keys -for ($i=2 ; $i <= $keys ; $i++) +for ($i=3 ; $i <= $keys ; $i++) { push(@keys,"index index$i (field$i)"); } @@ -1256,11 +1258,11 @@ if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value $res=$query; for ($i=0; $i < $many_keys_loop_count; $i++) { + $id= $i & 127; $rand=$random[$i]; - $tmp="(" . ($i & 127) . ",$rand," . ($i & 32766) . - ",'ABCDEF$rand',0,"; + $tmp="($id,$id,$rand," . ($i & 32766) . ",'ABCDEF$rand',0,"; - for ($j=5; $j <= $fields ; $j++) + for ($j=6; $j <= $fields ; $j++) { $tmp.= ($types[$j] == 0) ? "$rand," : "'$rand',"; } @@ -1281,11 +1283,12 @@ else { for ($i=0; $i < $many_keys_loop_count; $i++) { + $id= $i & 127; $rand=$random[$i]; - $query="insert into bench1 values (" . ($i & 127) . ",$rand," . ($i & 32767) . + $query="insert into bench1 values ($id,$id,$rand," . ($i & 32767) . ",'ABCDEF$rand',0,"; - for ($j=5; $j <= $fields ; $j++) + for ($j=6; $j <= $fields ; $j++) { $query.= ($types[$j] == 0) ? "$rand," : "'$rand',"; } @@ -1324,8 +1327,8 @@ print "Testing update of keys\n"; $loop_time=new Benchmark; for ($i=0 ; $i< 256; $i++) { - $dbh->do("update bench1 set field5=1 where field1=$i") - or die "Got error $DBI::errstr with query: update bench1 set field5=1 where field1=$i\n"; + $dbh->do("update bench1 set field5=1 where field_search=$i") + or die "Got error $DBI::errstr with query: update bench1 set field5=1 where field_search=$i\n"; } $end_time=new Benchmark; print "Time for update_of_primary_key_many_keys (256): " . @@ -1366,29 +1369,31 @@ $count=0; for ($i=0 ; $i < 128 ; $i++) { $count++; - $dbh->do("delete from bench1 where field1 = $i") or die $DBI::errstr; + $dbh->do("delete from bench1 where field_search = $i") or die $DBI::errstr; } $end_time=new Benchmark; print "Time for delete_big_many_keys ($count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +if ($opt_lock_tables) +{ + $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; +} + print "Deleting everything from table\n"; $count=1; if ($opt_fast) { - $dbh->do("delete from bench1") or die $DBI::errstr; + $query= ($limits->{'truncate_table'} ? "truncate table bench1" : + "delete from bench1"); + $dbh->do($query) or die $DBI::errstr; } else { $dbh->do("delete from bench1 where field1 > 0") or die $DBI::errstr; } -if ($opt_lock_tables) -{ - $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; -} - $end_time=new Benchmark; print "Time for delete_all_many_keys ($count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; diff --git a/sql-bench/test-wisconsin.sh b/sql-bench/test-wisconsin.sh index a017120259e..84d098ca583 100644 --- a/sql-bench/test-wisconsin.sh +++ b/sql-bench/test-wisconsin.sh @@ -48,27 +48,36 @@ init_query(); print "Wisconsin benchmark test\n\n"; -if (!$opt_skip_create) +if ($opt_skip_create) { - $loop_time= new Benchmark; - for($ti = 0; $ti <= $#table_names; $ti++) + if ($opt_lock_tables) { - my $table_name = $table_names[$ti]; - my $array_ref = $tables[$ti]; - - # This may fail if we have no table so do not check answer - $sth = $dbh->do("drop table $table_name" . $server->{'drop_attr'}); - print "Creating table $table_name\n" if ($opt_verbose); - do_many($dbh,@$array_ref); + @tmp=@table_names; push(@tmp,@extra_names); + $sth = $dbh->do("LOCK TABLES " . join(" WRITE,", @tmp) . " WRITE") || + die $DBI::errstr; } - $end_time=new Benchmark; - print "Time for create_table ($#tables): " . - timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + goto start_benchmark; +} - if ($opt_fast && defined($server->{vacuum})) - { - $server->vacuum(1,\$dbh); - } +$loop_time= new Benchmark; +for($ti = 0; $ti <= $#table_names; $ti++) +{ + my $table_name = $table_names[$ti]; + my $array_ref = $tables[$ti]; + + # This may fail if we have no table so do not check answer + $sth = $dbh->do("drop table $table_name" . $server->{'drop_attr'}); + print "Creating table $table_name\n" if ($opt_verbose); + do_many($dbh,@$array_ref); +} +$end_time=new Benchmark; +print "Time for create_table ($#tables): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(1,\$dbh); +} #### @@ -159,13 +168,6 @@ $sth = $dbh->do("delete from Bprime where Bprime.unique2 >= 1000") or $end_time=new Benchmark; print "Time to delete_big (1): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; -} -elsif ($opt_lock_tables) -{ - @tmp=@table_names; push(@tmp,@extra_names); - $sth = $dbh->do("LOCK TABLES " . join(" WRITE,", @tmp) . " WRITE") || - die $DBI::errstr; -} if ($opt_fast && defined($server->{vacuum})) { @@ -176,7 +178,9 @@ if ($opt_fast && defined($server->{vacuum})) #### Running the benchmark #### -print "Running actual benchmark\n"; +start_benchmark: + +print "Running the actual benchmark\n"; $loop_time= new Benchmark; $count=0; |