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