release/src/router/mysql/sql-bench/test-alter-table.sh

   1 #!/usr/bin/perl
   2 # Copyright (c) 2000, 2001, 2003, 2006 MySQL AB, 2009 Sun Microsystems, Inc.
   3 # Use is subject to license terms.
   4 #
   5 # This library is free software; you can redistribute it and/or
   6 # modify it under the terms of the GNU Library General Public
   7 # License as published by the Free Software Foundation; version 2
   8 # of the License.
   9 #
  10 # This library is distributed in the hope that it will be useful,
  11 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  13 # Library General Public License for more details.
  14 #
  15 # You should have received a copy of the GNU Library General Public
  16 # License along with this library; if not, write to the Free
  17 # Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston,
  18 # MA 02110-1301, USA
  19 #
  20 # Test of alter table
  21 #
  22 ##################### Standard benchmark inits ##############################
  23
  24 use Cwd;
  25 use DBI;
  26 use Benchmark;
  27
  28 $opt_start_field_count=8;       # start with this many fields
  29 $opt_loop_count=100;            # How many tests to do
  30 $opt_row_count=1000;            # Rows in the table
  31 $opt_field_count=1000;          # Add until this many fields.
  32 $opt_time_limit=10*60;          # Don't wait more than 10 min for some tests
  33
  34 $pwd = cwd(); $pwd = "." if ($pwd eq '');
  35 require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
  36
  37 $opt_field_count=min($opt_field_count,$limits->{'max_columns'},
  38                      ($limits->{'query_size'}-30)/14);
  39 $opt_start_field_count=min($opt_start_field_count,$limits->{'max_index'});
  40
  41 if ($opt_small_test)
  42 {
  43   $opt_row_count/=10;
  44   $opt_field_count/=10;
  45 }
  46
  47 if (!$limits->{'alter_table'})
  48 {
  49   print("Some of the servers given with --cmp or --server doesn't support ALTER TABLE\nTest aborted\n\n");
  50   $start_time=new Benchmark;
  51   end_benchmark($start_time);
  52   exit 0;
  53 }
  54
  55 print "Testing of ALTER TABLE\n";
  56 print "Testing with $opt_field_count columns and $opt_row_count rows in $opt_loop_count steps\n";
  57
  58 ####
  59 #### Create a table and fill it with data
  60 ####
  61
  62 $dbh = $server->connect();
  63 @fields=();
  64 @index=();
  65 push(@fields,"i1 int not null");
  66 for ($i=2 ; $i <= $opt_start_field_count ; $i++)
  67 {
  68   push(@fields,"i${i} int not null");
  69 }
  70 $field_count= $opt_start_field_count;
  71
  72 $start_time=new Benchmark;
  73
  74 $dbh->do("drop table bench" . $server->{'drop_attr'});
  75 do_many($dbh,$server->create("bench",\@fields,\@index));
  76
  77 print "Insert data into the table\n";
  78
  79 $loop_time=new Benchmark;
  80
  81 if ($opt_fast && $server->{transactions})
  82 {
  83   $dbh->{AutoCommit} = 0;
  84   print "Transactions enabled\n" if ($opt_debug);
  85 }
  86
  87 for ($i=0 ; $i < $opt_row_count ; $i++)
  88 {
  89   $query="insert into bench values ( " . ("$i," x ($opt_start_field_count-1)) . "$i)";
  90   $dbh->do($query) or die $DBI::errstr;
  91 }
  92
  93 if ($opt_fast && $server->{transactions})
  94 {
  95   $dbh->commit;
  96   $dbh->{AutoCommit} = 1;
  97 }
  98
  99 $end_time=new Benchmark;
 100
 101 print "Time for insert ($opt_row_count)",
 102   timestr(timediff($end_time, $loop_time),"all") . "\n\n";
 103
 104
 105 ####
 106 #### Add fields to the table.
 107 ####
 108
 109 $loop_time=new Benchmark;
 110 $add= int(($opt_field_count-$opt_start_field_count)/$opt_loop_count)+1;
 111
 112 $multi_add=$server->{'limits'}->{'alter_add_multi_col'} == 1;
 113 if ($opt_fast)
 114 {
 115   $add=1 if (!$server->{'limits'}->{'alter_add_multi_col'});
 116 }
 117 else
 118 {
 119   $add=1 if (!$limits->{'alter_add_multi_col'});
 120 }
 121
 122 $count=0;
 123 while ($field_count < $opt_field_count)
 124 {
 125   $count++;
 126   $end=min($field_count+$add,$opt_field_count);
 127   $fields="";
 128   $tmp="ADD ";
 129   while ($field_count < $end)
 130   {
 131     $field_count++;
 132     $fields.=",$tmp i${field_count} integer";
 133     $tmp="" if (!$multi_add);                   # Adabas
 134   }
 135   do_query($dbh,"ALTER TABLE bench " . substr($fields,1));
 136   $end_time=new Benchmark;
 137   last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count,
 138                                          $opt_field_count/$add+1));
 139 }
 140
 141 $end_time=new Benchmark;
 142 if ($estimated)
 143 { print "Estimated time"; }
 144 else
 145 { print "Time"; }
 146 print " for alter_table_add ($count): " .
 147   timestr(timediff($end_time, $loop_time),"all") . "\n\n";
 148
 149 #
 150 # If estimated, fix table to have known number of fields
 151 #
 152 if ($estimated && $field_count < $opt_field_count)
 153 {
 154   $fields="";
 155   $tmp="ADD ";
 156   while ($field_count < $opt_field_count)
 157   {
 158     $field_count++;
 159     $fields.=",$tmp i${field_count} integer";
 160     $tmp="" if (!$multi_add);                   # Adabas
 161   }
 162   do_query($dbh,"ALTER TABLE bench " . substr($fields,1));
 163 }
 164
 165 ####
 166 #### Test adding and deleting index on the first $opt_start_fields
 167 ####
 168
 169 $loop_time=new Benchmark;
 170
 171 $count= 0;
 172 for ($i=1; $i <= $opt_start_field_count ; $i++)
 173 {
 174   $dbh->do("CREATE INDEX bench_ind$i ON bench (i${i})") || die $DBI::errstr;
 175 }
 176
 177 $end_time=new Benchmark;
 178 print "Time for create_index ($opt_start_field_count): " .
 179   timestr(timediff($end_time, $loop_time),"all") . "\n\n";
 180
 181 $loop_time=new Benchmark;
 182 for ($i=1; $i <= $opt_start_field_count ; $i++)
 183 {
 184   $dbh->do($server->drop_index("bench","bench_ind$i")) || die $DBI::errstr;
 185 }
 186
 187 $end_time=new Benchmark;
 188 print "Time for drop_index ($opt_start_field_count): " .
 189   timestr(timediff($end_time, $loop_time),"all") . "\n\n";
 190
 191 ####
 192 #### Delete fields from the table
 193 ####
 194
 195 goto skip_dropcol if (!$limits->{'alter_table_dropcol'});
 196
 197 $loop_time=new Benchmark;
 198
 199 $count=0;
 200 while ($field_count > $opt_start_field_count)
 201 {
 202   $count++;
 203   $end=max($field_count-$add,$opt_start_field_count);
 204   $fields="";
 205   while(--$field_count >= $end)
 206   {
 207     $fields.=",DROP i${field_count}";
 208   }
 209   $dbh->do("ALTER TABLE bench " . substr($fields,1) . $server->{'drop_attr'})
 210   || die $DBI::errstr;
 211   $end_time=new Benchmark;
 212   last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count,
 213                                          $opt_field_count/$add+1));
 214 }
 215
 216 $end_time=new Benchmark;
 217 if ($estimated)
 218 { print "Estimated time"; }
 219 else
 220 { print "Time"; }
 221 print " for alter_table_drop ($count): " .
 222   timestr(timediff($end_time, $loop_time),"all") . "\n\n";
 223
 224 skip_dropcol:
 225
 226 ################################ END ###################################
 227 ####
 228 #### End of the test...Finally print time used to execute the
 229 #### whole test.
 230
 231 $dbh->do("drop table bench" . $server->{'drop_attr'});
 232
 233 $dbh->disconnect;
 234
 235 end_benchmark($start_time);