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