test-transactions.sh 6.99 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
#!@PERL@
# Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
#
# 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; either
# version 2 of the License, or (at your option) any later version.
#
# 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., 59 Temple Place - Suite 330, Boston,
# MA 02111-1307, USA
#
# Test of transactions performance.
#

##################### Standard benchmark inits ##############################

24
use Cwd;
25 26
use DBI;
use Benchmark;
unknown's avatar
unknown committed
27
#use warnings;
28 29 30

$opt_groups=27;		    # Characters are 'A' -> Z

unknown's avatar
unknown committed
31
$opt_loop_count=10000;	    # Change this to make test harder/easier
32 33
$opt_medium_loop_count=100; # Change this to make test harder/easier

34
$pwd = cwd(); $pwd = "." if ($pwd eq '');
35 36 37
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";

# Avoid warnings for variables in bench-init.pl
unknown's avatar
unknown committed
38 39
# (Only works with perl 5.6)
#our ($opt_small_test, $opt_small_tables, $opt_debug, $opt_force);
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117

if ($opt_small_test || $opt_small_tables)
{
  $opt_loop_count/=100;
  $opt_medium_loop_count/=10;
}


if (!$server->{transactions} && !$opt_force)
{
  print "Test skipped because the database doesn't support transactions\n";
  exit(0);
}

####
####  Connect and start timeing
####

$start_time=new Benchmark;
$dbh = $server->connect();

###
### Create Table
###

print "Creating tables\n";
$dbh->do("drop table bench1");
$dbh->do("drop table bench2");

do_many($dbh,$server->create("bench1",
			     ["idn int NOT NULL",
			      "rev_idn int NOT NULL",
			      "region char(1) NOT NULL",
			      "grp int NOT NULL",
			      "updated tinyint NOT NULL"],
			     ["primary key (idn)",
			      "unique (region,grp)"]));
do_many($dbh,$server->create("bench2",
			     ["idn int NOT NULL",
			      "rev_idn int NOT NULL",
			      "region char(1) NOT NULL",
			      "grp int NOT NULL",
			      "updated tinyint NOT NULL"],
			     ["primary key (idn)",
			      "unique (region,grp)"]));

$dbh->{AutoCommit} = 0;

###
### Test insert perfomance
###

test_insert("bench1","insert_commit",0);
test_insert("bench2","insert_autocommit",1);

sub test_insert
{
  my ($table, $test_name, $auto_commit)= @_;
  my ($loop_time,$end_time,$id,$rev_id,$grp,$region);

  $dbh->{AutoCommit}= $auto_commit;
  $loop_time=new Benchmark;

  for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ;
       $id++,$rev_id--)
  {
    $grp=$id/$opt_groups;
    $region=chr(65+$id%$opt_groups);
    do_query($dbh,"insert into $table values ($id,$rev_id,'$region',$grp,0)");
  }

  $dbh->commit if (!$auto_commit);
  $end_time=new Benchmark;
  print "Time for $test_name  ($opt_loop_count): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
}

###
unknown's avatar
unknown committed
118
### Test rollback performance
119 120
###

unknown's avatar
unknown committed
121 122 123 124 125 126 127 128 129 130
print "Test transactions rollback performance\n" if($opt_debug);

##
## Insert rollback test
##

#
# Test is done by inserting 100 rows in a table with lots of rows and
# then doing a rollback on these
#
131 132

{
unknown's avatar
unknown committed
133
  my ($id,$rev_id,$grp,$region,$end,$loop_time,$end_time,$commit_loop,$count);
134

unknown's avatar
unknown committed
135
  $dbh->{AutoCommit} = 0;
136
  $loop_time=new Benchmark;
unknown's avatar
unknown committed
137 138
  $end=$opt_loop_count*2;
  $count=0;
139

unknown's avatar
unknown committed
140 141
  for ($commit_loop=1, $id=$opt_loop_count ; $id < $end ;
       $id++, $commit_loop++)
142
  {
unknown's avatar
unknown committed
143 144 145 146 147 148 149 150 151 152 153 154 155 156 157
    $rev_id=$end-$id;
    $grp=$id/$opt_groups;
    $region=chr(65+$id%$opt_groups);
    do_query($dbh,"insert into bench1 values ($id,$rev_id,'$region',$grp,0)");
    if ($commit_loop >= $opt_medium_loop_count)
    {
      $dbh->rollback;
      $commit_loop=0;
      $count++;
    }
  }
  if ($commit_loop > 1)
  {
    $dbh->rollback;
    $count++;
158 159
  }
  $end_time=new Benchmark;
unknown's avatar
unknown committed
160
  print "Time for insert_rollback ($count:$opt_loop_count): " .
161 162 163
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
}

unknown's avatar
unknown committed
164 165 166
##
## Update rollback test
##
167

unknown's avatar
unknown committed
168 169 170 171
#
# Test is done by updating 100 rows in a table with lots of rows and
# then doing a rollback on these
#
172 173

{
unknown's avatar
unknown committed
174
  my ($id,$loop_time,$end_time,$commit_loop,$count);
175

unknown's avatar
unknown committed
176
  $dbh->{AutoCommit} = 0;
177
  $loop_time=new Benchmark;
unknown's avatar
unknown committed
178 179
  $end=$opt_loop_count*2;
  $count=0;
180

unknown's avatar
unknown committed
181 182 183 184 185 186 187 188 189 190 191 192 193 194
  for ($commit_loop=1, $id=0 ; $id < $opt_loop_count ; $id++, $commit_loop++)
  {
    do_query($dbh,"update bench1 set updated=2 where idn=$id");
    if ($commit_loop >= $opt_medium_loop_count)
    {
      $dbh->rollback;
      $commit_loop=0;
      $count++;
    }
  }
  if ($commit_loop > 1)
  {
    $dbh->rollback;
    $count++;
195 196
  }
  $end_time=new Benchmark;
unknown's avatar
unknown committed
197 198
  print "Time for update_rollback ($count:$opt_loop_count): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
199 200 201
}

##
unknown's avatar
unknown committed
202
## Delete rollback test
203 204 205
##

#
unknown's avatar
unknown committed
206
# Test is done by deleting 100 rows in a table with lots of rows and
207 208 209 210
# then doing a rollback on these
#

{
unknown's avatar
unknown committed
211
  my ($id,$loop_time,$end_time,$commit_loop,$count);
212 213 214 215 216 217

  $dbh->{AutoCommit} = 0;
  $loop_time=new Benchmark;
  $end=$opt_loop_count*2;
  $count=0;

unknown's avatar
unknown committed
218
  for ($commit_loop=1, $id=0 ; $id < $opt_loop_count ; $id++, $commit_loop++)
219
  {
unknown's avatar
unknown committed
220
    do_query($dbh,"delete from bench1 where idn=$id");
221 222 223 224 225 226 227 228 229 230 231 232 233
    if ($commit_loop >= $opt_medium_loop_count)
    {
      $dbh->rollback;
      $commit_loop=0;
      $count++;
    }
  }
  if ($commit_loop > 1)
  {
    $dbh->rollback;
    $count++;
  }
  $end_time=new Benchmark;
unknown's avatar
unknown committed
234
  print "Time for delete_rollback ($count:$opt_loop_count): " .
235 236 237
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
}

unknown's avatar
unknown committed
238

239
###
unknown's avatar
unknown committed
240
### Test update perfomance
241 242
###

unknown's avatar
unknown committed
243 244
test_update("bench1","update_commit",0);
test_update("bench2","update_autocommit",1);
245

unknown's avatar
unknown committed
246 247 248 249
sub test_update
{
  my ($table, $test_name, $auto_commit)= @_;
  my ($loop_time,$end_time,$id);
250

unknown's avatar
unknown committed
251
  $dbh->{AutoCommit}= $auto_commit;
252 253
  $loop_time=new Benchmark;

unknown's avatar
unknown committed
254
  for ($id=0 ; $id < $opt_loop_count ; $id++)
255
  {
unknown's avatar
unknown committed
256
    do_query($dbh,"update $table set updated=1 where idn=$id");
257 258
  }

unknown's avatar
unknown committed
259
  $dbh->commit if (!$auto_commit);
260
  $end_time=new Benchmark;
unknown's avatar
unknown committed
261
  print "Time for $test_name  ($opt_loop_count): " .
262
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
unknown's avatar
unknown committed
263
}
264

unknown's avatar
unknown committed
265 266 267
###
### Test delete perfomance
###
268

unknown's avatar
unknown committed
269 270
test_delete("bench1","delete_commit",0);
test_delete("bench2","delete_autocommit",1);
271

unknown's avatar
unknown committed
272 273 274 275
sub test_delete
{
  my ($table, $test_name, $auto_commit)= @_;
  my ($loop_time,$end_time,$id);
276

unknown's avatar
unknown committed
277 278
  $dbh->{AutoCommit}= $auto_commit;
  $loop_time=new Benchmark;
279

unknown's avatar
unknown committed
280 281 282 283 284
  for ($id=0 ; $id < $opt_loop_count ; $id++)
 {
    do_query($dbh,"delete from $table where idn=$id");
  }
  $dbh->commit if (!$auto_commit);
285
  $end_time=new Benchmark;
unknown's avatar
unknown committed
286 287
  print "Time for $test_name  ($opt_loop_count): " .
   timestr(timediff($end_time, $loop_time),"all") . "\n\n";
288 289 290 291 292 293 294 295 296 297 298
}

####
#### End of benchmark
####

$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
$sth = $dbh->do("drop table bench2" . $server->{'drop_attr'}) or die $DBI::errstr;

$dbh->disconnect;				# close connection
end_benchmark($start_time);