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