test-insert

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

#!/usr/bin/perl
# Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
#
# This library is free software; you can redistribute it and/or
# modify it under the terms of the GNU Library General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This library is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# Library General Public License for more details.
#
# You should have received a copy of the GNU Library General Public
# License along with this library; if not, write to the Free
# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
# MA 02111-1307, USA
#
# Test of creating a simple table and inserting $record_count records in it,
# $opt_loop_count rows in order, $opt_loop_count rows in reverse order and
# $opt_loop_count rows in random order
#
# changes made for Oracle compatibility
# - $limits->{'func_odbc_mod'} is OK from crash-me, but it fails here so set we
#   set it to 0 in server-cfg
# - the default server config runs out of rollback segments, so we added a
#   couple of disconnect/connects to reset
#
##################### Standard benchmark inits ##############################

use Cwd;
use DBI;
use Benchmark;
use Data::Dumper;

$opt_loop_count=100000;		# number of rows/3
$small_loop_count=10;		# Loop for full table retrieval
$range_loop_count=$small_loop_count*50;
$many_keys_loop_count=$opt_loop_count;
$opt_read_key_loop_count=$opt_loop_count;

$pwd = cwd(); $pwd = "." if ($pwd eq '');
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";

if ($opt_small_test)
{
  $opt_loop_count/=100;
  $many_keys_loop_count=$opt_loop_count/10;
  $range_loop_count=10;
  $opt_read_key_loop_count=10;
}
elsif ($opt_small_tables)
{
  $opt_loop_count=10000;		# number of rows/3
  $many_keys_loop_count=$opt_loop_count;
  $opt_read_key_loop_count=10;
}
elsif ($opt_small_key_tables)
{
  $many_keys_loop_count/=10;
}

if ($opt_loop_count < 100)
{
  $opt_loop_count=100;		# Some tests must have some data to work!
}
$range_loop_count=min($opt_loop_count,$range_loop_count);


print "Testing the speed of inserting data into 1 table and do some selects on it.\n";
print "The tests are done with a table that has $opt_loop_count rows.\n\n";

####
#### Generating random keys
####

print "Generating random keys\n";
$random[$opt_loop_count]=0;
for ($i=0 ; $i < $opt_loop_count ; $i++)
{
  $random[$i]=$i+$opt_loop_count;
}

my $tmpvar=1;
for ($i=0 ; $i < $opt_loop_count ; $i++)
{
  $tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
  $swap=$tmpvar % $opt_loop_count;
  $tmp=$random[$i]; $random[$i]=$random[$swap]; $random[$swap]=$tmp;
}

$total_rows=$opt_loop_count*3;

####
####  Connect and start timeing
####
$start_time=new Benchmark;
$dbh = $server->connect();
####
#### Create needed tables
####

goto keys_test if ($opt_stage == 2);
goto select_test if ($opt_skip_create);

print "Creating tables\n";
$dbh->do("drop table bench1" . $server->{'drop_attr'});
$dbh->do("drop table bench2" . $server->{'drop_attr'});
$dbh->do("drop table bench3" . $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 ix_id3 (id3)"]));

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

####
#### Insert $total_rows records in order, in reverse order and random.
####

$loop_time=new Benchmark;

if ($opt_fast_insert)
{
  $query="insert into bench1 values ";
}
else
{
  $query="insert into bench1 (id,id2,id3,dummy1) values ";
}

if ($opt_fast && $server->{transactions})
{
  $dbh->{AutoCommit} = 0;
  print "Transactions enabled\n" if ($opt_debug);
}

if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value'})
{
  $query_size=$server->{'limits'}->{'query_size'};

  print "Inserting $opt_loop_count multiple-value rows in order\n";
  $res=$query;
  for ($i=0 ; $i < $opt_loop_count ; $i++)
  {
    $tmp= "($i,$i,$i,'ABCDEFGHIJ'),";
    if (length($tmp)+length($res) < $query_size)
    {
      $res.= $tmp;
    }
    else
    {
      $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
      $res=$query . $tmp;
    }
  }
  print "Inserting $opt_loop_count multiple-value rows in reverse order\n";
  for ($i=0 ; $i < $opt_loop_count ; $i++)
  {
    $tmp= "(" . ($total_rows-1-$i) . "," .($total_rows-1-$i) .
      "," .($total_rows-1-$i) . ",'BCDEFGHIJK'),";
    if (length($tmp)+length($res) < $query_size)
    {
      $res.= $tmp;
    }
    else
    {
      $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
      $res=$query . $tmp;
    }
  }
  print "Inserting $opt_loop_count multiple-value rows in random order\n";
  for ($i=0 ; $i < $opt_loop_count ; $i++)
  {
    $tmp= "(" . $random[$i] . "," . $random[$i] . "," . $random[$i] .
      ",'CDEFGHIJKL')," or die $DBI::errstr;
    if (length($tmp)+length($res) < $query_size)
    {
      $res.= $tmp;
    }
    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
{
  print "Inserting $opt_loop_count rows in order\n";
  for ($i=0 ; $i < $opt_loop_count ; $i++)
  {
    $sth = $dbh->do($query . "($i,$i,$i,'ABCDEFGHIJ')") or die $DBI::errstr;
  }

  print "Inserting $opt_loop_count rows in reverse order\n";
  for ($i=0 ; $i < $opt_loop_count ; $i++)
  {
    $sth = $dbh->do($query . "(" . ($total_rows-1-$i) . "," .
		    ($total_rows-1-$i) . "," .
		    ($total_rows-1-$i) . ",'BCDEFGHIJK')")
      or die $DBI::errstr;
  }

  print "Inserting $opt_loop_count rows in random order\n";

  for ($i=0 ; $i < $opt_loop_count ; $i++)
  {
    $sth = $dbh->do($query . "(". $random[$i] . "," . $random[$i] .
		    "," . $random[$i] . ",'CDEFGHIJKL')") or die $DBI::errstr;
  }
}

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

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

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

####
#### insert $opt_loop_count records with duplicate id
####

if ($limits->{'unique_index'})
{
  print "Testing insert of duplicates\n";
  $loop_time=new Benchmark;

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

  for ($i=0 ; $i < $opt_loop_count ; $i++)
  {
    $tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
    $tmp=$tmpvar % ($total_rows);
    $tmpquery = "$query ($tmp,$tmp,2,'D')";
    if ($dbh->do($tmpquery))
    {
      die "Didn't get an error when inserting duplicate record $tmp\n";
    }
  }
  if ($opt_fast && $server->{transactions})
  {
    $dbh->commit;
    $dbh->{AutoCommit} = 1;
  }

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


####
#### Do some selects on the table
####

select_test:

# ----------------- prepared+executed/prepared*executed tests

print "Test of prepared+execute/once prepared many execute selects\n";
$loop_time=new Benchmark;

for ($i=1 ; $i <= $opt_loop_count ; $i++)
{
  my ($key_value)=$random[$i];
  my ($query)= "select * from bench1 where id=$key_value";  
  print "$query\n" if ($opt_debug);
  $sth = $dbh->prepare($query);
  if (! $sth)
    {
      die "error in prepare select with id = $key_value : $DBI::errstr";
    };
  if (! $sth->execute)  
   {
      die "cannot execute prepare select with id = $key_value : $DBI::errstr";      
   }
  while ($sth->fetchrow_arrayref) { };      
  $sth->finish;
};
$end_time=new Benchmark;
print "Time for prepared_select ($opt_loop_count): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";

$loop_time=new Benchmark;
$query= "select * from bench1 where id=?";  
$sth = $dbh->prepare($query);
if (! $sth)
{
  die "cannot prepare select: $DBI::errstr";
};

for ($i=1 ; $i <= $opt_loop_count ; $i++)
{
  my ($key_value)=$random[$i];
  $sth->bind_param(1,$key_value);
  print "$query , id = $key_value\n" if ($opt_debug);
  if (! $sth->execute)  
   {
      die "cannot execute prepare select with id = $key_value : $DBI::errstr";      
   }
  while ($sth->fetchrow_arrayref) { };      
};
$sth->finish;
$end_time=new Benchmark;
print "Time for once_prepared_select ($opt_loop_count): " .
    timestr(timediff($end_time, $loop_time),"all") . "\n";


print "Retrieving data from the table\n";
$loop_time=new Benchmark;
$error=0;

# It's really a small table, so we can try a select on everything

$count=0;
for ($i=1 ; $i <= $small_loop_count ; $i++)
{
  if (($found_rows=fetch_all_rows($dbh,"select id from bench1")) !=
      $total_rows)
  {
    if (!$error++)
    {
      print "Warning: Got $found_rows rows when selecting a whole table of " . ($total_rows) . " rows\nContact the database or DBD author!\n";
    }
  }
  $count+=$found_rows;
}

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

#
# Do a lot of different ORDER BY queries
#

$loop_time=new Benchmark;
$estimated=$rows=0;
for ($i=1 ; $i <= $small_loop_count ; $i++)
{
  $rows+=fetch_all_rows($dbh,"select id,id2 from bench1 order by id,id2",1);
  $end_time=new Benchmark;
  last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
					 $small_loop_count));
}
if ($estimated)
{ print "Estimated time"; }
else
{ print "Time"; }
print " for order_by_big_key ($small_loop_count:$rows): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n";


$loop_time=new Benchmark;
$estimated=$rows=0;
for ($i=1 ; $i <= $small_loop_count ; $i++)
{
  $rows+=fetch_all_rows($dbh,"select id,id2 from bench1 order by id desc, id2 desc",1);
  $end_time=new Benchmark;
  last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
					 $small_loop_count));
}
if ($estimated)
{ print "Estimated time"; }
else
{ print "Time"; }
print " for order_by_big_key_desc ($small_loop_count:$rows): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n";


$loop_time=new Benchmark;
$estimated=$rows=0;
for ($i=1 ; $i <= $small_loop_count ; $i++)
{
  $rows+=fetch_all_rows($dbh,"select id from bench1 order by id desc",1);
  $end_time=new Benchmark;
  last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
					 $small_loop_count));
}
if ($estimated)
{ print "Estimated time"; }
else
{ print "Time"; }
print " for order_by_big_key_prefix ($small_loop_count:$rows): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n";


$loop_time=new Benchmark;
$estimated=$rows=0;
for ($i=1 ; $i <= $small_loop_count ; $i++)
{
  $rows+=fetch_all_rows($dbh,"select id3 from bench1 order by id3",1);
  $end_time=new Benchmark;
  last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
					 $small_loop_count));
}
if ($estimated)
{ print "Estimated time"; }
else
{ print "Time"; }
print " for order_by_big_key2 ($small_loop_count:$rows): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n";


$sel=$limits->{'order_by_unused'} ? "id2" : "*";
$loop_time=new Benchmark;
$estimated=$rows=0;
for ($i=1 ; $i <= $small_loop_count ; $i++)
{
  $rows+=fetch_all_rows($dbh,"select $sel from bench1 order by id3",1);
  $end_time=new Benchmark;
  last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
					 $small_loop_count));
}
if ($estimated)
{ print "Estimated time"; }
else
{ print "Time"; }
print " for order_by_big_key_diff ($small_loop_count:$rows): " .
  timestr(timediff($end_time, $loop_time),"all") . "\n";


$sel=$limits->{'order_by_unused'} ? "id" : "*";