~drizzle-trunk/drizzle/development

2324.2.1 by patrick crews
Initial work for sql-bench mode. Added sql-bench to the tree. Test script for running entire suite added
1
#!/usr/bin/perl
2
# Copyright (C) 2000-2003 MySQL AB
3
#
4
# This library is free software; you can redistribute it and/or
5
# modify it under the terms of the GNU Library General Public
6
# License as published by the Free Software Foundation; version 2
7
# of the License.
8
#
9
# This library is distributed in the hope that it will be useful,
10
# but WITHOUT ANY WARRANTY; without even the implied warranty of
11
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
12
# Library General Public License for more details.
13
#
14
# You should have received a copy of the GNU Library General Public
15
# License along with this library; if not, write to the Free
16
# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
17
# MA 02111-1307, USA
18
#
19
# This test is for testing how long it takes to create tables,
20
# make a count(*) from them and finally drop the tables. These
21
# commands will be done in different ways in this test.
22
# Using option --fast will drop all the tables in the end
23
# of this test with one command instead of making own
24
# 'drop' command for each and every table.
25
# By changing the variable '$table_amount' value you can make
26
# this test a lot harder/easier for your computer to drive.
27
# Note that when using value bigger than 64 for this variable
28
# will do 'drop table'-command	in totally different way because of that
29
# how MySQL handles these commands.
30
31
##################### Standard benchmark inits ##############################
32
33
use Cwd;
34
use DBI;
35
use Benchmark;
36
37
$opt_loop_count=10000; # Change this to make test harder/easier
38
# This is the default value for the amount of tables used in this test.
39
40
$pwd = cwd(); $pwd = "." if ($pwd eq '');
41
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
42
43
$create_loop_count=$opt_loop_count;
44
if ($opt_small_test)
45
{
46
  $opt_loop_count/=100;
47
  $create_loop_count/=1000;
48
}
49
50
$max_tables=min($limits->{'max_tables'},$opt_loop_count);
51
52
if ($opt_small_test)
53
{
54
  $max_tables=10;
55
}
56
57
58
print "Testing the speed of creating and dropping tables\n";
59
print "Testing with $max_tables tables and $opt_loop_count loop count\n\n";
60
61
####
62
####  Connect and start timeing
63
####
64
65
$dbh = $server->connect();
66
67
### Test how the database can handle many tables
68
### Create $max_tables ; Access all off them with a simple query
69
### and then drop the tables
70
71
if ($opt_force) # If tables used in this test exist, drop 'em
72
{
73
  print "Okay..Let's make sure that our tables don't exist yet.\n\n";
74
  for ($i=1 ; $i <= $max_tables ; $i++)
75
  {
76
    $dbh->do("drop table bench_$i" . $server->{'drop_attr'});
77
  }
78
}
79
80
if ($opt_fast && defined($server->{vacuum}))
81
{
82
  $server->vacuum(1,\$dbh);
83
}
84
85
print "Testing create of tables\n";
86
87
$loop_time=$start_time=new Benchmark;
88
89
for ($i=1 ; $i <= $max_tables ; $i++)
90
{
91
    $dbh->do("drop table if exists bench_$i");
92
    if (do_many($dbh,$server->create("bench_$i",
93
				   ["i int NOT NULL",
94
				    "d double",
95
				    "f float",
96
				    "s char(10)",
97
				    "v varchar(100)"],
98
				   ["primary key (i)"])))
99
  {
100
    # Got an error; Do cleanup
101
    for ($i=1 ; $i <= $max_tables ; $i++)
102
    {
103
      $dbh->do("drop table bench_$i" . $server->{'drop_attr'});
104
    }
105
    die "Test aborted";
106
  }
107
}
108
109
$end_time=new Benchmark;
110
print "Time for create_MANY_tables ($max_tables): " .
111
  timestr(timediff($end_time, $loop_time),"all") . "\n\n";
112
113
if ($opt_fast && defined($server->{vacuum}))
114
{
115
  $server->vacuum(1,\$dbh);
116
}
117
118
#### Here comes $max_tables couples of cont(*) to the tables.
119
#### We'll check how long it will take...
120
####
121
122
print "Accessing tables\n";
123
124
if ($limits->{'group_functions'})
125
{
126
  $query="select count(*) from ";
127
  $type="select_group_when_MANY_tables";
128
}
129
else
130
{
131
  $query="select * from ";
132
  $type="select_when_MANY_tables";
133
}
134
135
$loop_time=new Benchmark;
136
for ($i=1 ; $i <= $max_tables ; $i++)
137
{
138
  $sth = $dbh->do("$query bench_$i") or die $DBI::errstr;
139
}
140
141
$end_time=new Benchmark;
142
print "Time to $type ($max_tables): " .
143
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
144
145
####
146
#### Now we are going to drop $max_tables tables;
147
####
148
149
print "Testing drop\n";
150
151
$loop_time=new Benchmark;
152
153
if ($opt_fast && $server->{'limits'}->{'multi_drop'} &&
154
    $server->{'limits'}->{'query_size'} > 11+$max_tables*10)
155
{
156
  my $query="drop table bench_1";
157
  for ($i=2 ; $i <= $max_tables ; $i++)
158
  {
159
    $query.=",bench_$i";
160
  }
161
  $sth = $dbh->do($query . $server->{'drop_attr'}) or die $DBI::errstr;
162
}
163
else
164
{
165
  for ($i=1 ; $i <= $max_tables ; $i++)
166
  {
167
    $sth = $dbh->do("drop table bench_$i" . $server->{'drop_attr'})
168
      or die $DBI::errstr;
169
  }
170
}
171
172
173
$end_time=new Benchmark;
174
print "Time for drop_table_when_MANY_tables ($max_tables): " .
175
    timestr(timediff($end_time, $loop_time),"all") . "\n\n";
176
177
if ($opt_fast && defined($server->{vacuum}))
178
{
179
  $server->vacuum(1,\$dbh);
180
}
181
182
#### We'll do first one 'create table' and then we'll drop it
183
#### away immediately. This loop shall be executed $opt_loop_count
184
#### times.
185
186
print "Testing create+drop\n";
187
188
$loop_time=new Benchmark;
189
190
for ($i=1 ; $i <= $create_loop_count ; $i++)
191
{
192
    $dbh->do("drop table if exists bench_$i");
193
    do_many($dbh,$server->create("bench_$i",
194
			       ["i int NOT NULL",
195
				"d double",
196
				"f float",
197
				"s char(10)",
198
				"v varchar(100)"],
199
			       ["primary key (i)"]));
200
  $sth = $dbh->do("drop table bench_$i" . $server->{'drop_attr'}) or die $DBI::errstr;
201
}
202
203
$end_time=new Benchmark;
204
print "Time for create+drop ($create_loop_count): " .
205
    timestr(timediff($end_time, $loop_time),"all") . "\n";
206
207
if ($opt_fast && defined($server->{vacuum}))
208
{
209
  $server->vacuum(1,\$dbh);
210
}
211
212
#
213
# Same test, but with a table with many keys
214
#
215
216
my @fields=(); my @keys=();
217
$keys=min($limits->{'max_index'},16);		# 16 is more than enough
218
$seg= min($limits->{'max_index_parts'},$keys,16);	# 16 is more than enough
219
220
# Make keys on the most important types
221
@types=(0,0,0,1,0,0,0,1,1,1,1,1,1,1,1,1,1);	# A 1 for each char field
222
push(@fields,"field1 integer not null");
223
push(@fields,"field2 integer not null");
224
push(@fields,"field3 integer not null");
225
push(@fields,"field4 char(16) not null");
226
push(@fields,"field5 integer not null");
227
push(@fields,"field6 float not null");
228
push(@fields,"field7 double not null");
229
for ($i=8 ; $i <= $keys ; $i++)
230
{
231
  push(@fields,"field$i char(5) not null");	# Should be relatively fair
232
}
233
234
# Let first key contain many segments
235
my $query="primary key (";
236
for ($i= 1 ; $i <= $seg ; $i++)
237
{
238
  $query.= "field$i,";
239
}
240
substr($query,-1)=")";
241
push (@keys,$query);
242
243
#Create other keys
244
for ($i=2 ; $i <= $keys ; $i++)
245
{
246
  push(@keys,"index index$i (field$i)");
247
}
248
249
$loop_time=new Benchmark;
250
for ($i=1 ; $i <= $opt_loop_count ; $i++) {
251
    $dbh->do("drop table if exists bench_$i");
252
    do_many($dbh,$server->create("bench_$i", \@fields, \@index));
253
    $dbh->do("drop table bench_$i" . $server->{'drop_attr'}) or die $DBI::errstr;
254
}
255
256
$end_time=new Benchmark;
257
print "Time for create_key+drop ($opt_loop_count): " .
258
    timestr(timediff($end_time, $loop_time),"all") . "\n";
259
260
if ($opt_fast && defined($server->{vacuum}))
261
{
262
  $server->vacuum(1,\$dbh);
263
}
264
265
####
266
#### End of benchmark
267
####
268
269
$dbh->disconnect;				# close connection
270
end_benchmark($start_time);