diff options
Diffstat (limited to 'sql-bench/test-create.sh')
| -rw-r--r-- | sql-bench/test-create.sh | 277 |
1 files changed, 277 insertions, 0 deletions
diff --git a/sql-bench/test-create.sh b/sql-bench/test-create.sh new file mode 100644 index 00000000..09961f0d --- /dev/null +++ b/sql-bench/test-create.sh @@ -0,0 +1,277 @@ +#!/usr/bin/env perl +# Copyright (c) 2000-2003, 2006 MySQL AB, 2009 Sun Microsystems, Inc. +# Use is subject to license terms. +# +# 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; version 2 +# of the License. +# +# 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., 51 Franklin St, Fifth Floor, Boston, +# MA 02110-1335 USA +# +# This test is for testing how long it takes to create tables, +# make a count(*) from them and finally drop the tables. These +# commands will be done in different ways in this test. +# Using option --fast will drop all the tables in the end +# of this test with one command instead of making own +# 'drop' command for each and every table. +# By changing the variable '$table_amount' value you can make +# this test a lot harder/easier for your computer to drive. +# Note that when using value bigger than 64 for this variable +# will do 'drop table'-command in totally different way because of that +# how MySQL handles these commands. + +##################### Standard benchmark inits ############################## + +use Cwd; +use DBI; +use Benchmark; + +$opt_loop_count=10000; # Change this to make test harder/easier +# This is the default value for the amount of tables used in this test. + +$pwd = cwd(); $pwd = "." if ($pwd eq ''); +require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; + +$create_loop_count=$opt_loop_count; +if ($opt_small_test) +{ + $opt_loop_count/=100; + $create_loop_count/=1000; +} + +if ($opt_temporary_tables) +{ + $max_tables=min($limits->{'max_tables'},$opt_loop_count); +} +else +{ + $max_tables=min($limits->{'max_tables'},$opt_loop_count); + $max_tables=400; +} + +if ($opt_small_test) +{ + $max_tables=10; +} + + +print "Testing the speed of creating and dropping tables\n"; +print "Testing with $max_tables tables and $opt_loop_count loop count\n\n"; + +#### +#### Connect and start timeing +#### + +$dbh = $server->connect(); + +### Test how the database can handle many tables +### Create $max_tables ; Access all off them with a simple query +### and then drop the tables + +if ($opt_force) # If tables used in this test exist, drop 'em +{ + print "Okay..Let's make sure that our tables don't exist yet.\n\n"; + for ($i=1 ; $i <= max($max_tables, $create_loop_count) ; $i++) + { + $dbh->do("drop table bench_$i" . $server->{'drop_attr'}); + } +} + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(1,\$dbh); +} + +print "Testing create of tables\n"; + +$loop_time=$start_time=new Benchmark; + +for ($i=1 ; $i <= $max_tables ; $i++) +{ + if (do_many($dbh,$server->create("bench_$i", + ["i int NOT NULL", + "d double", + "f float", + "s char(10)", + "v varchar(100)"], + ["primary key (i)"]))) + { + # Got an error; Do cleanup + for ($i=1 ; $i <= $max_tables ; $i++) + { + $dbh->do("drop table bench_$i" . $server->{'drop_attr'}); + } + die "Test aborted"; + } +} + +$end_time=new Benchmark; +print "Time for create_MANY_tables ($max_tables): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(1,\$dbh); +} + +#### Here comes $max_tables couples of cont(*) to the tables. +#### We'll check how long it will take... +#### + +print "Accessing tables\n"; + +if ($limits->{'group_functions'}) +{ + $query="select count(*) from "; + $type="select_group_when_MANY_tables"; +} +else +{ + $query="select * from "; + $type="select_when_MANY_tables"; +} + +$loop_time=new Benchmark; +for ($i=1 ; $i <= $max_tables ; $i++) +{ + $sth = $dbh->do("$query bench_$i") or die $DBI::errstr; +} + +$end_time=new Benchmark; +print "Time to $type ($max_tables): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +#### +#### Now we are going to drop $max_tables tables; +#### + +print "Testing drop\n"; + +$loop_time=new Benchmark; + +if ($opt_fast && $server->{'limits'}->{'multi_drop'} && + $server->{'limits'}->{'query_size'} > 11+$max_tables*10) +{ + my $query="drop table bench_1"; + for ($i=2 ; $i <= $max_tables ; $i++) + { + $query.=",bench_$i"; + } + $sth = $dbh->do($query . $server->{'drop_attr'}) or die $DBI::errstr; +} +else +{ + for ($i=1 ; $i <= $max_tables ; $i++) + { + $sth = $dbh->do("drop table bench_$i" . $server->{'drop_attr'}) + or die $DBI::errstr; + } +} + + +$end_time=new Benchmark; +print "Time for drop_table_when_MANY_tables ($max_tables): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(1,\$dbh); +} + +#### We'll do first one 'create table' and then we'll drop it +#### away immediately. This loop shall be executed $opt_loop_count +#### times. + +print "Testing create+drop\n"; + +$loop_time=new Benchmark; + +for ($i=1 ; $i <= $create_loop_count ; $i++) +{ + do_many($dbh,$server->create("bench_$i", + ["i int NOT NULL", + "d double", + "f float", + "s char(10)", + "v varchar(100)"], + ["primary key (i)"])); + $sth = $dbh->do("drop table bench_$i" . $server->{'drop_attr'}) or die $DBI::errstr; +} + +$end_time=new Benchmark; +print "Time for create+drop ($create_loop_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(1,\$dbh); +} + +# +# Same test, but with a table with many keys +# + +my @fields=(); my @keys=(); +$keys=min($limits->{'max_index'},16); # 16 is more than enough +$seg= min($limits->{'max_index_parts'},$keys,16); # 16 is more than enough + +# 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,"field2 mediumint not null"); +push(@fields,"field3 smallint not null"); +push(@fields,"field4 char(16) not null"); +push(@fields,"field5 integer not null"); +push(@fields,"field6 float not null"); +push(@fields,"field7 double not null"); +for ($i=8 ; $i <= $keys ; $i++) +{ + push(@fields,"field$i char(5) not null"); # Should be relatively fair +} + +# Let first key contain many segments +my $query="primary key ("; +for ($i= 1 ; $i <= $seg ; $i++) +{ + $query.= "field$i,"; +} +substr($query,-1)=")"; +push (@keys,$query); + +#Create other keys +for ($i=2 ; $i <= $keys ; $i++) +{ + push(@keys,"index index$i (field$i)"); +} + +$loop_time=new Benchmark; +for ($i=1 ; $i <= $create_loop_count ; $i++) +{ + do_many($dbh,$server->create("bench_$i", \@fields, \@keys)); + $dbh->do("drop table bench_$i" . $server->{'drop_attr'}) or die $DBI::errstr; +} + +$end_time=new Benchmark; +print "Time for create_key+drop ($opt_loop_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(1,\$dbh); +} + +#### +#### End of benchmark +#### + +$dbh->disconnect; # close connection +end_benchmark($start_time); |