fork_big2.pl

几个嵌入式MYSQL编程示例

# 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;
}