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-2001, 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 the speed of connections and sending
|
|
20 |
# data to the client.
|
|
21 |
#
|
|
22 |
# By changing the variable '$opt_loop_count' value you can make this test
|
|
23 |
# easier/harderto your computer to execute. You can also change this value
|
|
24 |
# by using option --loop_value='what_ever_you_like'.
|
|
25 |
##################### Standard benchmark inits ##############################
|
|
26 |
||
27 |
use Cwd; |
|
28 |
use DBI; |
|
29 |
use Benchmark; |
|
30 |
use Data::Dumper; |
|
31 |
||
32 |
# changed to 20k to get past issue with connect in drizzle for now
|
|
33 |
$opt_loop_count=20000; # Change this to make test harder/easier |
|
34 |
$str_length=65000; # This is the length of blob strings in PART:5 |
|
35 |
$max_test=20; # How many times to test if the server is busy |
|
36 |
||
37 |
$pwd = cwd(); $pwd = "." if ($pwd eq ''); |
|
38 |
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; |
|
39 |
||
40 |
# This is the length of blob strings in PART:5
|
|
41 |
$str_length=min($limits->{'max_text_size'},$limits->{'query_size'}-30,$str_length); |
|
42 |
||
43 |
if ($opt_small_test) |
|
44 |
{
|
|
45 |
$opt_loop_count/=100; |
|
46 |
}
|
|
47 |
||
48 |
$opt_loop_count=min(1000, $opt_loop_count) if ($opt_tcpip); |
|
49 |
$small_loop_count=$opt_loop_count/10; # For connect tests |
|
50 |
||
51 |
print "Testing the speed of connecting to the server and sending of data\n"; |
|
52 |
print "Connect tests are done $small_loop_count times and other tests $opt_loop_count times\n\n"; |
|
53 |
||
54 |
################################# PART:1 ###################################
|
|
55 |
####
|
|
56 |
#### Start timeing and start connect test..
|
|
57 |
####
|
|
58 |
||
59 |
$start_time=new Benchmark; |
|
60 |
||
61 |
print "Testing connection/disconnect\n"; |
|
62 |
||
63 |
$loop_time=new Benchmark; |
|
64 |
$errors=0; |
|
65 |
||
66 |
for ($i=0 ; $i < $small_loop_count ; $i++) |
|
67 |
{
|
|
68 |
print "$i " if (($opt_debug)); |
|
69 |
for ($j=0; $j < $max_test ; $j++) |
|
70 |
{
|
|
71 |
if ($dbh = DBI->connect($server->{'data_source'}, $opt_user, |
|
72 |
$opt_password)) |
|
73 |
{
|
|
74 |
$dbh->disconnect; |
|
75 |
last; |
|
76 |
}
|
|
77 |
select(undef, undef, undef, 0.01*$j); |
|
78 |
print "$errors " if (($opt_debug)); |
|
79 |
$errors++; |
|
80 |
}
|
|
81 |
die "Got error '$DBI::errstr' after $i connects" if ($j == $max_test); |
|
82 |
$dbh->disconnect; |
|
83 |
undef($dbh); |
|
84 |
}
|
|
85 |
$end_time=new Benchmark; |
|
86 |
print "Warning: $errors connections didn't work without a time delay\n" if ($errors); |
|
87 |
print "Time to connect ($small_loop_count): " . |
|
88 |
timestr(timediff($end_time, $loop_time),"all") . "\n\n"; |
|
89 |
||
90 |
################################# PART:2 ###################################
|
|
91 |
#### Now we shall do first one connect, then simple select
|
|
92 |
#### (select 1..) and then close connection. This will be
|
|
93 |
#### done $small_loop_count times.
|
|
94 |
||
95 |
if ($limits->{'select_without_from'}) |
|
96 |
{
|
|
97 |
print "Test connect/simple select/disconnect\n"; |
|
98 |
$loop_time=new Benchmark; |
|
99 |
||
100 |
for ($i=0; $i < $small_loop_count; $i++) |
|
101 |
{
|
|
102 |
$dbh = DBI->connect($server->{'data_source'}, $opt_user, $opt_password) || die $DBI::errstr; |
|
103 |
$sth = $dbh->do("select $i") or die $DBI::errstr; |
|
104 |
$dbh->disconnect; |
|
105 |
}
|
|
106 |
$end_time=new Benchmark; |
|
107 |
print "Time for connect+select_simple ($small_loop_count): " . |
|
108 |
timestr(timediff($end_time, $loop_time),"all") . "\n\n"; |
|
109 |
}
|
|
110 |
||
111 |
################################# PART:3 ###################################
|
|
112 |
####
|
|
113 |
#### Okay..Next thing we'll do is a simple select $opt_loop_count times.
|
|
114 |
####
|
|
115 |
||
116 |
$dbh = DBI->connect($server->{'data_source'}, $opt_user, $opt_password, |
|
117 |
{ PrintError => 0}) || die $DBI::errstr; |
|
118 |
||
119 |
if ($limits->{'select_without_from'}) |
|
120 |
{
|
|
121 |
print "Test simple select\n"; |
|
122 |
$loop_time=new Benchmark; |
|
123 |
for ($i=0 ; $i < $opt_loop_count ; $i++) |
|
124 |
{
|
|
125 |
$sth = $dbh->do("select $i") or die $DBI::errstr; |
|
126 |
}
|
|
127 |
$end_time=new Benchmark; |
|
128 |
print "Time for select_simple ($opt_loop_count): " . |
|
129 |
timestr(timediff($end_time, $loop_time),"all") . "\n\n"; |
|
130 |
}
|
|
131 |
||
132 |
###########################################################################
|
|
133 |
#### The same as the previous test, but always execute the same select
|
|
134 |
#### This is done to test the query cache for real simple selects.
|
|
135 |
||
136 |
if ($limits->{'select_without_from'}) |
|
137 |
{
|
|
138 |
print "Test simple select\n"; |
|
139 |
$loop_time=new Benchmark; |
|
140 |
for ($i=0 ; $i < $opt_loop_count ; $i++) |
|
141 |
{
|
|
142 |
$sth = $dbh->do("select 10000") or die $DBI::errstr; |
|
143 |
}
|
|
144 |
$end_time=new Benchmark; |
|
145 |
print "Time for select_simple_cache ($opt_loop_count): " . |
|
146 |
timestr(timediff($end_time, $loop_time),"all") . "\n\n"; |
|
147 |
}
|
|
148 |
||
149 |
##########################################################################
|
|
150 |
#### First, we'll create a simple table 'bench1'
|
|
151 |
#### Then we shall do $opt_loop_count selects from this table.
|
|
152 |
#### Table will contain very simple data.
|
|
153 |
||
154 |
$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}); |
|
155 |
do_many($dbh,$server->create("bench1", |
|
156 |
["a int NOT NULL", |
|
157 |
"i int", |
|
158 |
"s char(10)"], |
|
159 |
["primary key (a)"])); |
|
160 |
$sth = $dbh->do("insert into bench1 values(1,100,'AAA')") or die $DBI::errstr; |
|
161 |
||
162 |
if ($opt_fast && defined($server->{vacuum})) |
|
163 |
{
|
|
164 |
$server->vacuum(0,\$dbh); |
|
165 |
}
|
|
166 |
$dbh->disconnect; |
|
167 |
||
168 |
#
|
|
169 |
# First test connect/select/disconnect
|
|
170 |
#
|
|
171 |
print "Testing connect/select 1 row from table/disconnect\n"; |
|
172 |
||
173 |
$loop_time=new Benchmark; |
|
174 |
$errors=0; |
|
175 |
||
176 |
for ($i=0 ; $i < $small_loop_count ; $i++) |
|
177 |
{
|
|
178 |
for ($j=0; $j < $max_test ; $j++) |
|
179 |
{
|
|
180 |
last if ($dbh = DBI->connect($server->{'data_source'}, $opt_user, $opt_password)); |
|
181 |
$errors++; |
|
182 |
}
|
|
183 |
die $DBI::errstr if ($j == $max_test); |
|
184 |
||
185 |
$sth = $dbh->do("select a,i,s,$i from bench1") # Select * from table with 1 record |
|
186 |
or die $DBI::errstr; |
|
187 |
$dbh->disconnect; |
|
188 |
}
|
|
189 |
||
190 |
$end_time=new Benchmark; |
|
191 |
print "Warning: $errors connections didn't work without a time delay\n" if ($errors); |
|
192 |
print "Time to connect+select_1_row ($small_loop_count): " . |
|
193 |
timestr(timediff($end_time, $loop_time),"all") . "\n\n"; |
|
194 |
||
195 |
#
|
|
196 |
# The same test, but without connect/disconnect
|
|
197 |
#
|
|
198 |
print "Testing select 1 row from table\n"; |
|
199 |
||
200 |
$dbh = $server->connect(); |
|
201 |
$loop_time=new Benchmark; |
|
202 |
||
203 |
for ($i=0 ; $i < $opt_loop_count ; $i++) |
|
204 |
{
|
|
205 |
$sth = $dbh->do("select a,i,s,$i from bench1") # Select * from table with 1 record |
|
206 |
or die $DBI::errstr; |
|
207 |
}
|
|
208 |
||
209 |
$end_time=new Benchmark; |
|
210 |
print "Time to select_1_row ($opt_loop_count): " . |
|
211 |
timestr(timediff($end_time, $loop_time),"all") . "\n\n"; |
|
212 |
||
213 |
#
|
|
214 |
# Same test (as with one row) but now with a cacheable query
|
|
215 |
#
|
|
216 |
||
217 |
$loop_time=new Benchmark; |
|
218 |
||
219 |
for ($i=0 ; $i < $opt_loop_count ; $i++) |
|
220 |
{
|
|
221 |
$sth = $dbh->do("select a,i,s from bench1") # Select * from table with 1 record |
|
222 |
or die $DBI::errstr; |
|
223 |
}
|
|
224 |
$end_time=new Benchmark; |
|
225 |
print "Time to select_1_row_cache ($opt_loop_count): " . |
|
226 |
timestr(timediff($end_time, $loop_time),"all") . "\n\n"; |
|
227 |
||
228 |
#
|
|
229 |
# The same test, but with 2 rows (not cacheable).
|
|
230 |
#
|
|
231 |
||
232 |
print "Testing select 2 rows from table\n"; |
|
233 |
||
234 |
$sth = $dbh->do("insert into bench1 values(2,200,'BBB')") |
|
235 |
or die $DBI::errstr; |
|
236 |
||
237 |
$loop_time=new Benchmark; |
|
238 |
||
239 |
for ($i=0 ; $i < $opt_loop_count ; $i++) |
|
240 |
{
|
|
241 |
$sth = $dbh->do("select a,i,s,$i from bench1") # Select * from table with 2 record |
|
242 |
or die $DBI::errstr; |
|
243 |
}
|
|
244 |
||
245 |
$end_time=new Benchmark; |
|
246 |
print "Time to select_2_rows ($opt_loop_count): " . |
|
247 |
timestr(timediff($end_time, $loop_time),"all") . "\n\n"; |
|
248 |
||
249 |
#
|
|
250 |
# Simple test to test speed of functions.
|
|
251 |
#
|
|
252 |
||
253 |
if ($limits->{'functions'}) |
|
254 |
{
|
|
255 |
print "Test select with aritmetic (+)\n"; |
|
256 |
$loop_time=new Benchmark; |
|
257 |
||
258 |
for ($i=0; $i < $opt_loop_count; $i++) |
|
259 |
{
|
|
260 |
$sth = $dbh->do("select a+a+a+a+a+a+a+a+a+$i from bench1") or die $DBI::errstr; |
|
261 |
}
|
|
262 |
$end_time=new Benchmark; |
|
263 |
print "Time for select_column+column ($opt_loop_count): " . |
|
264 |
timestr(timediff($end_time, $loop_time),"all") . "\n\n"; |
|
265 |
}
|
|
266 |
||
267 |
$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) |
|
268 |
or die $DBI::errstr; |
|
269 |
||
270 |
if ($opt_fast && defined($server->{vacuum})) |
|
271 |
{
|
|
272 |
$server->vacuum(0,\$dbh); |
|
273 |
}
|
|
274 |
||
275 |
################################# PART:5 ###################################
|
|
276 |
#### We'll create one table with a single blob field,but with a
|
|
277 |
#### huge record in it and then we'll do $opt_loop_count selects
|
|
278 |
#### from it.
|
|
279 |
||
280 |
goto skip_blob_test if (!$limits->{'working_blobs'}); |
|
281 |
||
282 |
print "Testing retrieval of big records ($str_length bytes)\n"; |
|
283 |
||
284 |
do_many($dbh,$server->create("bench1", ["b blob"], [])); |
|
285 |
$dbh->{LongReadLen}= $str_length; # Set retrieval buffer |
|
286 |
||
287 |
my $string=(A) x ($str_length); # This will make a string $str_length long. |
|
288 |
$sth = $dbh->prepare("insert into bench1 values(?)") or die $dbh->errstr; |
|
289 |
$sth->execute($string) or die $sth->errstr; |
|
290 |
undef($string); |
|
291 |
if ($opt_fast && defined($server->{vacuum})) |
|
292 |
{
|
|
293 |
$server->vacuum(0,\$dbh); |
|
294 |
}
|
|
295 |
||
296 |
$loop_time=new Benchmark; |
|
297 |
||
298 |
for ($i=0 ; $i < $small_loop_count ; $i++) |
|
299 |
{
|
|
300 |
$sth = $dbh->prepare("select b,$i from bench1"); |
|
301 |
if (!$sth->execute || !(@row = $sth->fetchrow_array) || |
|
302 |
length($row[0]) != $str_length) |
|
303 |
{
|
|
304 |
warn "$DBI::errstr - ".length($row[0])." - $str_length **\n"; |
|
305 |
}
|
|
306 |
$sth->finish; |
|
307 |
}
|
|
308 |
||
309 |
$end_time=new Benchmark; |
|
310 |
print "Time to select_big_str ($small_loop_count): " . |
|
311 |
timestr(timediff($end_time, $loop_time),"all") . "\n\n"; |
|
312 |
||
313 |
$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) |
|
314 |
or do |
|
315 |
{
|
|
316 |
# Fix for Access 2000
|
|
317 |
die $dbh->errstr if (!$server->abort_if_fatal_error()); |
|
318 |
};
|
|
319 |
||
320 |
if ($opt_fast && defined($server->{vacuum})) |
|
321 |
{
|
|
322 |
$server->vacuum(0,\$dbh); |
|
323 |
}
|
|
324 |
||
325 |
skip_blob_test:
|
|
326 |
||
327 |
################################ END ###################################
|
|
328 |
####
|
|
329 |
#### End of the test...Finally print time used to execute the
|
|
330 |
#### whole test.
|
|
331 |
||
332 |
$dbh->disconnect; |
|
333 |
end_benchmark($start_time); |