#!/usr/bin/perl -w # # This is a test with uses many processes to test a MySQL server. # # Tested a lot with: --threads=30 $opt_loop_count=500000; # Change this to make test harder/easier ##################### Standard benchmark inits ############################## use DBI; use Getopt::Long; use Benchmark; package main; $opt_skip_create=$opt_skip_in=$opt_verbose=$opt_fast_insert= $opt_lock_tables=$opt_debug=$opt_skip_drop=$opt_fast=$opt_force=0; $opt_thread_factor=1; $opt_insert=1; $opt_select=6;$opt_join=4; $opt_select_count=$opt_join_count=0; $opt_update=1;$opt_delete=0; $opt_flush=$opt_check=$opt_repair=$opt_alter=0; $opt_join_range=100; $opt_resize_interval=0; $opt_time=0; $opt_host=$opt_user=$opt_password=""; $opt_db="test"; $opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$opt_force=undef; # Ignore warnings from these GetOptions("host=s","db=s","user=s","password=s","loop-count=i","skip-create","skip-in","skip-drop", "verbose","fast-insert","lock-tables","debug","fast","force","thread-factor=i", "insert=i", "select=i", "join=i", "select-count=i", "join-count=i", "update=i", "delete=i", "flush=i", "check=i", "repair=i", "alter=i", "resize-interval=i", "max-join_range=i", "time=i") || die "Aborted"; print "Test of multiple connections that test the following things:\n"; print "insert, select, delete, update, alter, check, repair and flush\n"; @testtables = ( ["bench_f31", ""], ["bench_f32", "row_format=fixed"], ["bench_f33", "delay_key_write=1"], ["bench_f34", "checksum=1"], ["bench_f35", "delay_key_write=1"]); $abort_table="bench_f39"; $numtables = $#testtables+1; srand 100; # Make random numbers repeatable #### #### Start timeing and start test #### $opt_insert*=$opt_thread_factor; $opt_select*=$opt_thread_factor; $opt_join*=$opt_thread_factor; $opt_select_count*=$opt_thread_factor; $opt_join_count*=$opt_thread_factor; $opt_update*=$opt_thread_factor; $opt_delete*=$opt_thread_factor; if ($opt_time == 0 && $opt_insert == 0) { $opt_insert=1; } $start_time=new Benchmark; $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; if (!$opt_skip_create) { my $table_def; foreach $table_def (@testtables) { my ($table,$extra)= ($table_def->[0], $table_def->[1]); print "Creating table $table in database $opt_db\n"; $dbh->do("drop table if exists $table"); $dbh->do("create table $table". " (id int(6) not null auto_increment,". " info varchar(32)," . " marker timestamp," . " flag int not null," . " primary key(id)) $extra") or die $DBI::errstr; # One row in the table will make future tests easier $dbh->do("insert into $table (id) values (null)") or die $DBI::errstr; } # Create the table we use to signal that we should end the test $dbh->do("drop table if exists $abort_table"); $dbh->do("create table $abort_table (id int(6) not null) type=heap") || die $DBI::errstr; } $dbh->do("delete from $abort_table"); $dbh->disconnect; $dbh=0; # Close handler $|= 1; # Autoflush #### #### Start the tests #### if ($opt_time != 0) { test_abort() if (($pid=fork()) == 0); $work{$pid}="abort"; } for ($i=0 ; $i < $opt_insert ; $i ++) { test_insert() if (($pid=fork()) == 0); $work{$pid}="insert"; } $threads=$i; for ($i=0 ; $i < $opt_select ; $i ++) { test_select() if (($pid=fork()) == 0); $work{$pid}="select"; } $threads+=$i; for ($i=0 ; $i < $opt_join ; $i ++) { test_join() if (($pid=fork()) == 0); $work{$pid}="join"; } $threads+=$i; for ($i=0 ; $i < $opt_select_count ; $i ++) { test_select_count() if (($pid=fork()) == 0); $work{$pid}="select_count"; } $threads+=$i; for ($i=0 ; $i < $opt_join_count ; $i ++) { test_join_count() if (($pid=fork()) == 0); $work{$pid}="join_count"; } $threads+=$i; for ($i=0 ; $i < $opt_update ; $i ++) { test_update() if (($pid=fork()) == 0); $work{$pid}="update"; } $threads+=$i; for ($i=0 ; $i < $opt_delete ; $i ++) { test_delete() if (($pid=fork()) == 0); $work{$pid}="delete"; } $threads+=$i; for ($i=0 ; $i < $opt_flush ; $i ++) { test_flush() if (($pid=fork()) == 0); $work{$pid}="flush"; } $threads+=$i; for ($i=0 ; $i < $opt_check ; $i ++) { test_check() if (($pid=fork()) == 0); $work{$pid}="check"; } $threads+=$i; for ($i=0 ; $i < $opt_repair ; $i ++) { test_repair() if (($pid=fork()) == 0); $work{$pid}="repair"; } $threads+=$i; for ($i=0 ; $i < $opt_alter ; $i ++) { test_alter() if (($pid=fork()) == 0); $work{$pid}="alter"; } $threads+=$i; if ($opt_resize_interval != 0) { test_resize() if (($pid=fork()) == 0); $work{$pid}="resize"; $threads+=1; } print "Started $threads threads\n"; $errors=0; $running_insert_threads=$opt_insert; while (($pid=wait()) != -1) { $ret=$?/256; print "thread '" . $work{$pid} . "' finished with exit code $ret\n"; if ($opt_time == 0) { if ($work{$pid} =~ /^insert/) { if (!--$running_insert_threads) { # Time to stop other threads signal_abort(); } } } $errors++ if ($ret != 0); } # # Cleanup # if (!$opt_skip_drop && !$errors) { my $table_def; $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $dbh->do("drop table $abort_table"); foreach $table_def (@testtables) { $dbh->do("drop table " . $table_def->[0]); } $dbh->disconnect; $dbh=0; # Close handler } print ($errors ? "Test failed\n" :"Test ok\n"); $end_time=new Benchmark; print "Total time: " . timestr(timediff($end_time, $start_time),"noc") . "\n"; exit(0); # # Sleep and then abort other threads # sub test_abort { sleep($opt_time); signal_abort(); exit(0); } # # Insert records in the table # sub test_insert { my ($from_table,$to_table)= @_; my ($dbh,$i,$j,$count,$table_def,$table); if (!defined($from_table)) { $from_table=0; $to_table=$numtables-1; } $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; for ($i=$count=0 ; $i < $opt_loop_count; $i++) { for ($j= $from_table ; $j <= $to_table ; $j++) { my ($table)= ($testtables[$j]->[0]); $dbh->do("insert into $table values (NULL,'This is entry $i','',0)") || die "Got error on insert: $DBI::errstr\n"; $count++; } } $dbh->disconnect; $dbh=0; print "Test_insert: Inserted $count rows\n"; exit(0); } # # select records # Do continously select over all tables as long as there is changed # rows in the table # sub test_select { my ($dbh, $i, $j, $count, $loop); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $count_query=make_count_query($numtables); $count=0; $loop=9999; $i=0; while (($i++ % 100) || !test_if_abort($dbh)) { if ($loop++ >= 100) { $loop=0; $row_counts=simple_query($dbh, $count_query); } for ($j=0 ; $j < $numtables ; $j++) { my ($id)= int rand $row_counts->[$j]; my ($table)= $testtables[$j]->[0]; simple_query($dbh, "select id,info from $table where id=$id"); $count++; } } $dbh->disconnect; $dbh=0; print "Test_select: Executed $count selects\n"; exit(0); } # # Do big select count(distinct..) over the table # sub test_select_count { my ($dbh, $i, $j, $count, $loop); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $count=0; $i=0; while (!test_if_abort($dbh)) { for ($j=0 ; $j < $numtables ; $j++) { my ($table)= $testtables[$j]->[0]; simple_query($dbh, "select count(distinct marker),count(distinct id),count(distinct info) from $table"); $count++; } sleep(20); # This query is quite slow } $dbh->disconnect; $dbh=0; print "Test_select: Executed $count select count(distinct) queries\n"; exit(0); } # # select records # Do continously joins between the first and second table # sub test_join { my ($dbh, $i, $j, $count, $loop); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $count_query=make_count_query($numtables); $count=0; $loop=9999; $i=0; while (($i++ % 100) || !test_if_abort($dbh)) { if ($loop++ >= 100) { $loop=0; $row_counts=simple_query($dbh, $count_query); } for ($j=0 ; $j < $numtables-1 ; $j++) { my ($id)= int rand $row_counts->[$j]; my ($t1,$t2)= ($testtables[$j]->[0],$testtables[$j+1]->[0]); simple_query($dbh, "select $t1.id,$t2.info from $t1, $t2 where $t1.id=$t2.id and $t1.id=$id"); $count++; } } $dbh->disconnect; $dbh=0; print "Test_join: Executed $count joins\n"; exit(0); } # # select records # Do continously joins between the first and second for range and count selected rows # sub test_join_count { my ($dbh, $i, $j, $count, $loop); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $count_query=make_count_query($numtables); $count=0; $loop=9999; $sum=0; srand(); $i=0; while (($i++ % 10) || !test_if_abort($dbh)) { if ($loop++ >= 10) { $loop=0; $row_counts=simple_query($dbh, $count_query); } for ($j=0 ; $j < $numtables-1 ; $j++) { my ($id1)= int rand $row_counts->[$j]; my ($id2)= int rand $row_counts->[$j]; if ($id1 > $id2) { my $id0=$id1; $id1=$id2; $id2=$id0; if ($id2-$id1 > $opt_join_range) { $id2=$id1+$opt_join_range; } } my ($t1,$t2)= ($testtables[$j]->[0],$testtables[$j+1]->[0]); $row=simple_query($dbh, "select count(*) from $t1, $t2 where $t1.id=$t2.id and $t1.id between $id1 and $id2"); $sum+=$row->[0]; $count++; } } $dbh->disconnect; $dbh=0; print "Test_join_count: Executed $count joins: total $sum rows\n"; exit(0); } # # Delete 1-5 rows from the first 2 tables. # Test ends when the number of rows for table 3 didn't change during # one loop # sub test_delete { my ($dbh, $i,$j, $row_counts, $count_query, $table_count, $count); $table_count=2; $count=0; $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $count_query=make_count_query($table_count+1); sleep(5); # Give time to insert some rows $i=0; while (($i++ % 10) || !test_if_abort($dbh)) { sleep(1); $row_counts=simple_query($dbh, $count_query); for ($j=0 ; $j < $table_count ; $j++) { my ($id)= int rand $row_counts->[$j]; my ($table)= $testtables[$j]->[0]; $dbh->do("delete from $table where id >= $id-2 and id <= $id +2") || die "Got error on delete from $table: $DBI::errstr\n"; $count++; } } $dbh->disconnect; $dbh=0; print "Test_delete: Executed $count deletes\n"; exit(0); } # # Update the flag for table 2 and 3 # Will abort after a while when table1 doesn't change max value # sub test_update { my ($dbh, $i, $j, $row_counts, $count_query, $count, $loop); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $count_query=make_count_query(3); $loop=9999; $count=0; sleep(5); # Give time to insert some rows $i=0; while (($i++ % 100) || !test_if_abort($dbh)) { if ($loop++ >= 100) { $loop=0; $row_counts=simple_query($dbh, $count_query); } for ($j=1 ; $j <= 2 ; $j++) { my ($id)= int rand $row_counts->[$j]; my ($table)= $testtables[$j]->[0]; # Fix to not change the same rows as the above delete $id= ($id + $count) % $row_counts->[$j]; $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"; $count++; } } $dbh->disconnect; $dbh=0; print "Test_update: Executed $count updates\n"; exit(0); } # # Run a check on all tables except the last one # (The last one is not checked to put pressure on the key cache) # sub test_check { my ($dbh, $row, $i, $j, $type, $table); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $type= "check"; for ($i=$j=0 ; !test_if_abort($dbh) ; $i++) { sleep(1000); $table=$testtables[$j]->[0]; $sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n"; $sth->execute || die $DBI::errstr; while (($row=$sth->fetchrow_arrayref)) { if ($row->[3] ne "OK") { print "Got error " . $row->[3] . " when doing $type on $table\n"; exit(1); } } if (++$j == $numtables-1) { $j=0; } } $dbh->disconnect; $dbh=0; print "test_check: Executed $i checks\n"; exit(0); } # # Do a repair on the first table once in a while # sub test_repair { my ($dbh, $row, $i, $type, $table); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $type= "repair"; for ($i=0 ; !test_if_abort($dbh) ; $i++) { sleep(2000); $table=$testtables[0]->[0]; $sth=$dbh->prepare("$type table $table") || die "Got error on prepare: $DBI::errstr\n"; $sth->execute || die $DBI::errstr; while (($row=$sth->fetchrow_arrayref)) { if ($row->[3] ne "OK") { print "Got error " . $row->[3] . " when doing $type on $table\n"; exit(1); } } } $dbh->disconnect; $dbh=0; print "test_repair: Executed $i repairs\n"; exit(0); } # # Do a flush tables on table 3 and 4 once in a while # sub test_flush { my ($dbh,$count,$tables); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $tables=$testtables[2]->[0] . "," . $testtables[3]->[0]; $count=0; while (!test_if_abort($dbh)) { sleep(3000); $dbh->do("flush tables $tables") || die "Got error on flush $DBI::errstr\n"; $count++; } $dbh->disconnect; $dbh=0; print "flush: Executed $count flushs\n"; exit(0); } # # Do a resize key cache every periodically # sub test_resize { my ($dbh, $key_buffer_size); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $count=0; $key_buffer_size=1024*64; while (!test_if_abort($dbh)) { sleep($opt_resize_interval); $dbh->do("set global key_buffer_size=$key_buffer_size") || die "Got error on resize key cache $DBI::errstr\n"; $key_buffer_size+=1024*16; $count++; } $dbh->disconnect; $dbh=0; print "Test_resize: Executed $count times resize key cache\n"; exit(0); } # # Test all tables in a database # sub test_database { my ($database) = @_; my ($dbh, $row, $i, $type, $tables); $dbh = DBI->connect("DBI:mysql:$database:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $tables= join(',',$dbh->func('_ListTables')); $type= "check"; for ($i=0 ; !test_if_abort($dbh) ; $i++) { sleep(120); $sth=$dbh->prepare("$type table $tables") || die "Got error on prepare: $DBI::errstr\n"; $sth->execute || die $DBI::errstr; while (($row=$sth->fetchrow_arrayref)) { if ($row->[3] ne "OK") { print "Got error " . $row->[2] . " " . $row->[3] . " when doing $type on " . $row->[0] . "\n"; exit(1); } } } $dbh->disconnect; $dbh=0; print "test_check: Executed $i checks\n"; exit(0); } # # Test ALTER TABLE on the second table # sub test_alter { my ($dbh, $row, $i, $type, $table); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; for ($i=0 ; !test_if_abort($dbh) ; $i++) { sleep(100); $table=$testtables[1]->[0]; $sth=$dbh->prepare("ALTER table $table modify info char(32)") || die "Got error on prepare: $DBI::errstr\n"; $sth->execute || die $DBI::errstr; } $dbh->disconnect; $dbh=0; print "test_alter: Executed $i ALTER TABLE\n"; exit(0); } # # Help functions # sub signal_abort { my ($dbh); $dbh = DBI->connect("DBI:mysql:$opt_db:$opt_host", $opt_user, $opt_password, { PrintError => 0}) || die $DBI::errstr; $dbh->do("insert into $abort_table values(1)") || die $DBI::errstr; $dbh->disconnect; $dbh=0; } sub test_if_abort() { my ($dbh)=@_; $row=simple_query($dbh,"select * from $opt_db.$abort_table"); return (defined($row) && defined($row->[0]) != 0) ? 1 : 0; } sub make_count_query { my ($table_count)= @_; my ($tables, $count_query, $i, $tables_def); $tables=""; $count_query="select high_priority "; $table_count--; for ($i=0 ; $i < $table_count ; $i++) { my ($table_def)= $testtables[$i]; $tables.=$table_def->[0] . ","; $count_query.= "max(" . $table_def->[0] . ".id),"; } $table_def=$testtables[$table_count]; $tables.=$table_def->[0]; $count_query.= "max(" . $table_def->[0] . ".id) from $tables"; return $count_query; } sub simple_query() { my ($dbh, $query)= @_; my ($sth,$row); $sth=$dbh->prepare($query) || die "Got error on '$query': " . $dbh->errstr . "\n"; $sth->execute || die "Got error on '$query': " . $dbh->errstr . "\n"; $row= $sth->fetchrow_arrayref(); $sth=0; return $row; }