1#!/usr/bin/perl -w
2
3# Copyright (C) 2000 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#
27# This is a test with uses processes to insert, select and drop tables.
28#
29
30$opt_loop_count=100000; # Change this to make test harder/easier
31
32##################### Standard benchmark inits ##############################
33
34use DBI;
35use Getopt::Long;
36use Benchmark;
37
38package main;
39
40$opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert=
41  $opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0;
42$opt_host=""; $opt_db="test";
43
44GetOptions("host=s","db=s","loop-count=i","skip-create","skip-in","skip-delete",
45"verbose","fast-insert","lock-tables","debug","fast","force") || die "Aborted";
46$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$opt_force=undef;  # Ignore warnings from these
47
48print "Testing 5 multiple connections to a server with 1 insert, 2 drop/rename\n";
49print "1 select and 1 flush thread\n";
50
51$firsttable  = "bench_f1";
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",
61		      $opt_user, $opt_password,
62		    { PrintError => 0}) || die $DBI::errstr;
63  $dbh->do("drop table if exists $firsttable, ${firsttable}_1, ${firsttable}_2");
64
65  print "Creating table $firsttable 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))") || die $DBI::errstr;
67  $dbh->disconnect; $dbh=0;	# Close handler
68}
69$|= 1;				# Autoflush
70
71####
72#### Start the tests
73####
74
75test_insert() if (($pid=fork()) == 0); $work{$pid}="insert";
76test_drop(1) if (($pid=fork()) == 0); $work{$pid}="drop 1";
77test_drop(2) if (($pid=fork()) == 0); $work{$pid}="drop 2";
78test_select() if (($pid=fork()) == 0); $work{$pid}="select";
79test_flush() if (($pid=fork()) == 0); $work{$pid}="flush";
80
81$errors=0;
82while (($pid=wait()) != -1)
83{
84  $ret=$?/256;
85  print "thread '" . $work{$pid} . "' finished with exit code $ret\n";
86  $errors++ if ($ret != 0);
87}
88
89if (!$opt_skip_delete && !$errors)
90{
91  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
92		      $opt_user, $opt_password,
93		    { PrintError => 0}) || die $DBI::errstr;
94  $dbh->do("drop table $firsttable");
95  $dbh->disconnect; $dbh=0;	# Close handler
96}
97print ($errors ? "Test failed\n" :"Test ok\n");
98
99$end_time=new Benchmark;
100print "Total time: " .
101  timestr(timediff($end_time, $start_time),"noc") . "\n";
102
103exit(0);
104
105#
106# Insert records in the table
107#
108
109sub test_insert
110{
111  my ($dbh,$i);
112
113  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
114		      $opt_user, $opt_password,
115		    { PrintError => 0}) || die $DBI::errstr;
116  for ($i=0 ; $i < $opt_loop_count; $i++)
117  {
118    if (!$dbh->do("insert into $firsttable values ($i,'This is entry $i','')"))
119    {
120      print "Warning; Got error on insert: " . $dbh->errstr . "\n" if (! ($dbh->errstr =~ /doesn't exist/));
121    }
122  }
123  $dbh->disconnect; $dbh=0;
124  print "Test_insert: Inserted $i rows\n";
125  exit(0);
126}
127
128
129sub test_drop
130{
131  my ($id) = @_;
132  my ($dbh,$i,$sth,$error_counter,$sleep_time);
133
134  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
135		      $opt_user, $opt_password,
136		    { PrintError => 0}) || die $DBI::errstr;
137  $error_counter=0;
138  $sleep_time=2;
139  for ($i=0 ; $i < $opt_loop_count ; $i++)
140  {
141    sleep($sleep_time);
142    # Check if insert thread is ready
143    $sth=$dbh->prepare("select count(*) from $firsttable") || die "Got error on select from $firsttable: $dbh->errstr\n";
144    if (!$sth->execute || !(@row = $sth->fetchrow_array()) ||
145	!$row[0])
146    {
147      $sth->finish;
148      $sleep_time=1;
149      last if ($error_counter++ == 5);
150      next;
151    }
152    $sleep_time=2;
153    $sth->finish;
154
155    # Change to use a new table
156    $dbh->do("create table ${firsttable}_$id (id int(6) not null, info varchar(32), marker char(1), primary key(id))") || die $DBI::errstr;
157    $dbh->do("drop table if exists $firsttable") || die "Got error on drop table: $dbh->errstr\n";
158    if (!$dbh->do("alter table ${firsttable}_$id rename to $firsttable"))
159    {
160      print "Warning; Got error from alter table: " . $dbh->errstr . "\n" if (! ($dbh->errstr =~ /already exist/));
161      $dbh->do("drop table if exists ${firsttable}_$id") || die "Got error on drop table: $dbh->errstr\n";
162    }
163  }
164  $dbh->do("drop table if exists $firsttable,${firsttable}_$id") || die "Got error on drop table: $dbh->errstr\n";
165  $dbh->disconnect; $dbh=0;
166  print "Test_drop: Did a drop $i times\n";
167  exit(0);
168}
169
170
171#
172# select records
173#
174
175sub test_select
176{
177  my ($dbh,$i,$sth,@row,$error_counter,$sleep_time);
178
179  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
180		      $opt_user, $opt_password,
181		    { PrintError => 0}) || die $DBI::errstr;
182
183  $error_counter=0;
184  $sleep_time=3;
185  for ($i=0 ; $i < $opt_loop_count ; $i++)
186  {
187    sleep($sleep_time);
188    $sth=$dbh->prepare("select sum(t.id) from $firsttable as t,$firsttable as t2") || die "Got error on select: $dbh->errstr;\n";
189    if ($sth->execute)
190    {
191      @row = $sth->fetchrow_array();
192      $sth->finish;
193      $sleep_time=3;
194    }
195    else
196    {
197      print "Warning; Got error from select: " . $dbh->errstr . "\n" if (! ($dbh->errstr =~ /doesn't exist/));
198      $sth->finish;
199      last if ($error_counter++ == 5);
200      $sleep_time=1;
201    }
202  }
203  $dbh->disconnect; $dbh=0;
204  print "Test_select: ok\n";
205  exit(0);
206}
207
208#
209# flush records
210#
211
212sub test_flush
213{
214  my ($dbh,$i,$sth,@row,$error_counter,$sleep_time);
215
216  $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host",
217		      $opt_user, $opt_password,
218		    { PrintError => 0}) || die $DBI::errstr;
219
220  $error_counter=0;
221  $sleep_time=5;
222  for ($i=0 ; $i < $opt_loop_count ; $i++)
223  {
224    sleep($sleep_time);
225    $sth=$dbh->prepare("select count(*) from $firsttable") || die "Got error on prepar: $dbh->errstr;\n";
226    if ($sth->execute)
227    {
228      @row = $sth->fetchrow_array();
229      $sth->finish;
230      $sleep_time=5;
231      $dbh->do("flush tables $firsttable") || die "Got error on flush table: " . $dbh->errstr . "\n";
232    }
233    else
234    {
235      print "Warning; Got error from select: " . $dbh->errstr . "\n" if (! ($dbh->errstr =~ /doesn't exist/));
236      $sth->finish;
237      last if ($error_counter++ == 5);
238      $sleep_time=1;
239    }
240  }
241  $dbh->disconnect; $dbh=0;
242  print "Test_select: ok\n";
243  exit(0);
244}
245