2
# Copyright (C) 2001, 2003 MySQL AB
4
# This library is free software; you can redistribute it and/or
5
# modify it under the terms of the GNU Library General Public
6
# License as published by the Free Software Foundation; version 2
9
# This library is distributed in the hope that it will be useful,
10
# but WITHOUT ANY WARRANTY; without even the implied warranty of
11
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12
# Library General Public License for more details.
14
# You should have received a copy of the GNU Library General Public
15
# License along with this library; if not, write to the Free
16
# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
19
# Test of transactions performance.
22
##################### Standard benchmark inits ##############################
29
$opt_groups=27; # Characters are 'A' -> Z
31
$opt_loop_count=10000; # Change this to make test harder/easier
32
$opt_medium_loop_count=100; # Change this to make test harder/easier
34
$pwd = cwd(); $pwd = "." if ($pwd eq '');
35
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
37
# Avoid warnings for variables in bench-init.pl
38
# (Only works with perl 5.6)
39
#our ($opt_small_test, $opt_small_tables, $opt_debug, $opt_force);
41
if ($opt_small_test || $opt_small_tables)
44
$opt_medium_loop_count/=10;
48
if (!$server->{transactions} && !$opt_force)
50
print "Test skipped because the database doesn't support transactions\n";
55
#### Connect and start timeing
58
$start_time=new Benchmark;
59
$dbh = $server->connect();
65
print "Creating tables\n";
66
$dbh->do("drop table bench1");
67
$dbh->do("drop table bench2");
69
do_many($dbh,$server->create("bench1",
71
"rev_idn int NOT NULL",
72
"region char(1) NOT NULL",
74
"updated tinyint NOT NULL"],
76
"unique (region,grp)"]));
77
do_many($dbh,$server->create("bench2",
79
"rev_idn int NOT NULL",
80
"region char(1) NOT NULL",
82
"updated tinyint NOT NULL"],
84
"unique (region,grp)"]));
86
$dbh->{AutoCommit} = 0;
89
### Test insert perfomance
92
test_insert("bench1","insert_commit",0);
93
test_insert("bench2","insert_autocommit",1);
97
my ($table, $test_name, $auto_commit)= @_;
98
my ($loop_time,$end_time,$id,$rev_id,$grp,$region);
100
$dbh->{AutoCommit}= $auto_commit;
101
$loop_time=new Benchmark;
103
for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ;
106
$grp=$id/$opt_groups;
107
$region=chr(65+$id%$opt_groups);
108
do_query($dbh,"insert into $table values ($id,$rev_id,'$region',$grp,0)");
111
$dbh->commit if (!$auto_commit);
112
$end_time=new Benchmark;
113
print "Time for $test_name ($opt_loop_count): " .
114
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
118
### Test rollback performance
121
print "Test transactions rollback performance\n" if($opt_debug);
124
## Insert rollback test
128
# Test is done by inserting 100 rows in a table with lots of rows and
129
# then doing a rollback on these
133
my ($id,$rev_id,$grp,$region,$end,$loop_time,$end_time,$commit_loop,$count);
135
$dbh->{AutoCommit} = 0;
136
$loop_time=new Benchmark;
137
$end=$opt_loop_count*2;
140
for ($commit_loop=1, $id=$opt_loop_count ; $id < $end ;
141
$id++, $commit_loop++)
144
$grp=$id/$opt_groups;
145
$region=chr(65+$id%$opt_groups);
146
do_query($dbh,"insert into bench1 values ($id,$rev_id,'$region',$grp,0)");
147
if ($commit_loop >= $opt_medium_loop_count)
154
if ($commit_loop > 1)
159
$end_time=new Benchmark;
160
print "Time for insert_rollback ($count:$opt_loop_count): " .
161
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
165
## Update rollback test
169
# Test is done by updating 100 rows in a table with lots of rows and
170
# then doing a rollback on these
174
my ($id,$loop_time,$end_time,$commit_loop,$count);
176
$dbh->{AutoCommit} = 0;
177
$loop_time=new Benchmark;
178
$end=$opt_loop_count*2;
181
for ($commit_loop=1, $id=0 ; $id < $opt_loop_count ; $id++, $commit_loop++)
183
do_query($dbh,"update bench1 set updated=2 where idn=$id");
184
if ($commit_loop >= $opt_medium_loop_count)
191
if ($commit_loop > 1)
196
$end_time=new Benchmark;
197
print "Time for update_rollback ($count:$opt_loop_count): " .
198
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
202
## Delete rollback test
206
# Test is done by deleting 100 rows in a table with lots of rows and
207
# then doing a rollback on these
211
my ($id,$loop_time,$end_time,$commit_loop,$count);
213
$dbh->{AutoCommit} = 0;
214
$loop_time=new Benchmark;
215
$end=$opt_loop_count*2;
218
for ($commit_loop=1, $id=0 ; $id < $opt_loop_count ; $id++, $commit_loop++)
220
do_query($dbh,"delete from bench1 where idn=$id");
221
if ($commit_loop >= $opt_medium_loop_count)
228
if ($commit_loop > 1)
233
$end_time=new Benchmark;
234
print "Time for delete_rollback ($count:$opt_loop_count): " .
235
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
240
### Test update perfomance
243
test_update("bench1","update_commit",0);
244
test_update("bench2","update_autocommit",1);
248
my ($table, $test_name, $auto_commit)= @_;
249
my ($loop_time,$end_time,$id);
251
$dbh->{AutoCommit}= $auto_commit;
252
$loop_time=new Benchmark;
254
for ($id=0 ; $id < $opt_loop_count ; $id++)
256
do_query($dbh,"update $table set updated=1 where idn=$id");
259
$dbh->commit if (!$auto_commit);
260
$end_time=new Benchmark;
261
print "Time for $test_name ($opt_loop_count): " .
262
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
266
### Test delete perfomance
269
test_delete("bench1","delete_commit",0);
270
test_delete("bench2","delete_autocommit",1);
274
my ($table, $test_name, $auto_commit)= @_;
275
my ($loop_time,$end_time,$id);
277
$dbh->{AutoCommit}= $auto_commit;
278
$loop_time=new Benchmark;
280
for ($id=0 ; $id < $opt_loop_count ; $id++)
282
do_query($dbh,"delete from $table where idn=$id");
284
$dbh->commit if (!$auto_commit);
285
$end_time=new Benchmark;
286
print "Time for $test_name ($opt_loop_count): " .
287
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
291
#### End of benchmark
294
$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
295
$sth = $dbh->do("drop table bench2" . $server->{'drop_attr'}) or die $DBI::errstr;
297
$dbh->disconnect; # close connection
298
end_benchmark($start_time);