fork_big.pl
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:14k
- #!/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_delete=$opt_fast=$opt_force=0;
- $opt_threads=5;
- $opt_host=$opt_user=$opt_password=""; $opt_db="test";
- GetOptions("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";
- $opt_verbose=$opt_debug=$opt_lock_tables=$opt_fast_insert=$opt_fast=$opt_skip_in=$opt_force=undef; # Ignore warnings from these
- print "Test of multiple connections that test the following things:n";
- print "insert, select, delete, update, alter, check, repair and flushn";
- @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
- ####
- $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_dbn";
- $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
- ####
- for ($i=0 ; $i < $opt_threads ; $i ++)
- {
- test_insert() if (($pid=fork()) == 0); $work{$pid}="insert";
- }
- for ($i=0 ; $i < $numtables ; $i ++)
- {
- test_insert($i,$i) if (($pid=fork()) == 0); $work{$pid}="insert_one";
- }
- for ($i=0 ; $i < $opt_threads ; $i ++)
- {
- test_select() if (($pid=fork()) == 0); $work{$pid}="select_key";
- }
- test_join() if (($pid=fork()) == 0); $work{$pid}="test_join";
- test_select_count() if (($pid=fork()) == 0); $work{$pid}="select_count";
- test_delete() if (($pid=fork()) == 0); $work{$pid}="delete";
- test_update() if (($pid=fork()) == 0); $work{$pid}="update";
- test_flush() if (($pid=fork()) == 0); $work{$pid}= "flush";
- test_check() if (($pid=fork()) == 0); $work{$pid}="check";
- test_repair() if (($pid=fork()) == 0); $work{$pid}="repair";
- test_alter() if (($pid=fork()) == 0); $work{$pid}="alter";
- #test_database("test2") if (($pid=fork()) == 0); $work{$pid}="check_database";
- print "Started " . ($opt_threads*2+4) . " threadsn";
- $errors=0;
- $running_insert_threads=$opt_threads+$numtables;
- while (($pid=wait()) != -1)
- {
- $ret=$?/256;
- print "thread '" . $work{$pid} . "' finished with exit code $retn";
- if ($work{$pid} =~ /^insert/)
- {
- if (!--$running_insert_threads)
- {
- # Time to stop other threads
- signal_abort();
- }
- }
- $errors++ if ($ret != 0);
- }
- #
- # Cleanup
- #
- if (!$opt_skip_delete && !$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 failedn" :"Test okn");
- $end_time=new Benchmark;
- print "Total time: " .
- timestr(timediff($end_time, $start_time),"noc") . "n";
- 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::errstrn";
- $count++;
- }
- }
- $dbh->disconnect; $dbh=0;
- print "Test_insert: Inserted $count rowsn";
- 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 selectsn";
- 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) queriesn";
- 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 joinsn";
- 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::errstrn";
- $count++;
- }
- }
- $dbh->disconnect; $dbh=0;
- print "Test_delete: Executed $count deletesn";
- 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::errstrn";
- $count++;
- }
- }
- $dbh->disconnect; $dbh=0;
- print "Test_update: Executed $count updatesn";
- 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::errstrn";
- $sth->execute || die $DBI::errstr;
- while (($row=$sth->fetchrow_arrayref))
- {
- if ($row->[3] ne "OK")
- {
- print "Got error " . $row->[3] . " when doing $type on $tablen";
- exit(1);
- }
- }
- if (++$j == $numtables-1)
- {
- $j=0;
- }
- }
- $dbh->disconnect; $dbh=0;
- print "test_check: Executed $i checksn";
- 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::errstrn";
- $sth->execute || die $DBI::errstr;
- while (($row=$sth->fetchrow_arrayref))
- {
- if ($row->[3] ne "OK")
- {
- print "Got error " . $row->[3] . " when doing $type on $tablen";
- exit(1);
- }
- }
- }
- $dbh->disconnect; $dbh=0;
- print "test_repair: Executed $i repairsn";
- 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::errstrn";
- $count++;
- }
- $dbh->disconnect; $dbh=0;
- print "flush: Executed $count flushsn";
- 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::errstrn";
- $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 checksn";
- 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::errstrn";
- $sth->execute || die $DBI::errstr;
- }
- $dbh->disconnect; $dbh=0;
- print "test_alter: Executed $i ALTER TABLEn";
- 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;
- exit(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;
- }