test-insert

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

  #restore id3 to 0 <= id3 < $total_rows/10 or 0<= id3 < $total_rows

  my $func=($limits->{'func_odbc_floor'}) ? "floor((0-id3)/20)" : "0-id3";
  $count++;
  $sth=$dbh->do($query="update bench1 set id3=$func where id3<0") or die $DBI::errstr;

  $end_time=new Benchmark;
  print "Time for update_of_key_big ($count): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
}
else
{
  print "\nTesting update of keys in loops\n";
  #
  # This is for mSQL that doesn't have functions. Do we really need this ????
  #

  $sth=$dbh->prepare("select id3 from bench1 where id3 >= 0") or die $DBI::errstr;
  $sth->execute or die $DBI::errstr;
  $count=0;
  while (@tmp = $sth->fetchrow_array)
  {
    my $tmp1 = "-$tmp[0]";
    my $sth1 = $dbh->do("update bench1 set id3 = $tmp1 where id3 = $tmp[0]");
    $count++;
    $end_time=new Benchmark;
    if (($end_time->[0] - $loop_time->[0]) > $opt_time_limit)
    {
      print "note: Aborting update loop because of timeout\n";
      last;
    }
  }
  $sth->finish;
  # Check that everything except id3=0 was updated
  # In principle we shouldn't time this in the update loop..
  #
  if (fetch_all_rows($dbh,$query="select * from bench1 where id3>=0") != 1)
  {
    if ($count == $total_rows)
    {
      print "Warning: Wrong information after update: Found '$row_count' rows, but should have been: 1\n";
    }
  }
  #restore id3 to 0 <= id3 < $total_rows
  $sth=$dbh->prepare("select id3 from bench1 where id3 < 0") or die $DBI::errstr;
  $sth->execute or die $DBI::errstr;
  while (@tmp = $sth->fetchrow_array)
  {
    $count++;
    my $tmp1 = floor((0-$tmp[0])/10);
    my $sth1 = $dbh->do("update bench1 set id3 = $tmp1 where id3 = $tmp[0]");
  }
  $sth->finish;
  $end_time=new Benchmark;
  $estimated=predict_query_time($loop_time,$end_time,\$count,$count,
				$opt_loop_count*6);
  if ($estimated)
  { print "Estimated time"; }
  else
  { print "Time"; }
  print " for update_of_key ($count): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
}

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

#
# Testing some simple updates
#

print "Testing update with key\n";
$loop_time=new Benchmark;
for ($i=0 ; $i < $opt_loop_count*3 ; $i++)
{
  $sth = $dbh->do("update bench1 set dummy1='updated' where id=$i and id2=$i") or die $DBI::errstr;
}

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

$loop_time=new Benchmark;
$count=0;
for ($i=1 ; $i < $opt_loop_count*3 ; $i+=3)
{
  $sth = $dbh->do("update bench1 set dummy1='updated' where id=$i") or die $DBI::errstr;
  $end_time=new Benchmark;
  last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$tests,
					 $opt_loop_count));
}
if ($estimated)
{ print "Estimated time"; }
else
{ print "Time"; }
print " for update_with_key_prefix (" . ($opt_loop_count) . "):  " .
  timestr(timediff($end_time, $loop_time),"all") . "\n";

print "\nTesting update of all rows\n";
$loop_time=new Benchmark;
for ($i=0 ; $i < $small_loop_count ; $i++)
{
  $sth = $dbh->do("update bench1 set dummy1='updated $i'") or die $DBI::errstr;
}
$end_time=new Benchmark;
print "Time for update_big ($small_loop_count):  " .
  timestr(timediff($end_time, $loop_time),"all") . "\n";


#
# Testing left outer join
#

if ($limits->{'func_odbc_floor'} && $limits->{'left_outer_join'})
{
  if ($opt_lock_tables)
  {
    $sth = $dbh->do("LOCK TABLES bench1 a READ, bench1 b READ") || die $DBI::errstr;
  }
  print "\nTesting left outer join\n";
  $loop_time=new Benchmark;
  $count=0;
  for ($i=0 ; $i < $small_loop_count ; $i++)
  {
    $count+=fetch_all_rows($dbh,"select count(*) from bench1 as a left outer join bench1 as b on (a.id2=b.id3)");
  }
  $end_time=new Benchmark;
  print "Time for outer_join_on_key ($small_loop_count:$count):  " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";

  $loop_time=new Benchmark;
  $count=0;
  for ($i=0 ; $i < $small_loop_count ; $i++)
  {
    $count+=fetch_all_rows($dbh,"select count(a.dummy1),count(b.dummy1) from bench1 as a left outer join bench1 as b on (a.id2=b.id3)");
  }
  $end_time=new Benchmark;
  print "Time for outer_join ($small_loop_count:$count):  " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";

  $count=0;
  $loop_time=new Benchmark;
  for ($i=0 ; $i < $small_loop_count ; $i++)
  {
    $count+=fetch_all_rows($dbh,"select count(a.dummy1),count(b.dummy1) from bench1 as a left outer join bench1 as b on (a.id2=b.id3) where b.id3 is not null");
  }
  $end_time=new Benchmark;
  print "Time for outer_join_found ($small_loop_count:$count):  " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";

  $count=$estimated=0;
  $loop_time=new Benchmark;
  for ($i=1 ; $i <= $small_loop_count ; $i++)
  {
    $count+=fetch_all_rows($dbh,"select count(a.dummy1),count(b.dummy1) from bench1 as a left outer join bench1 as b on (a.id2=b.id3) where b.id3 is null");
    $end_time=new Benchmark;
    last if ($estimated=predict_query_time($loop_time,$end_time,
					   \$count,$i,
					   $range_loop_count));
  }
  if ($estimated)
  { print "Estimated time"; }
  else
  { print "Time"; }
  print " for outer_join_not_found ($range_loop_count:$count):  " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";

  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();
}

###
### Test speed of IN( value list)
###

if ($limits->{'left_outer_join'})
{
  if ($opt_lock_tables)
  {
    $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
  }
  print "\n";
  do_many($dbh,$server->create("bench2",
			       ["id int NOT NULL"],
			       ["primary key (id)"]));

  $max_tests=min(($limits->{'query_size'}-50)/6, $opt_loop_count);

  if ($opt_lock_tables)
  {
    $sth = $dbh->do("LOCK TABLES bench1 READ, bench2 WRITE") ||
      die $DBI::errstr;
  }
  test_where_in("bench1","bench2","id",1,10);
  test_where_in("bench1","bench2","id",11,min(100,$max_tests));
  test_where_in("bench1","bench2","id",101,min(1000,$max_tests));
  if ($opt_lock_tables)
  {
    $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
  }
  $sth = $dbh->do("DROP TABLE bench2" . $server->{'drop_attr'}) ||
    die $DBI::errstr;
  if ($opt_lock_tables)
  {
    $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
  }
}

####
#### Test INSERT INTO ... SELECT
####

if ($limits->{'insert_select'})
{
  if ($opt_lock_tables)
  {
    $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
  }
  print "\nTesting INSERT INTO ... SELECT\n";
  do_many($dbh,$server->create("bench2",
			       ["id int NOT NULL",
				"id2 int NOT NULL",
				"id3 int NOT NULL",
				"dummy1 char(30)"],
			       ["primary key (id,id2)"]));
  do_many($dbh,$server->create("bench3",
			       ["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;
  $sth = $dbh->do("INSERT INTO bench2 SELECT * from bench1") ||
    die $DBI::errstr;
  $end_time=new Benchmark;
  print "Time for insert_select_1_key (1):  " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";
  $loop_time=new Benchmark;
  $sth = $dbh->do("INSERT INTO bench3 SELECT * from bench1") ||
    die $DBI::errstr;
  $end_time=new Benchmark;
  print "Time for insert_select_2_keys (1):  " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";
  $loop_time=new Benchmark;
  $sth = $dbh->do("DROP TABLE bench2" . $server->{'drop_attr'}) ||
    die $DBI::errstr;
  $sth = $dbh->do("DROP TABLE bench3" . $server->{'drop_attr'}) ||
    die $DBI::errstr;
  $end_time=new Benchmark;
  print "Time for drop table(2): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";

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

####
#### Do some deletes on the table
####

if (!$opt_skip_delete)
{
  print "\nTesting delete\n";
  $loop_time=new Benchmark;
  $count=0;
  for ($i=0 ; $i < $opt_loop_count ; $i+=10)
  {
    $count++;
    $tmp=$opt_loop_count+$random[$i]; # $opt_loop_count*2 <= $tmp < $total_rows
    $dbh->do("delete from bench1 where id3=$tmp") or die $DBI::errstr;
  }

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

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

  $count=0;
  $loop_time=new Benchmark;
  for ($i= 0 ; $i < $opt_loop_count ; $i+=$opt_loop_count/10)
  {
    $sth=$dbh->do("delete from bench1 where id3 >= 0 and id3 <= $i") or die $DBI::errstr;
    $count++;
  }
  $count+=2;
  if ($server->small_rollback_segment())
  {
    $dbh->disconnect;				# close connection
    $dbh = $server->connect();
  }
  $sth=$dbh->do("delete from bench1 where id3 >= 0 and id3 <= $opt_loop_count") or die $DBI::errstr;
  if ($server->small_rollback_segment())
  {
    $dbh->disconnect;				# close connection
    $dbh = $server->connect();
  }

  $sth=$dbh->do("delete from bench1 where id >= $opt_loop_count and id <= " . ($opt_loop_count*2) ) or die $DBI::errstr;

  if ($server->small_rollback_segment())
  {
    $dbh->disconnect;				# close connection
    $dbh = $server->connect();
  }
  if ($opt_fast)
  {
    $sth=$dbh->do("delete from bench1") or die $DBI::errstr;
  }
  else
  {
    $sth = $dbh->do("delete from bench1 where id3 < " . ($total_rows)) or die $DBI::errstr;
  }

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

  if ($opt_lock_tables)
  {
    $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
  }
  $sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
}

if ($server->small_rollback_segment())
{
  $dbh->disconnect;				# close connection
  $dbh = $server->connect();
}
if ($opt_fast && defined($server->{vacuum}))
{
  $server->vacuum(1,\$dbh);
}


keys_test:
#
# Test of insert in table with many keys
# This test assumes that the server really create the keys!
#

my @fields=(); my @keys=();
$keys=min($limits->{'max_index'},16);		  # 16 is more than enough
$seg= min($limits->{'max_index_parts'},$keys,16); # 16 is more than enough

print "Insert into table with $keys keys and with a primary key with $seg parts\n";

# Make keys on the most important types
@types=(0,0,0,1,0,0,0,1,1,1,1,1,1,1,1,1,1);	# A 1 for each char field
push(@fields,"field1 tinyint not null");
push(@fields,"field_search tinyint not null");
push(@fields,"field2 mediumint not null");
push(@fields,"field3 smallint not null");
push(@fields,"field4 char(16) not null");
push(@fields,"field5 integer not null");
push(@fields,"field6 float not null");
push(@fields,"field7 double not null");
for ($i=8 ; $i <= $keys ; $i++)
{
  push(@fields,"field$i char(6) not null");	# Should be relatively fair
}

# First key contains many segments
$query="primary key (";
for ($i= 1 ; $i <= $seg ; $i++)
{
  $query.= "field$i,";
}
substr($query,-1)=")";
push (@keys,$query);
push (@keys,"index index2 (field_search)");

#Create other keys
for ($i=3 ; $i <= $keys ; $i++)
{
  push(@keys,"index index$i (field$i)");
}

do_many($dbh,$server->create("bench1",\@fields,\@keys));
if ($opt_lock_tables)
{
  $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
}

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

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

$fields=$#fields;
if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value'})
{
  $query_size=$server->{'limits'}->{'query_size'};
  $query="insert into bench1 values ";
  $res=$query;
  for ($i=0; $i < $many_keys_loop_count; $i++)
  {
    $id= $i & 127;
    $rand=$random[$i];
    $tmp="($id,$id,$rand," . ($i & 32766) . ",'ABCDEF$rand',0,$rand,$rand.0,";

    for ($j=8; $j <= $fields ; $j++)
    {
      $tmp.= ($types[$j] == 0) ? "$rand," : "'$rand',";
    }
    substr($tmp,-1)=")";
    if (length($tmp)+length($res) < $query_size)
    {
      $res.= $tmp . ",";