1#!/usr/bin/env perl
2
3# Copyright (C) 2000, 2001 MySQL AB
4# Use is subject to license terms
5#
6# This program is free software; you can redistribute it and/or modify
7# it under the terms of the GNU General Public License as published by
8# the Free Software Foundation; version 2 of the License.
9#
10# This program 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
13# GNU General Public License for more details.
14#
15# You should have received a copy of the GNU General Public License
16# along with this program; if not, write to the Free Software
17# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1335  USA
18
19# This is a test for INSERT DELAYED
20#
21
22$opt_loop_count=10000; # Change this to make test harder/easier
23
24##################### Standard benchmark inits ##############################
25
26use DBI;
27use Getopt::Long;
28use Benchmark;
29
30package main;
31
32$opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert=
33  $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0;
34$opt_host=$opt_user=$opt_password=""; $opt_db="test";
35
36GetOptions("host=s","db=s","loop-count=i","skip-create","skip-in","skip-delete",
37"verbose","fast-insert","lock-tables","debug","fast","force") || die "Aborted";
38$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$opt_force=undef;  # Ignore warnings from these
39
40print "Testing 8 multiple connections to a server with 1 insert, 2 delayed\n";
41print "insert, 1 update, 1 delete, 1 flush tables and 3 select connections.\n";
42
43$firsttable  = "bench_f1";
44$secondtable = "bench_f2";
45
46####
47####  Start timeing and start test
48####
49
50$start_time=new Benchmark;
51if (!$opt_skip_create)
52{
53  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr;
54  $Mysql::QUIET = 1;
55  $dbh->do("drop table if exists $firsttable,$secondtable");
56  $Mysql::QUIET = 0;
57
58  print "Creating tables $firsttable and $secondtable in database $opt_db\n";
59  $dbh->do("create table $firsttable (id int(6) not null, info varchar(32), marker char(1), primary key(id))") or die $DBI::errstr;
60  $dbh->do("create table $secondtable (id int(6) not null, row int(3) not null,value double, primary key(id,row))") or die $DBI::errstr;
61
62  $dbh->disconnect;
63}
64$|= 1;				# Autoflush
65
66####
67#### Start the tests
68####
69
70test_1() if (($pid=fork()) == 0); $work{$pid}="insert";
71test_delayed_1() if (($pid=fork()) == 0); $work{$pid}="delayed_insert1";
72test_delayed_2() if (($pid=fork()) == 0); $work{$pid}="delayed_insert2";
73test_2() if (($pid=fork()) == 0); $work{$pid}="update";
74test_3() if (($pid=fork()) == 0); $work{$pid}="select1";
75test_4() if (($pid=fork()) == 0); $work{$pid}="select2";
76test_5() if (($pid=fork()) == 0); $work{$pid}="select3";
77test_del() if (($pid=fork()) == 0); $work{$pid}="delete";
78test_flush() if (($pid=fork()) == 0); $work{$pid}="flush";
79
80$errors=0;
81while (($pid=wait()) != -1)
82{
83  $ret=$?/256;
84  print "thread '" . $work{$pid} . "' finished with exit code $ret\n";
85  $errors++ if ($ret != 0);
86}
87
88if (!$opt_skip_delete && !$errors)
89{
90  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr;
91  $dbh->do("drop table $firsttable");
92  $dbh->do("drop table $secondtable");
93}
94print ($errors ? "Test failed\n" :"Test ok\n");
95
96$end_time=new Benchmark;
97print "Total time: " .
98  timestr(timediff($end_time, $start_time),"noc") . "\n";
99
100exit(0);
101
102#
103# Insert records in the two tables
104#
105
106sub test_1
107{
108  my ($dbh,$tmpvar,$rows,$found,$i);
109
110  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr;
111  $tmpvar=1;
112  $rows=$found=0;
113  for ($i=0 ; $i < $opt_loop_count; $i++)
114  {
115    $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000);
116    $dbh->do("insert into $firsttable values ($i,'This is entry $i','')") || die "Got error on insert: $DBI::errstr\n";
117    $row_count=($i % 7)+1;
118    $rows+=1+$row_count;
119    for ($j=0 ; $j < $row_count; $j++)
120    {
121      $dbh->do("insert into $secondtable values ($i,$j,0)") || die "Got error on insert: $DBI::errstr\n";
122    }
123  }
124  $dbh->disconnect;
125  print "Test_1: Inserted $rows rows\n";
126  exit(0);
127}
128
129
130sub test_delayed_1
131{
132  my ($dbh,$tmpvar,$rows,$found,$i,$id);
133
134  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr;
135  $tmpvar=1;
136  $rows=$found=0;
137  for ($i=0 ; $i < $opt_loop_count; $i++)
138  {
139    $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000);
140    $id=$i+$opt_loop_count;
141    $dbh->do("insert delayed into $firsttable values ($id,'This is entry $id','')") || die "Got error on insert: $DBI::errstr\n";
142    $row_count=($i % 7)+1;
143    $rows+=1+$row_count;
144    for ($j=0 ; $j < $row_count; $j++)
145    {
146      $dbh->do("insert into $secondtable values ($id,$j,0)") || die "Got error on insert: $DBI::errstr\n";
147    }
148    if (($tmpvar % 100) == 0)
149    {
150      $dbh->do("select max(info) from $firsttable") || die "Got error on select max(info): $DBI::errstr\n";
151      $dbh->do("select max(value) from $secondtable") || die "Got error on select max(info): $DBI::errstr\n";
152      $found+=2;
153    }
154  }
155  $dbh->disconnect;
156  print "Test_1: Inserted delayed $rows rows, found $found rows\n";
157  exit(0);
158}
159
160
161sub test_delayed_2
162{
163  my ($dbh,$tmpvar,$rows,$found,$i,$id);
164
165  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr;
166  $tmpvar=1;
167  $rows=$found=0;
168  for ($i=0 ; $i < $opt_loop_count; $i++)
169  {
170    $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000);
171    $id=$i+$opt_loop_count*2;
172    $dbh->do("insert delayed into $firsttable values ($id,'This is entry $id','')") || die "Got error on insert: $DBI::errstr\n";
173    $row_count=($i % 7)+1;
174    $rows+=1+$row_count;
175    for ($j=0 ; $j < $row_count; $j++)
176    {
177      $dbh->do("insert delayed into $secondtable values ($id,$j,0)") || die "Got error on insert: $DBI::errstr\n";
178    }
179    if (($tmpvar % 100) == 0)
180    {
181      $dbh->do("select max(info) from $firsttable") || die "Got error on select max(info): $DBI::errstr\n";
182      $dbh->do("select max(value) from $secondtable") || die "Got error on select max(info): $DBI::errstr\n";
183      $found+=2;
184    }
185  }
186  $dbh->disconnect;
187  print "Test_1: Inserted delayed $rows rows, found $found rows\n";
188  exit(0);
189}
190
191#
192# Update records in both tables
193#
194
195sub test_2
196{
197  my ($dbh,$id,$tmpvar,$rows,$found,$i,$max_id,$tmp,$sth,$count);
198
199  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr;
200  $tmpvar=111111;
201  $rows=$found=$max_id=$id=0;
202  for ($i=0 ; $i < $opt_loop_count ; $i++)
203  {
204    $tmp=(($tmpvar + 63) + $i)*3;
205    $tmp=$tmp-int($tmp/100000)*100000;
206    $tmpvar^= $tmp;
207    $tmp=$tmpvar - int($tmpvar/10)*10;
208    if ($max_id*$tmp == 0)
209    {
210      $max_id=0;
211      $sth=$dbh->prepare("select max(id) from $firsttable where marker=''");
212      $sth->execute() || die "Got error select max: $DBI::errstr\n";
213      if ((@row = $sth->fetchrow_array()) && defined($row[0]))
214      {
215	$found++;
216	$max_id=$id=$row[0];
217      }
218      $sth->finish;
219    }
220    else
221    {
222      $id= $tmpvar % ($max_id-1)+1;
223    }
224    if ($id)
225    {
226      ($count=$dbh->do("update $firsttable set marker='x' where id=$id")) || die "Got error update $firsttable: $DBI::errstr\n";
227      $rows+=$count;
228      if ($count > 0)
229      {
230	$count=$dbh->do("update $secondtable set value=$i where id=$id") || die "Got error update $firsttable: $DBI::errstr\n";
231	$rows+=$count;
232      }
233    }
234  }
235  $dbh->disconnect;
236  print "Test_2: Found $found rows, Updated $rows rows\n";
237  exit(0);
238}
239
240
241#
242# select records
243#
244
245sub test_3
246{
247  my ($dbh,$id,$tmpvar,$rows,$i,$count);
248  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr;
249  $tmpvar=222222;
250  $rows=0;
251  for ($i=0 ; $i < $opt_loop_count ; $i++)
252  {
253    $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000);
254    $id=$tmpvar % $opt_loop_count;
255    $count=$dbh->do("select id from $firsttable where id=$id") || die "Got error on select from $firsttable: $DBI::errstr\n";
256    $rows+=$count;
257  }
258  $dbh->disconnect;
259  print "Test_3: Found $rows rows\n";
260  exit(0);
261}
262
263
264#
265# Note that this uses row=1 and in some cases won't find any matching
266# records
267#
268
269sub test_4
270{
271  my ($dbh,$id,$tmpvar,$rows,$i,$count);
272  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr;
273  $tmpvar=333333;
274  $rows=0;
275  for ($i=0 ; $i < $opt_loop_count; $i++)
276  {
277    $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000);
278    $id=$tmpvar % $opt_loop_count;
279    $count=$dbh->do("select id from $secondtable where id=$id") || die "Got error on select from $secondtable: $DBI::errstr\n";
280    $rows+=$count;
281  }
282  $dbh->disconnect;
283  print "Test_4: Found $rows rows\n";
284  exit(0);
285}
286
287
288sub test_5
289{
290  my ($dbh,$id,$tmpvar,$rows,$i,$max_id,$count,$sth);
291  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr;
292  $tmpvar=444444;
293  $rows=$max_id=0;
294  for ($i=0 ; $i < $opt_loop_count ; $i++)
295  {
296    $tmpvar^= ((($tmpvar + 63) + $i)*3 % 100000);
297    if ($max_id == 0 || ($tmpvar % 10 == 0))
298    {
299      $sth=$dbh->prepare("select max(id) from $firsttable");
300      $sth->execute() || die "Got error select max: $DBI::errstr\n";
301      if ((@row = $sth->fetchrow_array()) && defined($row[0]))
302      {
303	$max_id=$id=$row[0];
304      }
305      else
306      {
307	$id=0;
308      }
309      $sth->finish;
310    }
311    else
312    {
313      $id= $tmpvar % $max_id;
314    }
315    $count=$dbh->do("select value from $firsttable,$secondtable where $firsttable.id=$id and $secondtable.id=$firsttable.id") || die "Got error on select from $secondtable: $DBI::errstr\n";
316    $rows+=$count;
317  }
318  $dbh->disconnect;
319  print "Test_5: Found $rows rows\n";
320  exit(0);
321}
322
323
324#
325# Delete the smallest row
326#
327
328sub test_del
329{
330  my ($dbh,$min_id,$i,$sth,$rows);
331  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host") || die $DBI::errstr;
332  $rows=0;
333  for ($i=0 ; $i < $opt_loop_count/3; $i++)
334  {
335    $sth=$dbh->prepare("select min(id) from $firsttable");
336    $sth->execute() || die "Got error on select from $firsttable: $DBI::errstr\n";
337    if ((@row = $sth->fetchrow_array()) && defined($row[0]))
338    {
339      $min_id=$row[0];
340    }
341    $sth->finish;
342    $dbh->do("delete from $firsttable where id = $min_id") || die "Got error on DELETE from $firsttable: $DBI::errstr\n";
343    $rows++;
344  }
345  $dbh->disconnect;
346  print "Test_del: Deleted $rows rows\n";
347  exit(0);
348}
349
350
351#
352# Do a flush tables once in a while
353#
354
355sub test_flush
356{
357  my ($dbh,$sth,$found1,$last_found1,$i,@row);
358  $found1=0; $last_found1=-1;
359
360  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
361		      $opt_user, $opt_password,
362		    { PrintError => 0}) || die $DBI::errstr;
363
364  for ($i=0; $found1 != $last_found1 ; $i++)
365  {
366    $sth=$dbh->prepare("flush tables") || die "Got error on prepare: $dbh->errstr\n";
367    $sth->execute || die $dbh->errstr;
368    $sth->finish;
369
370    $sth=$dbh->prepare("select count(*) from $firsttable") || die "Got error on prepare: $dbh->errstr\n";
371    $sth->execute || die $dbh->errstr;
372    @row = $sth->fetchrow_array();
373    $last_found1=$found1;
374    $found1= $row[0];
375    $sth->finish;
376    sleep(5);
377  }
378  $dbh->disconnect; $dbh=0;
379  print "flush: Did $i repair/checks\n";
380  exit(0);
381}
382