7
$opt_loop_count=100000; # number of rows/3
8
$small_loop_count=10; # Loop for full table retrieval
9
$range_loop_count=$small_loop_count*50;
10
$many_keys_loop_count=$opt_loop_count;
12
chomp($pwd = `pwd`); $pwd = "." if ($pwd eq '');
13
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
15
if ($opt_loop_count < 256)
17
$opt_loop_count=256; # Some tests must have some data to work!
23
$range_loop_count/=10;
24
$many_keys_loop_count=$opt_loop_count/10;
26
elsif ($opt_small_tables)
28
$opt_loop_count=10000; # number of rows/3
29
$many_keys_loop_count=$opt_loop_count;
31
elsif ($opt_small_key_tables)
33
$many_keys_loop_count/=10;
36
print "Testing the speed difference between some table types\n";
39
#### Generating random keys
42
print "Generating random keys\n";
43
$random[$opt_loop_count]=0;
44
for ($i=0 ; $i < $opt_loop_count ; $i++)
46
$random[$i]=$i+$opt_loop_count;
50
for ($i=0 ; $i < $opt_loop_count ; $i++)
52
$tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
53
$swap=$tmpvar % $opt_loop_count;
54
$tmp=$random[$i]; $random[$i]=$random[$swap]; $random[$swap]=$tmp;
57
$total_rows=$opt_loop_count*3;
60
#### Connect and start timeing
62
$start_time=new Benchmark;
63
$dbh = $server->connect();
65
#### Create needed tables
69
test($table_name,"type=isam","char");
70
test($table_name,"type=myisam pack_keys=0","char");
71
test($table_name,"type=myisam pack_keys=0","char");
72
test($table_name,"type=myisam pack_keys=0 checksum=1","char");
73
test($table_name,"type=myisam pack_keys=1","char");
75
test($table_name,"type=isam","varchar");
76
test($table_name,"type=myisam pack_keys=1","varchar");
77
test($table_name,"type=myisam pack_keys=0","varchar");
78
test($table_name,"type=myisam pack_keys=0 checksum=1","varchar");
80
#test("type=heap","char"); # The default table sizes is a bit big for this one
86
my ($name,$options,$chartype)=@_;
88
print "\nTesting with options: '$options'\n";
89
$dbh->do("drop table $name");
90
do_many($dbh,$server->create("$name",
94
"dummy1 $chartype(30)"],
95
["primary key (id,id2)",
96
"index index_id3 (id3)"],
101
$sth = $dbh->do("LOCK TABLES $name WRITE") || die $DBI::errstr;
104
if ($opt_fast && defined($server->{vacuum}))
106
$server->vacuum(\$dbh,1);
110
#### Insert $total_rows records in order, in reverse order and random.
113
$loop_time=new Benchmark;
115
if ($opt_fast_insert)
117
$query="insert into $name values ";
121
$query="insert into $name (id,id2,id3,dummy1) values ";
124
if (($opt_fast || $opt_fast_insert) && $limits->{'multi_value_insert'})
126
$query_size=$server->{'limits'}->{'query_size'};
128
print "Inserting $opt_loop_count multiple-value rows in order\n";
130
for ($i=0 ; $i < $opt_loop_count ; $i++)
132
$tmp= "($i,$i,$i,'ABCDEFGHIJ'),";
133
if (length($tmp)+length($res) < $query_size)
139
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
143
print "Inserting $opt_loop_count multiple-value rows in reverse order\n";
144
for ($i=0 ; $i < $opt_loop_count ; $i++)
146
$tmp= "(" . ($total_rows-1-$i) . "," .($total_rows-1-$i) .
147
"," .($total_rows-1-$i) . ",'BCDEFGHIJK'),";
148
if (length($tmp)+length($res) < $query_size)
154
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
158
print "Inserting $opt_loop_count multiple-value rows in random order\n";
159
for ($i=0 ; $i < $opt_loop_count ; $i++)
161
$tmp= "(" . $random[$i] . "," . $random[$i] . "," . $random[$i] .
162
",'CDEFGHIJKL')," or die $DBI::errstr;
163
if (length($tmp)+length($res) < $query_size)
169
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
173
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
177
print "Inserting $opt_loop_count rows in order\n";
178
for ($i=0 ; $i < $opt_loop_count ; $i++)
180
$sth = $dbh->do($query . "($i,$i,$i,'ABCDEFGHIJ')") or die $DBI::errstr;
183
print "Inserting $opt_loop_count rows in reverse order\n";
184
for ($i=0 ; $i < $opt_loop_count ; $i++)
186
$sth = $dbh->do($query . "(" . ($total_rows-1-$i) . "," .
187
($total_rows-1-$i) . "," .
188
($total_rows-1-$i) . ",'BCDEFGHIJK')")
192
print "Inserting $opt_loop_count rows in random order\n";
194
for ($i=0 ; $i < $opt_loop_count ; $i++)
196
$sth = $dbh->do($query . "(". $random[$i] . "," . $random[$i] .
197
"," . $random[$i] . ",'CDEFGHIJKL')") or die $DBI::errstr;
201
$end_time=new Benchmark;
202
print "Time for insert (" . ($total_rows) . "): " .
203
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
205
if ($opt_fast && defined($server->{vacuum}))
207
$server->vacuum(\$dbh,1);
210
$sth=$dbh->prepare("show table status like '$name'");
211
$sth->execute || die "Show table status returned error: $DBI::errstr\n";
212
while (@row = $sth->fetchrow_array)
214
print join("| ",@row) . " |\n";
216
$dbh->do("drop table $name") if (!$opt_skip_delete);