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