summaryrefslogtreecommitdiffstats
path: root/sql-bench/test-alter-table.sh
diff options
context:
space:
mode:
Diffstat (limited to 'sql-bench/test-alter-table.sh')
-rw-r--r--sql-bench/test-alter-table.sh269
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);