1#!/usr/bin/perl -w 2 3# Copyright (c) 2001, 2006 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 many processes to test a MySQL server. 28# 29# Tested a lot with: --threads=30 30 31$opt_loop_count=500000; # Change this to make test harder/easier 32 33##################### Standard benchmark inits ############################## 34 35use DBI; 36use Getopt::Long; 37use Benchmark; 38 39package main; 40 41$opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert= 42$opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=0; 43$opt_threads=5; 44$opt_host=$opt_user=$opt_password=""; $opt_db="test"; 45 46GetOptions("host=s","db=s","user=s","password=s","loop-count=i","skip-create","skip-in","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force","threads=i") || die "Aborted"; 47$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$opt_force=undef; # Ignore warnings from these 48 49print "Test of multiple connections that test the following things:\n"; 50print "insert, select, delete, update, alter, check, repair and flush\n"; 51 52@testtables = ( ["bench_f31", ""], 53 ["bench_f32", "row_format=fixed"], 54 ["bench_f33", "delay_key_write=1"], 55 ["bench_f34", "checksum=1"], 56 ["bench_f35", "delay_key_write=1"]); 57$abort_table="bench_f39"; 58 59$numtables = $#testtables+1; 60srand 100; # Make random numbers repeatable 61 62#### 63#### Start timeing and start test 64#### 65 66$start_time=new Benchmark; 67$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 68 $opt_user, $opt_password, 69 { PrintError => 0}) || die $DBI::errstr; 70if (!$opt_skip_create) 71{ 72 my $table_def; 73 foreach $table_def (@testtables) 74 { 75 my ($table,$extra)= ($table_def->[0], $table_def->[1]); 76 print "Creating table $table in database $opt_db\n"; 77 $dbh->do("drop table if exists $table"); 78 $dbh->do("create table $table". 79 " (id int(6) not null auto_increment,". 80 " info varchar(32)," . 81 " marker timestamp," . 82 " flag int not null," . 83 " primary key(id)) $extra") 84 85 or die $DBI::errstr; 86 # One row in the table will make future tests easier 87 $dbh->do("insert into $table (id) values (null)") 88 or die $DBI::errstr; 89 } 90 # Create the table we use to signal that we should end the test 91 $dbh->do("drop table if exists $abort_table"); 92 $dbh->do("create table $abort_table (id int(6) not null) ENGINE=heap") || 93 die $DBI::errstr; 94} 95 96$dbh->do("delete from $abort_table"); 97$dbh->disconnect; $dbh=0; # Close handler 98$|= 1; # Autoflush 99 100#### 101#### Start the tests 102#### 103 104for ($i=0 ; $i < $opt_threads ; $i ++) 105{ 106 test_insert() if (($pid=fork()) == 0); $work{$pid}="insert"; 107} 108for ($i=0 ; $i < $numtables ; $i ++) 109{ 110 test_insert($i,$i) if (($pid=fork()) == 0); $work{$pid}="insert_one"; 111} 112for ($i=0 ; $i < $opt_threads ; $i ++) 113{ 114 test_select() if (($pid=fork()) == 0); $work{$pid}="select_key"; 115} 116test_join() if (($pid=fork()) == 0); $work{$pid}="test_join"; 117test_select_count() if (($pid=fork()) == 0); $work{$pid}="select_count"; 118test_delete() if (($pid=fork()) == 0); $work{$pid}="delete"; 119test_update() if (($pid=fork()) == 0); $work{$pid}="update"; 120test_flush() if (($pid=fork()) == 0); $work{$pid}= "flush"; 121test_check() if (($pid=fork()) == 0); $work{$pid}="check"; 122test_repair() if (($pid=fork()) == 0); $work{$pid}="repair"; 123test_alter() if (($pid=fork()) == 0); $work{$pid}="alter"; 124#test_database("test2") if (($pid=fork()) == 0); $work{$pid}="check_database"; 125 126print "Started " . ($opt_threads*2+4) . " threads\n"; 127 128$errors=0; 129$running_insert_threads=$opt_threads+$numtables; 130while (($pid=wait()) != -1) 131{ 132 $ret=$?/256; 133 print "thread '" . $work{$pid} . "' finished with exit code $ret\n"; 134 if ($work{$pid} =~ /^insert/) 135 { 136 if (!--$running_insert_threads) 137 { 138 # Time to stop other threads 139 signal_abort(); 140 } 141 } 142 $errors++ if ($ret != 0); 143} 144 145# 146# Cleanup 147# 148 149if (!$opt_skip_delete && !$errors) 150{ 151 my $table_def; 152 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 153 $opt_user, $opt_password, 154 { PrintError => 0}) || die $DBI::errstr; 155 156 $dbh->do("drop table $abort_table"); 157 foreach $table_def (@testtables) 158 { 159 $dbh->do("drop table " . $table_def->[0]); 160 } 161 $dbh->disconnect; $dbh=0; # Close handler 162} 163 164print ($errors ? "Test failed\n" :"Test ok\n"); 165$end_time=new Benchmark; 166print "Total time: " . 167 timestr(timediff($end_time, $start_time),"noc") . "\n"; 168 169exit(0); 170 171 172# 173# Insert records in the table 174# 175 176sub test_insert 177{ 178 my ($from_table,$to_table)= @_; 179 my ($dbh,$i,$j,$count,$table_def,$table); 180 181 if (!defined($from_table)) 182 { 183 $from_table=0; $to_table=$numtables-1; 184 } 185 186 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 187 $opt_user, $opt_password, 188 { PrintError => 0}) || die $DBI::errstr; 189 190 for ($i=$count=0 ; $i < $opt_loop_count; $i++) 191 { 192 for ($j= $from_table ; $j <= $to_table ; $j++) 193 { 194 my ($table)= ($testtables[$j]->[0]); 195 $dbh->do("insert into $table values (NULL,'This is entry $i','',0)") || die "Got error on insert: $DBI::errstr\n"; 196 $count++; 197 } 198 } 199 $dbh->disconnect; $dbh=0; 200 print "Test_insert: Inserted $count rows\n"; 201 exit(0); 202} 203 204 205# 206# select records 207# Do continously select over all tables as long as there is changed 208# rows in the table 209# 210 211sub test_select 212{ 213 my ($dbh, $i, $j, $count, $loop); 214 215 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 216 $opt_user, $opt_password, 217 { PrintError => 0}) || die $DBI::errstr; 218 219 $count_query=make_count_query($numtables); 220 $count=0; 221 $loop=9999; 222 223 $i=0; 224 while (($i++ % 100) || !test_if_abort($dbh)) 225 { 226 if ($loop++ >= 100) 227 { 228 $loop=0; 229 $row_counts=simple_query($dbh, $count_query); 230 } 231 for ($j=0 ; $j < $numtables ; $j++) 232 { 233 my ($id)= int rand $row_counts->[$j]; 234 my ($table)= $testtables[$j]->[0]; 235 simple_query($dbh, "select id,info from $table where id=$id"); 236 $count++; 237 } 238 } 239 $dbh->disconnect; $dbh=0; 240 print "Test_select: Executed $count selects\n"; 241 exit(0); 242} 243 244# 245# Do big select count(distinct..) over the table 246# 247 248sub test_select_count 249{ 250 my ($dbh, $i, $j, $count, $loop); 251 252 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 253 $opt_user, $opt_password, 254 { PrintError => 0}) || die $DBI::errstr; 255 256 $count=0; 257 $i=0; 258 while (!test_if_abort($dbh)) 259 { 260 for ($j=0 ; $j < $numtables ; $j++) 261 { 262 my ($table)= $testtables[$j]->[0]; 263 simple_query($dbh, "select count(distinct marker),count(distinct id),count(distinct info) from $table"); 264 $count++; 265 } 266 sleep(20); # This query is quite slow 267 } 268 $dbh->disconnect; $dbh=0; 269 print "Test_select: Executed $count select count(distinct) queries\n"; 270 exit(0); 271} 272 273# 274# select records 275# Do continously joins between the first and second table 276# 277 278sub test_join 279{ 280 my ($dbh, $i, $j, $count, $loop); 281 282 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 283 $opt_user, $opt_password, 284 { PrintError => 0}) || die $DBI::errstr; 285 286 $count_query=make_count_query($numtables); 287 $count=0; 288 $loop=9999; 289 290 $i=0; 291 while (($i++ % 100) || !test_if_abort($dbh)) 292 { 293 if ($loop++ >= 100) 294 { 295 $loop=0; 296 $row_counts=simple_query($dbh, $count_query); 297 } 298 for ($j=0 ; $j < $numtables-1 ; $j++) 299 { 300 my ($id)= int rand $row_counts->[$j]; 301 my ($t1,$t2)= ($testtables[$j]->[0],$testtables[$j+1]->[0]); 302 simple_query($dbh, "select $t1.id,$t2.info from $t1, $t2 where $t1.id=$t2.id and $t1.id=$id"); 303 $count++; 304 } 305 } 306 $dbh->disconnect; $dbh=0; 307 print "Test_join: Executed $count joins\n"; 308 exit(0); 309} 310 311# 312# Delete 1-5 rows from the first 2 tables. 313# Test ends when the number of rows for table 3 didn't change during 314# one loop 315# 316 317sub test_delete 318{ 319 my ($dbh, $i,$j, $row_counts, $count_query, $table_count, $count); 320 321 $table_count=2; 322 $count=0; 323 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 324 $opt_user, $opt_password, 325 { PrintError => 0}) || die $DBI::errstr; 326 327 $count_query=make_count_query($table_count+1); 328 329 sleep(5); # Give time to insert some rows 330 $i=0; 331 while (($i++ % 10) || !test_if_abort($dbh)) 332 { 333 sleep(1); 334 $row_counts=simple_query($dbh, $count_query); 335 336 for ($j=0 ; $j < $table_count ; $j++) 337 { 338 my ($id)= int rand $row_counts->[$j]; 339 my ($table)= $testtables[$j]->[0]; 340 $dbh->do("delete from $table where id >= $id-2 and id <= $id +2") || die "Got error on delete from $table: $DBI::errstr\n"; 341 $count++; 342 } 343 } 344 $dbh->disconnect; $dbh=0; 345 print "Test_delete: Executed $count deletes\n"; 346 exit(0); 347} 348 349# 350# Update the flag for table 2 and 3 351# Will abort after a while when table1 doesn't change max value 352# 353 354sub test_update 355{ 356 my ($dbh, $i, $j, $row_counts, $count_query, $count, $loop); 357 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 358 $opt_user, $opt_password, 359 { PrintError => 0}) || die $DBI::errstr; 360 361 $count_query=make_count_query(3); 362 $loop=9999; 363 $count=0; 364 365 sleep(5); # Give time to insert some rows 366 $i=0; 367 while (($i++ % 100) || !test_if_abort($dbh)) 368 { 369 if ($loop++ >= 100) 370 { 371 $loop=0; 372 $row_counts=simple_query($dbh, $count_query); 373 } 374 375 for ($j=1 ; $j <= 2 ; $j++) 376 { 377 my ($id)= int rand $row_counts->[$j]; 378 my ($table)= $testtables[$j]->[0]; 379 # Fix to not change the same rows as the above delete 380 $id= ($id + $count) % $row_counts->[$j]; 381 382 $dbh->do("update $table set flag=flag+1 where id >= $id-2 and id <= $id +2") || die "Got error on update of $table: $DBI::errstr\n"; 383 $count++; 384 } 385 } 386 $dbh->disconnect; $dbh=0; 387 print "Test_update: Executed $count updates\n"; 388 exit(0); 389} 390 391 392# 393# Run a check on all tables except the last one 394# (The last one is not checked to put pressure on the key cache) 395# 396 397sub test_check 398{ 399 my ($dbh, $row, $i, $j, $type, $table); 400 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 401 $opt_user, $opt_password, 402 { PrintError => 0}) || die $DBI::errstr; 403 404 $type= "check"; 405 for ($i=$j=0 ; !test_if_abort($dbh) ; $i++) 406 { 407 sleep(1000); 408 $table=$testtables[$j]->[0]; 409 $sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n"; 410 $sth->execute || die $DBI::errstr; 411 412 while (($row=$sth->fetchrow_arrayref)) 413 { 414 if ($row->[3] ne "OK") 415 { 416 print "Got error " . $row->[3] . " when doing $type on $table\n"; 417 exit(1); 418 } 419 } 420 if (++$j == $numtables-1) 421 { 422 $j=0; 423 } 424 } 425 $dbh->disconnect; $dbh=0; 426 print "test_check: Executed $i checks\n"; 427 exit(0); 428} 429 430# 431# Do a repair on the first table once in a while 432# 433 434sub test_repair 435{ 436 my ($dbh, $row, $i, $type, $table); 437 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 438 $opt_user, $opt_password, 439 { PrintError => 0}) || die $DBI::errstr; 440 441 $type= "repair"; 442 for ($i=0 ; !test_if_abort($dbh) ; $i++) 443 { 444 sleep(2000); 445 $table=$testtables[0]->[0]; 446 $sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n"; 447 $sth->execute || die $DBI::errstr; 448 449 while (($row=$sth->fetchrow_arrayref)) 450 { 451 if ($row->[3] ne "OK") 452 { 453 print "Got error " . $row->[3] . " when doing $type on $table\n"; 454 exit(1); 455 } 456 } 457 } 458 $dbh->disconnect; $dbh=0; 459 print "test_repair: Executed $i repairs\n"; 460 exit(0); 461} 462 463# 464# Do a flush tables on table 3 and 4 once in a while 465# 466 467sub test_flush 468{ 469 my ($dbh,$count,$tables); 470 471 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 472 $opt_user, $opt_password, 473 { PrintError => 0}) || die $DBI::errstr; 474 475 $tables=$testtables[2]->[0] . "," . $testtables[3]->[0]; 476 477 $count=0; 478 while (!test_if_abort($dbh)) 479 { 480 sleep(3000); 481 $dbh->do("flush tables $tables") || 482 die "Got error on flush $DBI::errstr\n"; 483 $count++; 484 } 485 $dbh->disconnect; $dbh=0; 486 print "flush: Executed $count flushs\n"; 487 exit(0); 488} 489 490 491# 492# Test all tables in a database 493# 494 495sub test_database 496{ 497 my ($database) = @_; 498 my ($dbh, $row, $i, $type, $tables); 499 $dbh = DBI->connect("DBI:mysql:$database:$opt_host", 500 $opt_user, $opt_password, 501 { PrintError => 0}) || die $DBI::errstr; 502 503 $tables= join(',',$dbh->func('_ListTables')); 504 $type= "check"; 505 for ($i=0 ; !test_if_abort($dbh) ; $i++) 506 { 507 sleep(120); 508 $sth=$dbh->prepare("$type table $tables") || die "Got error on prepare: $DBI::errstr\n"; 509 $sth->execute || die $DBI::errstr; 510 511 while (($row=$sth->fetchrow_arrayref)) 512 { 513 if ($row->[3] ne "OK") 514 { 515 print "Got error " . $row->[2] . " " . $row->[3] . " when doing $type on " . $row->[0] . "\n"; 516 exit(1); 517 } 518 } 519 } 520 $dbh->disconnect; $dbh=0; 521 print "test_check: Executed $i checks\n"; 522 exit(0); 523} 524 525# 526# Test ALTER TABLE on the second table 527# 528 529sub test_alter 530{ 531 my ($dbh, $row, $i, $type, $table); 532 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 533 $opt_user, $opt_password, 534 { PrintError => 0}) || die $DBI::errstr; 535 536 for ($i=0 ; !test_if_abort($dbh) ; $i++) 537 { 538 sleep(100); 539 $table=$testtables[1]->[0]; 540 $sth=$dbh->prepare("ALTER table $table modify info char(32)") || die "Got error on prepare: $DBI::errstr\n"; 541 $sth->execute || die $DBI::errstr; 542 } 543 $dbh->disconnect; $dbh=0; 544 print "test_alter: Executed $i ALTER TABLE\n"; 545 exit(0); 546} 547 548 549# 550# Help functions 551# 552 553sub signal_abort 554{ 555 my ($dbh); 556 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 557 $opt_user, $opt_password, 558 { PrintError => 0}) || die $DBI::errstr; 559 560 $dbh->do("insert into $abort_table values(1)") || die $DBI::errstr; 561 $dbh->disconnect; $dbh=0; 562 exit(0); 563} 564 565 566sub test_if_abort() 567{ 568 my ($dbh)=@_; 569 $row=simple_query($dbh,"select * from $opt_db.$abort_table"); 570 return (defined($row) && defined($row->[0]) != 0) ? 1 : 0; 571} 572 573 574sub make_count_query 575{ 576 my ($table_count)= @_; 577 my ($tables, $count_query, $i, $tables_def); 578 $tables=""; 579 $count_query="select high_priority "; 580 $table_count--; 581 for ($i=0 ; $i < $table_count ; $i++) 582 { 583 my ($table_def)= $testtables[$i]; 584 $tables.=$table_def->[0] . ","; 585 $count_query.= "max(" . $table_def->[0] . ".id),"; 586 } 587 $table_def=$testtables[$table_count]; 588 $tables.=$table_def->[0]; 589 $count_query.= "max(" . $table_def->[0] . ".id) from $tables"; 590 return $count_query; 591} 592 593sub simple_query() 594{ 595 my ($dbh, $query)= @_; 596 my ($sth,$row); 597 598 $sth=$dbh->prepare($query) || die "Got error on '$query': " . $dbh->errstr . "\n"; 599 $sth->execute || die "Got error on '$query': " . $dbh->errstr . "\n"; 600 $row= $sth->fetchrow_arrayref(); 601 $sth=0; 602 return $row; 603} 604