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