fork_big2.pl

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

#!/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