test-insert

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

    }
    else
    {
      $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
      $res=$query . $tmp . ",";
    }
  }
  $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
}
else
{
  for ($i=0; $i < $many_keys_loop_count; $i++)
  {
    $id= $i & 127;
    $rand=$random[$i];
    $query="insert into bench1 values ($id,$id,$rand," . ($i & 32767) .
      ",'ABCDEF$rand',0,$rand,$rand.0,";

    for ($j=8; $j <= $fields ; $j++)
    {
      $query.= ($types[$j] == 0) ? "$rand," : "'$rand',";
    }
    substr($query,-1)=")";
    print "query1: $query\n" if ($opt_debug);
    $dbh->do($query) or die "Got error $DBI::errstr with query: $query\n";
  }
}

if ($opt_fast && $server->{transactions})
{
  $dbh->commit;
  $dbh->{AutoCommit} = 1;
}

$end_time=new Benchmark;
print "Time for insert_key ($many_keys_loop_count): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n\n";

if ($server->small_rollback_segment())
{
  $dbh->disconnect;				# close connection
  $dbh = $server->connect();
}
if ($opt_fast && defined($server->{vacuum}))
{
  if ($opt_lock_tables)
  {
    do_query($dbh,"UNLOCK TABLES");
  }
  $server->vacuum(1,\$dbh,"bench1");
  if ($opt_lock_tables)
  {
    $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
  }
}

#
# update one key of the above
#

print "Testing update of keys\n";
$loop_time=new Benchmark;

if ($opt_fast && $server->{transactions})
{
  $dbh->{AutoCommit} = 0;
}

for ($i=0 ; $i< 256; $i++)
{
  $dbh->do("update bench1 set field5=1 where field_search=$i")
    or die "Got error $DBI::errstr with query: update bench1 set field5=1 where field_search=$i\n";
}

if ($opt_fast && $server->{transactions})
{
  $dbh->commit;
  $dbh->{AutoCommit} = 1;
}

$end_time=new Benchmark;
print "Time for update_of_primary_key_many_keys (256): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n\n";

if ($server->small_rollback_segment())
{
  $dbh->disconnect;				# close connection
  $dbh = $server->connect();
}
if ($opt_fast && defined($server->{vacuum}))
{
  if ($opt_lock_tables)
  {
    do_query($dbh,"UNLOCK TABLES");
  }
  $server->vacuum(1,\$dbh,"bench1");
  if ($opt_lock_tables)
  {
    $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
  }
}

if ($server->small_rollback_segment())
{
  $dbh->disconnect;				# close connection
  $dbh = $server->connect();
}

#
# Delete everything from table
#

print "Deleting rows from the table\n";
$loop_time=new Benchmark;
$count=0;

for ($i=0 ; $i < 128 ; $i++)
{
  $count++;
  $dbh->do("delete from bench1 where field_search = $i") or die $DBI::errstr;
}

$end_time=new Benchmark;
print "Time for delete_big_many_keys ($count): " .
timestr(timediff($end_time, $loop_time),"all") . "\n\n";

if ($opt_lock_tables)
{
  $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
}

print "Deleting everything from table\n";
$count=1;
if ($opt_fast)
{
  $query= ($limits->{'truncate_table'} ? "truncate table bench1" :
	     "delete from bench1");
  $dbh->do($query) or die $DBI::errstr;
}
else
{
  $dbh->do("delete from bench1 where field1 > 0") or die $DBI::errstr;
}

$end_time=new Benchmark;
print "Time for delete_all_many_keys ($count): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n\n";

$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
if ($opt_fast && defined($server->{vacuum}))
{
  $server->vacuum(1,\$dbh);
}

#
# Test multi value inserts if the server supports it
#

if ($limits->{'insert_multi_value'})
{
  $query_size=$limits->{'query_size'}; # Same limit for all databases

  $sth = $dbh->do("drop table bench1" . $server->{'drop_attr'});
  do_many($dbh,$server->create("bench1",
			       ["id int NOT NULL",
				"id2 int NOT NULL",
				"id3 int NOT NULL",
				"dummy1 char(30)"],
			       ["primary key (id,id2)",
			       "index index_id3 (id3)"]));

  $loop_time=new Benchmark;

  if ($opt_lock_tables)
  {
    $sth = $dbh->do("LOCK TABLES bench1 write") || die $DBI::errstr;
  }
  if ($opt_fast && $server->{transactions})
  {
    $dbh->{AutoCommit} = 0;
  }

  print "Inserting $opt_loop_count rows with multiple values\n";
  $query="insert into bench1 values ";
  $res=$query;
  for ($i=0 ; $i < $opt_loop_count ; $i++)
  {
    my $tmp= "($i,$i,$i,'EFGHIJKLM'),";
    if (length($i)+length($res) < $query_size)
    {
      $res.= $tmp;
    }
    else
    {
      do_query($dbh,substr($res,0,length($res)-1));
      $res=$query .$tmp;
    }
  }
  do_query($dbh,substr($res,0,length($res)-1));

  if ($opt_lock_tables)
  {
    $sth = $dbh->do("UNLOCK TABLES ") || die $DBI::errstr;
  }
  if ($opt_fast && $server->{transactions})
  {
    $dbh->commit;
    $dbh->{AutoCommit} = 1;
  }

  $end_time=new Benchmark;
  print "Time for multiple_value_insert (" . ($opt_loop_count) . "): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";

  if ($opt_lock_tables)
  {
    $sth = $dbh->do("UNLOCK TABLES ") || die $DBI::errstr;
  }

  # A big table may take a while to drop
  $loop_time=new Benchmark;
  $sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
  $end_time=new Benchmark;
  print "Time for drop table(1): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
}

####
#### End of benchmark
####

$dbh->disconnect;				# close connection

end_benchmark($start_time);

###
### Some help functions
###


# Do some sample selects on direct key
# First select finds a row, the second one doesn't find.

sub check_select_key
{
  my ($sel_columns,$column,$check)= @_;
  my ($loop_time,$end_time,$i,$tmp_var,$tmp,$count,$row_count,$estimated);

  $estimated=0;
  $loop_time=new Benchmark;
  $count=0;
  for ($i=1 ; $i <= $opt_read_key_loop_count; $i++)
  {
    $count+=2;
    $tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
    $tmp=$tmpvar % ($total_rows);
    fetch_all_rows($dbh,"select $sel_columns from bench1 where $column=$tmp")
      or die $DBI::errstr;
    $tmp+=$total_rows;
    defined($row_count=fetch_all_rows($dbh,"select $sel_columns from bench1 where $column=$tmp")) or die $DBI::errstr;
    die "Found $row_count rows on impossible id: $tmp\n" if ($row_count);
    $end_time=new Benchmark;
    last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i,
					   $opt_loop_count));
  }
  if ($estimated)
  { print "Estimated time"; }
  else
  { print "Time"; }
  print " for $check ($count): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";
}

# Same as above, but select on 2 columns

sub check_select_key2
{
  my ($sel_columns,$column,$column2,$check)= @_;
  my ($loop_time,$end_time,$i,$tmp_var,$tmp,$count,$row_count,$estimated);

  $estimated=0;
  $loop_time=new Benchmark;
  $count=0;
  for ($i=1 ; $i <= $opt_read_key_loop_count; $i++)
  {
    $count+=2;
    $tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
    $tmp=$tmpvar % ($total_rows);
    fetch_all_rows($dbh,"select $sel_columns from bench1 where $column=$tmp and $column2=$tmp")
      or die $DBI::errstr;
    $tmp+=$total_rows;
    defined($row_count=fetch_all_rows($dbh,"select $sel_columns from bench1 where $column=$tmp and $column2=$tmp")) or die $DBI::errstr;
    die "Found $row_count rows on impossible id: $tmp\n" if ($row_count);
    $end_time=new Benchmark;
    last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i,
					   $opt_loop_count));
  }
  if ($estimated)
  { print "Estimated time"; }
  else
  { print "Time"; }
  print " for $check ($count): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";
}

#
# Search using some very simple queries
#

sub check_select_range
{
  my ($column,$check)= @_;
  my ($loop_time,$end_time,$i,$tmp_var,$tmp,$query,$rows,$estimated);

  $estimated=0;
  $loop_time=new Benchmark;
  $found=$count=0;
  for ($test=1 ; $test <= $range_loop_count; $test++)
  {
    $count+=$#Q+1;
    for ($i=0 ; $i < $#Q ; $i+=2)
    {
      $query=$Q[$i];
      $rows=$Q[$i+1];
      $query =~ s/!id!/$column/g;
      if (($row_count=fetch_all_rows($dbh,$query)) != $rows)
      {
	if ($row_count == undef())
	{
	  die "Got error: $DBI::errstr when executing $query\n";
	}
	die "'$query' returned wrong number of rows: $row_count instead of $rows\n";
      }
      $found+=$row_count;
    }
    $end_time=new Benchmark;
    last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$test,
					   $range_loop_count));
  }
  if ($estimated)
  { print "Estimated time"; }
  else
  { print "Time"; }
  print " for $check ($count:$found): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";
}


#
# SELECT * from bench where col=x or col=x or col=x ...


sub check_or_range
{
  my ($column,$check)= @_;
  my ($loop_time,$end_time,$i,$tmp_var,$tmp,$columns,$estimated,$found,
      $or_part,$count,$loop_count);

  $columns=min($limits->{'max_columns'},50,($limits->{'query_size'}-50)/13);
  $columns=$columns- ($columns % 4); # Make Divisible by 4

  $estimated=0;
  $loop_time=new Benchmark;
  $found=0;
  # The number of tests must be divisible by the following
  $tmp= $limits->{'func_extra_in_num'} ? 15 : 10; 
  # We need to calculate the exact number of test to make 'Estimated' right
  $loop_count=$range_loop_count*10+$tmp-1;
  $loop_count=$loop_count- ($loop_count % $tmp);
  
  for ($count=0 ; $count < $loop_count ; )
  {
    for ($rowcnt=0; $rowcnt <= $columns; $rowcnt+= $columns/4)
    {
      my $query="select * from bench1 where ";
      my $or_part= "$column = 1";
      $count+=2;

      for ($i=1 ; $i < $rowcnt ; $i++)
      {
	$tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
	$tmp=$tmpvar % ($opt_loop_count*4);
	$or_part.=" or $column=$tmp";
      }
      print $query . $or_part . "\n" if ($opt_debug);
      ($rows=fetch_all_rows($dbh,$query . $or_part)) or die $DBI::errstr;
      $found+=$rows;

      if ($limits->{'func_extra_in_num'})
      {
	my $in_part=$or_part;	# Same query, but use 'func_extra_in_num' instead.
	$in_part=~ s/ = / IN \(/;
	$in_part=~ s/ or $column=/,/g;
	$in_part.= ")";
	fetch_all_rows($dbh,$query . $in_part) or die $DBI::errstr;
	$count++;
      }
      # Do it a little harder by setting a extra range
      defined(($rows=fetch_all_rows($dbh,"$query($or_part) and $column < 10"))) or die $DBI::errstr;
      $found+=$rows;
    }
    $end_time=new Benchmark;
    last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count,
					   $loop_count));
  }

  if ($estimated)
  { print "Estimated time"; }
  else
  { print "Time"; }
  print " for $check ($count:$found): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";
}

#
# General test of SELECT ... WHERE id in(value-list)
#

sub test_where_in
{
  my ($t1,$t2,$id,$from,$to)= @_;

  return if ($from >= $to);

  $query="SELECT $t1.* FROM $t1 WHERE $id IN (";
  for ($i=1 ; $i <= $to ; $i++)
  {
    $query.="$i,";
  }
  $query=substr($query,0,length($query)-1) . ")";

  # Fill join table to have the same id's as 'query'
  for ($i= $from ; $i <= $to ; $i++)
  {
    $dbh->do("insert into $t2 values($i)") or die $DBI::errstr;
  }
  if ($opt_fast && defined($server->{vacuum}))
  {
    $server->vacuum(1,\$dbh,"bench1");
  }

  time_fetch_all_rows("Testing SELECT ... WHERE id in ($to values)",
		      "select_in", $query, $dbh,
		      $range_loop_count);
  time_fetch_all_rows(undef, "select_join_in",
		      "SELECT $t1.* FROM $t2 left outer join $t1 on ($t1.$id=$t2.$id)",
		       $dbh, $range_loop_count);
}