diff options
Diffstat (limited to '')
| -rw-r--r-- | sql-bench/test-alter-table.sh | 269 |
1 files changed, 269 insertions, 0 deletions
diff --git a/sql-bench/test-alter-table.sh b/sql-bench/test-alter-table.sh new file mode 100644 index 00000000..350c6fae --- /dev/null +++ b/sql-bench/test-alter-table.sh @@ -0,0 +1,269 @@ +#!/usr/bin/env perl +# Copyright (c) 2000, 2001, 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 +# +# Test of alter table +# +##################### Standard benchmark inits ############################## + +use Cwd; +use DBI; +use Benchmark; + +$opt_start_field_count=8; # start with this many fields +$opt_loop_count=10000; # How many tests to do +$opt_row_count=1000; # Rows in the table +$opt_field_count=1000; # Add until this many fields. +$opt_time_limit=10*60; # Don't wait more than 10 min for some tests + +$pwd = cwd(); $pwd = "." if ($pwd eq ''); +require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; + +$opt_field_count=min($opt_field_count,$limits->{'max_columns'}, + ($limits->{'query_size'}-30)/14); +$opt_start_field_count=min($opt_start_field_count,$limits->{'max_index'}); + +if ($opt_small_test) +{ + $opt_row_count/=10; + $opt_field_count/=10; +} + +if (!$limits->{'alter_table'}) +{ + print("Some of the servers given with --cmp or --server doesn't support ALTER TABLE\nTest aborted\n\n"); + $start_time=new Benchmark; + end_benchmark($start_time); + exit 0; +} + +print "Testing of ALTER TABLE\n"; +print "Testing with $opt_field_count columns and $opt_row_count rows in $opt_loop_count steps\n"; + +#### +#### Create a table and fill it with data +#### + +$dbh = $server->connect(); +@fields=(); +@index=(); +push(@fields,"i1 int not null"); +for ($i=2 ; $i <= $opt_start_field_count ; $i++) +{ + push(@fields,"i${i} int not null"); +} +$field_count= $opt_start_field_count; + +$start_time=new Benchmark; + +$dbh->do("drop table bench" . $server->{'drop_attr'}); +do_many($dbh,$server->create("bench",\@fields,\@index)); + +print "Insert data into the table\n"; + +$loop_time=new Benchmark; + +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; + print "Transactions enabled\n" if ($opt_debug); +} + +for ($i=0 ; $i < $opt_row_count ; $i++) +{ + $query="insert into bench values ( " . ("$i," x ($opt_start_field_count-1)) . "$i)"; + $dbh->do($query) or die $DBI::errstr; +} + +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + +$end_time=new Benchmark; + +print "Time for insert ($opt_row_count)", + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + + +#### +#### Add fields to the table. +#### + +$loop_time=new Benchmark; +$add= int(($opt_field_count-$opt_start_field_count)/$opt_loop_count)+1; + +$multi_add=$server->{'limits'}->{'alter_add_multi_col'} == 1; +if ($opt_fast) +{ + $add=1 if (!$server->{'limits'}->{'alter_add_multi_col'}); +} +else +{ + $add=1 if (!$limits->{'alter_add_multi_col'}); +} + +$count=0; +while ($field_count < $opt_field_count) +{ + $count++; + $end=min($field_count+$add,$opt_field_count); + $fields=""; + $tmp="ADD "; + while ($field_count < $end) + { + $field_count++; + $fields.=",$tmp i${field_count} integer"; + $tmp="" if (!$multi_add); # Adabas + } + do_query($dbh,"ALTER TABLE bench " . substr($fields,1)); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count, + $opt_field_count/$add+1)); +} + +$end_time=new Benchmark; +if ($estimated) +{ print "Estimated time"; } +else +{ print "Time"; } +print " for alter_table_add ($count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +# +# If estimated, fix table to have known number of fields +# +if ($estimated && $field_count < $opt_field_count) +{ + $fields=""; + $tmp="ADD "; + while ($field_count < $opt_field_count) + { + $field_count++; + $fields.=",$tmp i${field_count} integer"; + $tmp="" if (!$multi_add); # Adabas + } + do_query($dbh,"ALTER TABLE bench " . substr($fields,1)); +} + +#### +#### Test adding and deleting index on the first $opt_start_fields +#### + +$loop_time=new Benchmark; + +$count= 0; +for ($i=1; $i <= $opt_start_field_count ; $i++) +{ + $dbh->do("CREATE INDEX bench_ind$i ON bench (i${i})") || die $DBI::errstr; +} + +$end_time=new Benchmark; +print "Time for create_index ($opt_start_field_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +$loop_time=new Benchmark; +for ($i=1; $i <= $opt_start_field_count ; $i++) +{ + $dbh->do($server->drop_index("bench","bench_ind$i")) || die $DBI::errstr; +} + +$end_time=new Benchmark; +print "Time for drop_index ($opt_start_field_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +#### +#### Delete fields from the table +#### + +goto skip_dropcol if (!$limits->{'alter_table_dropcol'}); + +$loop_time=new Benchmark; + +$count=0; +while ($field_count > $opt_start_field_count) +{ + $count++; + $end=max($field_count-$add,$opt_start_field_count); + $fields=""; + while ($field_count > $end) + { + $fields.=",DROP i${field_count}"; + $field_count--; + } + $dbh->do("ALTER TABLE bench " . substr($fields,1) . $server->{'drop_attr'}) + || die $DBI::errstr; + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count, + $opt_field_count/$add+1)); +} + +$end_time=new Benchmark; +if ($estimated) +{ print "Estimated time"; } +else +{ print "Time"; } +print " for alter_table_drop ($count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +#### +#### Add fields in middle of the table +#### + +goto skip_dropcol if (!$limits->{'alter_table_after'}); + +$count=0; +while ($field_count < $opt_field_count) +{ + $count++; + $end=min($field_count+$add,$opt_field_count); + $fields=""; + $tmp="ADD "; + while ($field_count < $end) + { + $field_count++; + $fields.=",$tmp i${field_count} integer after i1"; + $tmp="" if (!$multi_add); # Adabas + } + do_query($dbh,"ALTER TABLE bench " . substr($fields,1)); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count, + $opt_field_count/$add+1)); +} + +$end_time=new Benchmark; +if ($estimated) +{ print "Estimated time"; } +else +{ print "Time"; } +print " for alter_table_add_in_middle ($count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +skip_dropcol: + +################################ END ################################### +#### +#### End of the test...Finally print time used to execute the +#### whole test. + +$dbh->do("drop table bench" . $server->{'drop_attr'}); + +$dbh->disconnect; + +end_benchmark($start_time); |