1#!/usr/bin/env perl
2# Copyright (c) 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-1335  USA
19#
20# Test of transactions performance.
21#
22
23##################### Standard benchmark inits ##############################
24
25use Cwd;
26use DBI;
27use Benchmark;
28#use warnings;
29
30$opt_groups=27;		    # Characters are 'A' -> Z
31
32$opt_loop_count=500000;	    # Change this to make test harder/easier
33$opt_medium_loop_count=10000; # Change this to make test harder/easier
34
35$pwd = cwd(); $pwd = "." if ($pwd eq '');
36require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
37
38# Avoid warnings for variables in bench-init.pl
39# (Only works with perl 5.6)
40#our ($opt_small_test, $opt_small_tables, $opt_debug, $opt_force);
41
42if ($opt_small_test || $opt_small_tables)
43{
44  $opt_loop_count/=100;
45  $opt_medium_loop_count/=10;
46}
47
48
49if (!$server->{transactions} && !$opt_force)
50{
51  print "Test skipped because the database doesn't support transactions\n";
52  exit(0);
53}
54
55####
56####  Connect and start timeing
57####
58
59$start_time=new Benchmark;
60$dbh = $server->connect();
61
62###
63### Create Table
64###
65
66print "Creating tables\n";
67$dbh->do("drop table bench1");
68$dbh->do("drop table bench2");
69
70do_many($dbh,$server->create("bench1",
71			     ["idn int NOT NULL",
72			      "rev_idn int NOT NULL",
73			      "region char(1) NOT NULL",
74			      "grp int NOT NULL",
75			      "updated tinyint NOT NULL"],
76			     ["primary key (idn)",
77			      "unique (region,grp)"]));
78do_many($dbh,$server->create("bench2",
79			     ["idn int NOT NULL",
80			      "rev_idn int NOT NULL",
81			      "region char(1) NOT NULL",
82			      "grp int NOT NULL",
83			      "updated tinyint NOT NULL"],
84			     ["primary key (idn)",
85			      "unique (region,grp)"]));
86
87$dbh->{AutoCommit} = 0;
88
89###
90### Test insert perfomance
91###
92
93test_insert("bench1","insert_commit",0);
94test_insert("bench2","insert_autocommit",1);
95
96sub test_insert
97{
98  my ($table, $test_name, $auto_commit)= @_;
99  my ($loop_time,$end_time,$id,$rev_id,$grp,$region);
100
101  $dbh->{AutoCommit}= $auto_commit;
102  $loop_time=new Benchmark;
103
104  for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ;
105       $id++,$rev_id--)
106  {
107    $grp=$id/$opt_groups;
108    $region=chr(65+$id%$opt_groups);
109    do_query($dbh,"insert into $table values ($id,$rev_id,'$region',$grp,0)");
110  }
111
112  $dbh->commit if (!$auto_commit);
113  $end_time=new Benchmark;
114  print "Time for $test_name  ($opt_loop_count): " .
115    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
116}
117
118###
119### Test rollback performance
120###
121
122print "Test transactions rollback performance\n" if($opt_debug);
123
124##
125## Insert rollback test
126##
127
128#
129# Test is done by inserting 100 rows in a table with lots of rows and
130# then doing a rollback on these
131#
132
133{
134  my ($id,$rev_id,$grp,$region,$end,$loop_time,$end_time,$commit_loop,$count);
135
136  $dbh->{AutoCommit} = 0;
137  $loop_time=new Benchmark;
138  $end=$opt_loop_count*2;
139  $count=0;
140
141  for ($commit_loop=1, $id=$opt_loop_count ; $id < $end ;
142       $id++, $commit_loop++)
143  {
144    $rev_id=$end-$id;
145    $grp=$id/$opt_groups;
146    $region=chr(65+$id%$opt_groups);
147    do_query($dbh,"insert into bench1 values ($id,$rev_id,'$region',$grp,0)");
148    if ($commit_loop >= $opt_medium_loop_count)
149    {
150      $dbh->rollback;
151      $commit_loop=0;
152      $count++;
153    }
154  }
155  if ($commit_loop > 1)
156  {
157    $dbh->rollback;
158    $count++;
159  }
160  $end_time=new Benchmark;
161  print "Time for insert_rollback ($count:$opt_loop_count): " .
162    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
163}
164
165##
166## Update rollback test
167##
168
169#
170# Test is done by updating 100 rows in a table with lots of rows and
171# then doing a rollback on these
172#
173
174{
175  my ($id,$loop_time,$end_time,$commit_loop,$count);
176
177  $dbh->{AutoCommit} = 0;
178  $loop_time=new Benchmark;
179  $end=$opt_loop_count*2;
180  $count=0;
181
182  for ($commit_loop=1, $id=0 ; $id < $opt_loop_count ; $id++, $commit_loop++)
183  {
184    do_query($dbh,"update bench1 set updated=2 where idn=$id");
185    if ($commit_loop >= $opt_medium_loop_count)
186    {
187      $dbh->rollback;
188      $commit_loop=0;
189      $count++;
190    }
191  }
192  if ($commit_loop > 1)
193  {
194    $dbh->rollback;
195    $count++;
196  }
197  $end_time=new Benchmark;
198  print "Time for update_rollback ($count:$opt_loop_count): " .
199    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
200}
201
202##
203## Delete rollback test
204##
205
206#
207# Test is done by deleting 100 rows in a table with lots of rows and
208# then doing a rollback on these
209#
210
211{
212  my ($id,$loop_time,$end_time,$commit_loop,$count);
213
214  $dbh->{AutoCommit} = 0;
215  $loop_time=new Benchmark;
216  $end=$opt_loop_count*2;
217  $count=0;
218
219  for ($commit_loop=1, $id=0 ; $id < $opt_loop_count ; $id++, $commit_loop++)
220  {
221    do_query($dbh,"delete from bench1 where idn=$id");
222    if ($commit_loop >= $opt_medium_loop_count)
223    {
224      $dbh->rollback;
225      $commit_loop=0;
226      $count++;
227    }
228  }
229  if ($commit_loop > 1)
230  {
231    $dbh->rollback;
232    $count++;
233  }
234  $end_time=new Benchmark;
235  print "Time for delete_rollback ($count:$opt_loop_count): " .
236    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
237}
238
239
240###
241### Test update perfomance
242###
243
244test_update("bench1","update_commit",0);
245test_update("bench2","update_autocommit",1);
246
247sub test_update
248{
249  my ($table, $test_name, $auto_commit)= @_;
250  my ($loop_time,$end_time,$id);
251
252  $dbh->{AutoCommit}= $auto_commit;
253  $loop_time=new Benchmark;
254
255  for ($id=0 ; $id < $opt_loop_count ; $id++)
256  {
257    do_query($dbh,"update $table set updated=1 where idn=$id");
258  }
259
260  $dbh->commit if (!$auto_commit);
261  $end_time=new Benchmark;
262  print "Time for $test_name  ($opt_loop_count): " .
263    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
264}
265
266###
267### Test delete perfomance
268###
269
270test_delete("bench1","delete_commit",0);
271test_delete("bench2","delete_autocommit",1);
272
273sub test_delete
274{
275  my ($table, $test_name, $auto_commit)= @_;
276  my ($loop_time,$end_time,$id);
277
278  $dbh->{AutoCommit}= $auto_commit;
279  $loop_time=new Benchmark;
280
281  for ($id=0 ; $id < $opt_loop_count ; $id++)
282 {
283    do_query($dbh,"delete from $table where idn=$id");
284  }
285  $dbh->commit if (!$auto_commit);
286  $end_time=new Benchmark;
287  print "Time for $test_name  ($opt_loop_count): " .
288   timestr(timediff($end_time, $loop_time),"all") . "\n\n";
289}
290
291####
292#### End of benchmark
293####
294
295$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
296$sth = $dbh->do("drop table bench2" . $server->{'drop_attr'}) or die $DBI::errstr;
297
298$dbh->disconnect;				# close connection
299end_benchmark($start_time);
300