1#!/usr/bin/perl -w 2 3# Copyright (c) 2002, 2003, 2005, 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_drop=$opt_fast=$opt_force=0; 43$opt_thread_factor=1; 44$opt_insert=1; 45$opt_select=6;$opt_join=4; 46$opt_select_count=$opt_join_count=0; 47$opt_update=1;$opt_delete=0; 48$opt_flush=$opt_check=$opt_repair=$opt_alter=0; 49$opt_join_range=100; 50$opt_resize_interval=0; 51$opt_time=0; 52$opt_host=$opt_user=$opt_password=""; $opt_db="test"; 53$opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$opt_force=undef; # Ignore warnings from these 54 55GetOptions("host=s","db=s","user=s","password=s","loop-count=i","skip-create","skip-in","skip-drop", 56 "verbose","fast-insert","lock-tables","debug","fast","force","thread-factor=i", 57 "insert=i", "select=i", "join=i", "select-count=i", "join-count=i", "update=i", "delete=i", 58 "flush=i", "check=i", "repair=i", "alter=i", "resize-interval=i", "max-join_range=i", "time=i") || die "Aborted"; 59 60print "Test of multiple connections that test the following things:\n"; 61print "insert, select, delete, update, alter, check, repair and flush\n"; 62 63@testtables = ( ["bench_f31", ""], 64 ["bench_f32", "row_format=fixed"], 65 ["bench_f33", "delay_key_write=1"], 66 ["bench_f34", "checksum=1"], 67 ["bench_f35", "delay_key_write=1"]); 68$abort_table="bench_f39"; 69 70$numtables = $#testtables+1; 71srand 100; # Make random numbers repeatable 72 73#### 74#### Start timeing and start test 75#### 76 77$opt_insert*=$opt_thread_factor; 78$opt_select*=$opt_thread_factor; 79$opt_join*=$opt_thread_factor; 80$opt_select_count*=$opt_thread_factor; 81$opt_join_count*=$opt_thread_factor; 82$opt_update*=$opt_thread_factor; 83$opt_delete*=$opt_thread_factor; 84 85if ($opt_time == 0 && $opt_insert == 0) 86{ 87 $opt_insert=1; 88} 89 90$start_time=new Benchmark; 91$dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 92 $opt_user, $opt_password, 93 { PrintError => 0}) || die $DBI::errstr; 94if (!$opt_skip_create) 95{ 96 my $table_def; 97 foreach $table_def (@testtables) 98 { 99 my ($table,$extra)= ($table_def->[0], $table_def->[1]); 100 print "Creating table $table in database $opt_db\n"; 101 $dbh->do("drop table if exists $table"); 102 $dbh->do("create table $table". 103 " (id int(6) not null auto_increment,". 104 " info varchar(32)," . 105 " marker timestamp," . 106 " flag int not null," . 107 " primary key(id)) $extra") 108 109 or die $DBI::errstr; 110 # One row in the table will make future tests easier 111 $dbh->do("insert into $table (id) values (null)") 112 or die $DBI::errstr; 113 } 114 # Create the table we use to signal that we should end the test 115 $dbh->do("drop table if exists $abort_table"); 116 $dbh->do("create table $abort_table (id int(6) not null) ENGINE=heap") || 117 die $DBI::errstr; 118} 119 120$dbh->do("delete from $abort_table"); 121$dbh->disconnect; $dbh=0; # Close handler 122$|= 1; # Autoflush 123 124#### 125#### Start the tests 126#### 127if ($opt_time != 0) 128{ 129 test_abort() if (($pid=fork()) == 0); $work{$pid}="abort"; 130} 131for ($i=0 ; $i < $opt_insert ; $i ++) 132{ 133 test_insert() if (($pid=fork()) == 0); $work{$pid}="insert"; 134} 135$threads=$i; 136for ($i=0 ; $i < $opt_select ; $i ++) 137{ 138 test_select() if (($pid=fork()) == 0); $work{$pid}="select"; 139} 140$threads+=$i; 141for ($i=0 ; $i < $opt_join ; $i ++) 142{ 143 test_join() if (($pid=fork()) == 0); $work{$pid}="join"; 144} 145$threads+=$i; 146for ($i=0 ; $i < $opt_select_count ; $i ++) 147{ 148 test_select_count() if (($pid=fork()) == 0); $work{$pid}="select_count"; 149} 150$threads+=$i; 151for ($i=0 ; $i < $opt_join_count ; $i ++) 152{ 153 test_join_count() if (($pid=fork()) == 0); $work{$pid}="join_count"; 154} 155$threads+=$i; 156for ($i=0 ; $i < $opt_update ; $i ++) 157{ 158 test_update() if (($pid=fork()) == 0); $work{$pid}="update"; 159} 160$threads+=$i; 161for ($i=0 ; $i < $opt_delete ; $i ++) 162{ 163 test_delete() if (($pid=fork()) == 0); $work{$pid}="delete"; 164} 165$threads+=$i; 166for ($i=0 ; $i < $opt_flush ; $i ++) 167{ 168 test_flush() if (($pid=fork()) == 0); $work{$pid}="flush"; 169} 170$threads+=$i; 171for ($i=0 ; $i < $opt_check ; $i ++) 172{ 173 test_check() if (($pid=fork()) == 0); $work{$pid}="check"; 174} 175$threads+=$i; 176for ($i=0 ; $i < $opt_repair ; $i ++) 177{ 178 test_repair() if (($pid=fork()) == 0); $work{$pid}="repair"; 179} 180$threads+=$i; 181for ($i=0 ; $i < $opt_alter ; $i ++) 182{ 183 test_alter() if (($pid=fork()) == 0); $work{$pid}="alter"; 184} 185$threads+=$i; 186if ($opt_resize_interval != 0) 187{ 188 test_resize() if (($pid=fork()) == 0); $work{$pid}="resize"; 189 $threads+=1; 190} 191 192print "Started $threads threads\n"; 193 194$errors=0; 195$running_insert_threads=$opt_insert; 196while (($pid=wait()) != -1) 197{ 198 $ret=$?/256; 199 print "thread '" . $work{$pid} . "' finished with exit code $ret\n"; 200 if ($opt_time == 0) 201 { 202 if ($work{$pid} =~ /^insert/) 203 { 204 if (!--$running_insert_threads) 205 { 206 207 # Time to stop other threads 208 signal_abort(); 209 } 210 } 211 } 212 $errors++ if ($ret != 0); 213} 214 215# 216# Cleanup 217# 218 219if (!$opt_skip_drop && !$errors) 220{ 221 my $table_def; 222 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 223 $opt_user, $opt_password, 224 { PrintError => 0}) || die $DBI::errstr; 225 226 $dbh->do("drop table $abort_table"); 227 foreach $table_def (@testtables) 228 { 229 $dbh->do("drop table " . $table_def->[0]); 230 } 231 $dbh->disconnect; $dbh=0; # Close handler 232} 233 234print ($errors ? "Test failed\n" :"Test ok\n"); 235$end_time=new Benchmark; 236print "Total time: " . 237 timestr(timediff($end_time, $start_time),"noc") . "\n"; 238 239exit(0); 240 241# 242# Sleep and then abort other threads 243# 244 245sub test_abort 246{ 247 sleep($opt_time); 248 signal_abort(); 249 exit(0); 250} 251 252 253# 254# Insert records in the table 255# 256 257sub test_insert 258{ 259 my ($from_table,$to_table)= @_; 260 my ($dbh,$i,$j,$count,$table_def,$table); 261 262 if (!defined($from_table)) 263 { 264 $from_table=0; $to_table=$numtables-1; 265 } 266 267 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 268 $opt_user, $opt_password, 269 { PrintError => 0}) || die $DBI::errstr; 270 271 for ($i=$count=0 ; $i < $opt_loop_count; $i++) 272 { 273 for ($j= $from_table ; $j <= $to_table ; $j++) 274 { 275 my ($table)= ($testtables[$j]->[0]); 276 $dbh->do("insert into $table values (NULL,'This is entry $i','',0)") || die "Got error on insert: $DBI::errstr\n"; 277 $count++; 278 } 279 } 280 $dbh->disconnect; $dbh=0; 281 print "Test_insert: Inserted $count rows\n"; 282 exit(0); 283} 284 285 286# 287# select records 288# Do continously select over all tables as long as there is changed 289# rows in the table 290# 291 292sub test_select 293{ 294 my ($dbh, $i, $j, $count, $loop); 295 296 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 297 $opt_user, $opt_password, 298 { PrintError => 0}) || die $DBI::errstr; 299 300 $count_query=make_count_query($numtables); 301 $count=0; 302 $loop=9999; 303 304 $i=0; 305 while (($i++ % 100) || !test_if_abort($dbh)) 306 { 307 if ($loop++ >= 100) 308 { 309 $loop=0; 310 $row_counts=simple_query($dbh, $count_query); 311 } 312 for ($j=0 ; $j < $numtables ; $j++) 313 { 314 my ($id)= int rand $row_counts->[$j]; 315 my ($table)= $testtables[$j]->[0]; 316 simple_query($dbh, "select id,info from $table where id=$id"); 317 $count++; 318 } 319 } 320 $dbh->disconnect; $dbh=0; 321 print "Test_select: Executed $count selects\n"; 322 exit(0); 323} 324 325# 326# Do big select count(distinct..) over the table 327# 328 329sub test_select_count 330{ 331 my ($dbh, $i, $j, $count, $loop); 332 333 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 334 $opt_user, $opt_password, 335 { PrintError => 0}) || die $DBI::errstr; 336 337 $count=0; 338 $i=0; 339 while (!test_if_abort($dbh)) 340 { 341 for ($j=0 ; $j < $numtables ; $j++) 342 { 343 my ($table)= $testtables[$j]->[0]; 344 simple_query($dbh, "select count(distinct marker),count(distinct id),count(distinct info) from $table"); 345 $count++; 346 } 347 sleep(20); # This query is quite slow 348 } 349 $dbh->disconnect; $dbh=0; 350 print "Test_select: Executed $count select count(distinct) queries\n"; 351 exit(0); 352} 353 354# 355# select records 356# Do continously joins between the first and second table 357# 358 359sub test_join 360{ 361 my ($dbh, $i, $j, $count, $loop); 362 363 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 364 $opt_user, $opt_password, 365 { PrintError => 0}) || die $DBI::errstr; 366 367 $count_query=make_count_query($numtables); 368 $count=0; 369 $loop=9999; 370 371 $i=0; 372 while (($i++ % 100) || !test_if_abort($dbh)) 373 { 374 if ($loop++ >= 100) 375 { 376 $loop=0; 377 $row_counts=simple_query($dbh, $count_query); 378 } 379 for ($j=0 ; $j < $numtables-1 ; $j++) 380 { 381 my ($id)= int rand $row_counts->[$j]; 382 my ($t1,$t2)= ($testtables[$j]->[0],$testtables[$j+1]->[0]); 383 simple_query($dbh, "select $t1.id,$t2.info from $t1, $t2 where $t1.id=$t2.id and $t1.id=$id"); 384 $count++; 385 } 386 } 387 $dbh->disconnect; $dbh=0; 388 print "Test_join: Executed $count joins\n"; 389 exit(0); 390} 391 392# 393# select records 394# Do continously joins between the first and second for range and count selected rows 395# 396 397sub test_join_count 398{ 399 my ($dbh, $i, $j, $count, $loop); 400 401 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 402 $opt_user, $opt_password, 403 { PrintError => 0}) || die $DBI::errstr; 404 405 $count_query=make_count_query($numtables); 406 $count=0; 407 $loop=9999; 408 $sum=0; 409 410 srand(); 411 412 $i=0; 413 while (($i++ % 10) || !test_if_abort($dbh)) 414 { 415 if ($loop++ >= 10) 416 { 417 $loop=0; 418 $row_counts=simple_query($dbh, $count_query); 419 } 420 for ($j=0 ; $j < $numtables-1 ; $j++) 421 { 422 my ($id1)= int rand $row_counts->[$j]; 423 my ($id2)= int rand $row_counts->[$j]; 424 if ($id1 > $id2) 425 { 426 my $id0=$id1; $id1=$id2; $id2=$id0; 427 if ($id2-$id1 > $opt_join_range) 428 { 429 $id2=$id1+$opt_join_range; 430 } 431 } 432 my ($t1,$t2)= ($testtables[$j]->[0],$testtables[$j+1]->[0]); 433 $row=simple_query($dbh, "select count(*) from $t1, $t2 where $t1.id=$t2.id and $t1.id between $id1 and $id2"); 434 $sum+=$row->[0]; 435 $count++; 436 } 437 } 438 $dbh->disconnect; $dbh=0; 439 print "Test_join_count: Executed $count joins: total $sum rows\n"; 440 exit(0); 441} 442 443 444# 445# Delete 1-5 rows from the first 2 tables. 446# Test ends when the number of rows for table 3 didn't change during 447# one loop 448# 449 450sub test_delete 451{ 452 my ($dbh, $i,$j, $row_counts, $count_query, $table_count, $count); 453 454 $table_count=2; 455 $count=0; 456 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 457 $opt_user, $opt_password, 458 { PrintError => 0}) || die $DBI::errstr; 459 460 $count_query=make_count_query($table_count+1); 461 462 sleep(5); # Give time to insert some rows 463 $i=0; 464 while (($i++ % 10) || !test_if_abort($dbh)) 465 { 466 sleep(1); 467 $row_counts=simple_query($dbh, $count_query); 468 469 for ($j=0 ; $j < $table_count ; $j++) 470 { 471 my ($id)= int rand $row_counts->[$j]; 472 my ($table)= $testtables[$j]->[0]; 473 $dbh->do("delete from $table where id >= $id-2 and id <= $id +2") || die "Got error on delete from $table: $DBI::errstr\n"; 474 $count++; 475 } 476 } 477 $dbh->disconnect; $dbh=0; 478 print "Test_delete: Executed $count deletes\n"; 479 exit(0); 480} 481 482# 483# Update the flag for table 2 and 3 484# Will abort after a while when table1 doesn't change max value 485# 486 487sub test_update 488{ 489 my ($dbh, $i, $j, $row_counts, $count_query, $count, $loop); 490 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 491 $opt_user, $opt_password, 492 { PrintError => 0}) || die $DBI::errstr; 493 494 $count_query=make_count_query(3); 495 $loop=9999; 496 $count=0; 497 498 sleep(5); # Give time to insert some rows 499 $i=0; 500 while (($i++ % 100) || !test_if_abort($dbh)) 501 { 502 if ($loop++ >= 100) 503 { 504 $loop=0; 505 $row_counts=simple_query($dbh, $count_query); 506 } 507 508 for ($j=1 ; $j <= 2 ; $j++) 509 { 510 my ($id)= int rand $row_counts->[$j]; 511 my ($table)= $testtables[$j]->[0]; 512 # Fix to not change the same rows as the above delete 513 $id= ($id + $count) % $row_counts->[$j]; 514 515 $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"; 516 $count++; 517 } 518 } 519 $dbh->disconnect; $dbh=0; 520 print "Test_update: Executed $count updates\n"; 521 exit(0); 522} 523 524 525# 526# Run a check on all tables except the last one 527# (The last one is not checked to put pressure on the key cache) 528# 529 530sub test_check 531{ 532 my ($dbh, $row, $i, $j, $type, $table); 533 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 534 $opt_user, $opt_password, 535 { PrintError => 0}) || die $DBI::errstr; 536 537 $type= "check"; 538 for ($i=$j=0 ; !test_if_abort($dbh) ; $i++) 539 { 540 sleep(1000); 541 $table=$testtables[$j]->[0]; 542 $sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n"; 543 $sth->execute || die $DBI::errstr; 544 545 while (($row=$sth->fetchrow_arrayref)) 546 { 547 if ($row->[3] ne "OK") 548 { 549 print "Got error " . $row->[3] . " when doing $type on $table\n"; 550 exit(1); 551 } 552 } 553 if (++$j == $numtables-1) 554 { 555 $j=0; 556 } 557 } 558 $dbh->disconnect; $dbh=0; 559 print "test_check: Executed $i checks\n"; 560 exit(0); 561} 562 563# 564# Do a repair on the first table once in a while 565# 566 567sub test_repair 568{ 569 my ($dbh, $row, $i, $type, $table); 570 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 571 $opt_user, $opt_password, 572 { PrintError => 0}) || die $DBI::errstr; 573 574 $type= "repair"; 575 for ($i=0 ; !test_if_abort($dbh) ; $i++) 576 { 577 sleep(2000); 578 $table=$testtables[0]->[0]; 579 $sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n"; 580 $sth->execute || die $DBI::errstr; 581 582 while (($row=$sth->fetchrow_arrayref)) 583 { 584 if ($row->[3] ne "OK") 585 { 586 print "Got error " . $row->[3] . " when doing $type on $table\n"; 587 exit(1); 588 } 589 } 590 } 591 $dbh->disconnect; $dbh=0; 592 print "test_repair: Executed $i repairs\n"; 593 exit(0); 594} 595 596# 597# Do a flush tables on table 3 and 4 once in a while 598# 599 600sub test_flush 601{ 602 my ($dbh,$count,$tables); 603 604 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 605 $opt_user, $opt_password, 606 { PrintError => 0}) || die $DBI::errstr; 607 608 $tables=$testtables[2]->[0] . "," . $testtables[3]->[0]; 609 610 $count=0; 611 while (!test_if_abort($dbh)) 612 { 613 sleep(3000); 614 $dbh->do("flush tables $tables") || 615 die "Got error on flush $DBI::errstr\n"; 616 $count++; 617 } 618 $dbh->disconnect; $dbh=0; 619 print "flush: Executed $count flushs\n"; 620 exit(0); 621} 622 623# 624# Do a resize key cache every periodically 625# 626 627sub test_resize 628{ 629 my ($dbh, $key_buffer_size); 630 631 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 632 $opt_user, $opt_password, 633 { PrintError => 0}) || die $DBI::errstr; 634 635 $count=0; 636 $key_buffer_size=1024*64; 637 while (!test_if_abort($dbh)) 638 { 639 sleep($opt_resize_interval); 640 $dbh->do("set global key_buffer_size=$key_buffer_size") || 641 die "Got error on resize key cache $DBI::errstr\n"; 642 $key_buffer_size+=1024*16; 643 $count++; 644 } 645 $dbh->disconnect; $dbh=0; 646 print "Test_resize: Executed $count times resize key cache\n"; 647 exit(0); 648} 649 650# 651# Test all tables in a database 652# 653 654sub test_database 655{ 656 my ($database) = @_; 657 my ($dbh, $row, $i, $type, $tables); 658 $dbh = DBI->connect("DBI:mysql:$database:$opt_host", 659 $opt_user, $opt_password, 660 { PrintError => 0}) || die $DBI::errstr; 661 662 $tables= join(',',$dbh->func('_ListTables')); 663 $type= "check"; 664 for ($i=0 ; !test_if_abort($dbh) ; $i++) 665 { 666 sleep(120); 667 $sth=$dbh->prepare("$type table $tables") || die "Got error on prepare: $DBI::errstr\n"; 668 $sth->execute || die $DBI::errstr; 669 670 while (($row=$sth->fetchrow_arrayref)) 671 { 672 if ($row->[3] ne "OK") 673 { 674 print "Got error " . $row->[2] . " " . $row->[3] . " when doing $type on " . $row->[0] . "\n"; 675 exit(1); 676 } 677 } 678 } 679 $dbh->disconnect; $dbh=0; 680 print "test_check: Executed $i checks\n"; 681 exit(0); 682} 683 684# 685# Test ALTER TABLE on the second table 686# 687 688sub test_alter 689{ 690 my ($dbh, $row, $i, $type, $table); 691 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 692 $opt_user, $opt_password, 693 { PrintError => 0}) || die $DBI::errstr; 694 695 for ($i=0 ; !test_if_abort($dbh) ; $i++) 696 { 697 sleep(100); 698 $table=$testtables[1]->[0]; 699 $sth=$dbh->prepare("ALTER table $table modify info char(32)") || die "Got error on prepare: $DBI::errstr\n"; 700 $sth->execute || die $DBI::errstr; 701 } 702 $dbh->disconnect; $dbh=0; 703 print "test_alter: Executed $i ALTER TABLE\n"; 704 exit(0); 705} 706 707 708# 709# Help functions 710# 711 712sub signal_abort 713{ 714 my ($dbh); 715 $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", 716 $opt_user, $opt_password, 717 { PrintError => 0}) || die $DBI::errstr; 718 719 $dbh->do("insert into $abort_table values(1)") || die $DBI::errstr; 720 $dbh->disconnect; $dbh=0; 721} 722 723 724sub test_if_abort() 725{ 726 my ($dbh)=@_; 727 $row=simple_query($dbh,"select * from $opt_db.$abort_table"); 728 return (defined($row) && defined($row->[0]) != 0) ? 1 : 0; 729} 730 731 732sub make_count_query 733{ 734 my ($table_count)= @_; 735 my ($tables, $count_query, $i, $tables_def); 736 $tables=""; 737 $count_query="select high_priority "; 738 $table_count--; 739 for ($i=0 ; $i < $table_count ; $i++) 740 { 741 my ($table_def)= $testtables[$i]; 742 $tables.=$table_def->[0] . ","; 743 $count_query.= "max(" . $table_def->[0] . ".id),"; 744 } 745 $table_def=$testtables[$table_count]; 746 $tables.=$table_def->[0]; 747 $count_query.= "max(" . $table_def->[0] . ".id) from $tables"; 748 return $count_query; 749} 750 751sub simple_query() 752{ 753 my ($dbh, $query)= @_; 754 my ($sth,$row); 755 756 $sth=$dbh->prepare($query) || die "Got error on '$query': " . $dbh->errstr . "\n"; 757 $sth->execute || die "Got error on '$query': " . $dbh->errstr . "\n"; 758 $row= $sth->fetchrow_arrayref(); 759 $sth=0; 760 return $row; 761} 762