mirror of
https://github.com/MariaDB/server.git
synced 2025-01-15 19:42:28 +01:00
269 lines
6.7 KiB
Bash
269 lines
6.7 KiB
Bash
#!/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);
|