2
# Copyright (c) 2000-2003, 2006, 2007 MySQL AB, 2009 Sun Microsystems, Inc.
3
# Use is subject to license terms.
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
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.
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,
20
# Test of creating a simple table and inserting $record_count records in it,
21
# $opt_loop_count rows in order, $opt_loop_count rows in reverse order and
22
# $opt_loop_count rows in random order
24
# changes made for Oracle compatibility
25
# - $limits->{'func_odbc_mod'} is OK from crash-me, but it fails here so set we
26
# set it to 0 in server-cfg
27
# - the default server config runs out of rollback segments, so we added a
28
# couple of disconnect/connects to reset
30
##################### Standard benchmark inits ##############################
37
$opt_loop_count=100000; # number of rows/3
38
$small_loop_count=10; # Loop for full table retrieval
39
$range_loop_count=$small_loop_count*50;
40
$many_keys_loop_count=$opt_loop_count;
41
$opt_read_key_loop_count=$opt_loop_count;
43
$pwd = cwd(); $pwd = "." if ($pwd eq '');
44
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
49
$many_keys_loop_count=$opt_loop_count/10;
51
$opt_read_key_loop_count=10;
53
elsif ($opt_small_tables)
55
$opt_loop_count=10000; # number of rows/3
56
$many_keys_loop_count=$opt_loop_count;
57
$opt_read_key_loop_count=10;
59
elsif ($opt_small_key_tables)
61
$many_keys_loop_count/=10;
64
if ($opt_loop_count < 100)
66
$opt_loop_count=100; # Some tests must have some data to work!
68
$range_loop_count=min($opt_loop_count,$range_loop_count);
71
print "Testing the speed of inserting data into 1 table and do some selects on it.\n";
72
print "The tests are done with a table that has $opt_loop_count rows.\n\n";
75
#### Generating random keys
78
print "Generating random keys\n";
79
$random[$opt_loop_count]=0;
80
for ($i=0 ; $i < $opt_loop_count ; $i++)
82
$random[$i]=$i+$opt_loop_count;
86
for ($i=0 ; $i < $opt_loop_count ; $i++)
88
$tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
89
$swap=$tmpvar % $opt_loop_count;
90
$tmp=$random[$i]; $random[$i]=$random[$swap]; $random[$swap]=$tmp;
93
$total_rows=$opt_loop_count*3;
96
#### Connect and start timeing
98
$start_time=new Benchmark;
99
$dbh = $server->connect();
101
#### Create needed tables
104
goto keys_test if ($opt_stage == 2);
105
goto select_test if ($opt_skip_create);
107
print "Creating tables\n";
108
$dbh->do("drop table bench1" . $server->{'drop_attr'});
109
$dbh->do("drop table bench2" . $server->{'drop_attr'});
110
$dbh->do("drop table bench3" . $server->{'drop_attr'});
111
do_many($dbh,$server->create("bench1",
116
["primary key (id,id2)",
117
"index ix_id3 (id3)"]));
119
if ($opt_lock_tables)
121
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
125
#### Insert $total_rows records in order, in reverse order and random.
128
$loop_time=new Benchmark;
130
if ($opt_fast_insert)
132
$query="insert into bench1 values ";
136
$query="insert into bench1 (id,id2,id3,dummy1) values ";
139
if ($opt_fast && $server->{transactions})
141
$dbh->{AutoCommit} = 0;
142
print "Transactions enabled\n" if ($opt_debug);
145
if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value'})
147
$query_size=$server->{'limits'}->{'query_size'};
149
print "Inserting $opt_loop_count multiple-value rows in order\n";
151
for ($i=0 ; $i < $opt_loop_count ; $i++)
153
$tmp= "($i,$i,$i,'ABCDEFGHIJ'),";
154
if (length($tmp)+length($res) < $query_size)
160
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
164
print "Inserting $opt_loop_count multiple-value rows in reverse order\n";
165
for ($i=0 ; $i < $opt_loop_count ; $i++)
167
$tmp= "(" . ($total_rows-1-$i) . "," .($total_rows-1-$i) .
168
"," .($total_rows-1-$i) . ",'BCDEFGHIJK'),";
169
if (length($tmp)+length($res) < $query_size)
175
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
179
print "Inserting $opt_loop_count multiple-value rows in random order\n";
180
for ($i=0 ; $i < $opt_loop_count ; $i++)
182
$tmp= "(" . $random[$i] . "," . $random[$i] . "," . $random[$i] .
183
",'CDEFGHIJKL')," or die $DBI::errstr;
184
if (length($tmp)+length($res) < $query_size)
190
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
194
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
198
print "Inserting $opt_loop_count rows in order\n";
199
for ($i=0 ; $i < $opt_loop_count ; $i++)
201
$sth = $dbh->do($query . "($i,$i,$i,'ABCDEFGHIJ')") or die $DBI::errstr;
204
print "Inserting $opt_loop_count rows in reverse order\n";
205
for ($i=0 ; $i < $opt_loop_count ; $i++)
207
$sth = $dbh->do($query . "(" . ($total_rows-1-$i) . "," .
208
($total_rows-1-$i) . "," .
209
($total_rows-1-$i) . ",'BCDEFGHIJK')")
213
print "Inserting $opt_loop_count rows in random order\n";
215
for ($i=0 ; $i < $opt_loop_count ; $i++)
217
$sth = $dbh->do($query . "(". $random[$i] . "," . $random[$i] .
218
"," . $random[$i] . ",'CDEFGHIJKL')") or die $DBI::errstr;
222
if ($opt_fast && $server->{transactions})
225
$dbh->{AutoCommit} = 1;
228
$end_time=new Benchmark;
229
print "Time for insert (" . ($total_rows) . "): " .
230
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
232
if ($opt_lock_tables)
234
$sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
236
if ($opt_fast && defined($server->{vacuum}))
238
$server->vacuum(1,\$dbh,"bench1");
240
if ($opt_lock_tables)
242
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
246
#### insert $opt_loop_count records with duplicate id
249
if ($limits->{'unique_index'})
251
print "Testing insert of duplicates\n";
252
$loop_time=new Benchmark;
254
if ($opt_fast && $server->{transactions})
256
$dbh->{AutoCommit} = 0;
259
for ($i=0 ; $i < $opt_loop_count ; $i++)
261
$tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
262
$tmp=$tmpvar % ($total_rows);
263
$tmpquery = "$query ($tmp,$tmp,2,'D')";
264
if ($dbh->do($tmpquery))
266
die "Didn't get an error when inserting duplicate record $tmp\n";
269
if ($opt_fast && $server->{transactions})
272
$dbh->{AutoCommit} = 1;
275
$end_time=new Benchmark;
276
print "Time for insert_duplicates (" . ($opt_loop_count) . "): " .
277
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
282
#### Do some selects on the table
287
# ----------------- prepared+executed/prepared*executed tests
289
print "Test of prepared+execute/once prepared many execute selects\n";
290
$loop_time=new Benchmark;
292
for ($i=1 ; $i <= $opt_loop_count ; $i++)
294
my ($key_value)=$random[$i];
295
my ($query)= "select * from bench1 where id=$key_value";
296
print "$query\n" if ($opt_debug);
297
$sth = $dbh->prepare($query);
300
die "error in prepare select with id = $key_value : $DBI::errstr";
304
die "cannot execute prepare select with id = $key_value : $DBI::errstr";
306
while ($sth->fetchrow_arrayref) { };
309
$end_time=new Benchmark;
310
print "Time for prepared_select ($opt_loop_count): " .
311
timestr(timediff($end_time, $loop_time),"all") . "\n";
313
$loop_time=new Benchmark;
314
$query= "select * from bench1 where id=?";
315
$sth = $dbh->prepare($query);
318
die "cannot prepare select: $DBI::errstr";
321
for ($i=1 ; $i <= $opt_loop_count ; $i++)
323
my ($key_value)=$random[$i];
324
$sth->bind_param(1,$key_value);
325
print "$query , id = $key_value\n" if ($opt_debug);
328
die "cannot execute prepare select with id = $key_value : $DBI::errstr";
330
while ($sth->fetchrow_arrayref) { };
333
$end_time=new Benchmark;
334
print "Time for once_prepared_select ($opt_loop_count): " .
335
timestr(timediff($end_time, $loop_time),"all") . "\n";
338
print "Retrieving data from the table\n";
339
$loop_time=new Benchmark;
342
# It's really a small table, so we can try a select on everything
345
for ($i=1 ; $i <= $small_loop_count ; $i++)
347
if (($found_rows=fetch_all_rows($dbh,"select id from bench1")) !=
352
print "Warning: Got $found_rows rows when selecting a whole table of " . ($total_rows) . " rows\nContact the database or DBD author!\n";
358
$end_time=new Benchmark;
359
print "Time for select_big ($small_loop_count:$count): " .
360
timestr(timediff($end_time, $loop_time),"all") . "\n";
363
# Do a lot of different ORDER BY queries
366
$loop_time=new Benchmark;
368
for ($i=1 ; $i <= $small_loop_count ; $i++)
370
$rows+=fetch_all_rows($dbh,"select id,id2 from bench1 order by id,id2",1);
371
$end_time=new Benchmark;
372
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
376
{ print "Estimated time"; }
379
print " for order_by_big_key ($small_loop_count:$rows): " .
380
timestr(timediff($end_time, $loop_time),"all") . "\n";
383
$loop_time=new Benchmark;
385
for ($i=1 ; $i <= $small_loop_count ; $i++)
387
$rows+=fetch_all_rows($dbh,"select id,id2 from bench1 order by id desc, id2 desc",1);
388
$end_time=new Benchmark;
389
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
393
{ print "Estimated time"; }
396
print " for order_by_big_key_desc ($small_loop_count:$rows): " .
397
timestr(timediff($end_time, $loop_time),"all") . "\n";
400
$loop_time=new Benchmark;
402
for ($i=1 ; $i <= $small_loop_count ; $i++)
404
$rows+=fetch_all_rows($dbh,"select id from bench1 order by id desc",1);
405
$end_time=new Benchmark;
406
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
410
{ print "Estimated time"; }
413
print " for order_by_big_key_prefix ($small_loop_count:$rows): " .
414
timestr(timediff($end_time, $loop_time),"all") . "\n";
417
$loop_time=new Benchmark;
419
for ($i=1 ; $i <= $small_loop_count ; $i++)
421
$rows+=fetch_all_rows($dbh,"select id3 from bench1 order by id3",1);
422
$end_time=new Benchmark;
423
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
427
{ print "Estimated time"; }
430
print " for order_by_big_key2 ($small_loop_count:$rows): " .
431
timestr(timediff($end_time, $loop_time),"all") . "\n";
434
$sel=$limits->{'order_by_unused'} ? "id2" : "*";
435
$loop_time=new Benchmark;
437
for ($i=1 ; $i <= $small_loop_count ; $i++)
439
$rows+=fetch_all_rows($dbh,"select $sel from bench1 order by id3",1);
440
$end_time=new Benchmark;
441
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
445
{ print "Estimated time"; }
448
print " for order_by_big_key_diff ($small_loop_count:$rows): " .
449
timestr(timediff($end_time, $loop_time),"all") . "\n";
452
$sel=$limits->{'order_by_unused'} ? "id" : "*";
453
$loop_time=new Benchmark;
455
for ($i=1 ; $i <= $small_loop_count ; $i++)
457
$rows+=fetch_all_rows($dbh,"select $sel from bench1 order by id2,id3",1);
458
$end_time=new Benchmark;
459
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
463
{ print "Estimated time"; }
466
print " for order_by_big ($small_loop_count:$rows): " .
467
timestr(timediff($end_time, $loop_time),"all") . "\n";
470
$sel=$limits->{'order_by_unused'} ? "dummy1" : "dummy1,id3";
471
$loop_time=new Benchmark;
473
for ($i=1 ; $i <= $range_loop_count ; $i++)
475
$start=$opt_loop_count/$range_loop_count*$i;
477
$rows+=fetch_all_rows($dbh,"select $sel from bench1 where id>=$start and id <= $end order by id3",1);
478
$end_time=new Benchmark;
479
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
483
{ print "Estimated time"; }
486
print " for order_by_range ($range_loop_count:$rows): " .
487
timestr(timediff($end_time, $loop_time),"all") . "\n";
489
$sel=$limits->{'order_by_unused'} ? "dummy1" : "dummy1,id";
490
$loop_time=new Benchmark;
492
for ($i=1 ; $i <= $range_loop_count ; $i++)
494
$start=$opt_loop_count/$range_loop_count*$i;
496
$rows+=fetch_all_rows($dbh,"select $sel from bench1 where id>=$start and id <= $end order by id",1);
497
$end_time=new Benchmark;
498
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
502
{ print "Estimated time"; }
505
print " for order_by_key_prefix ($range_loop_count:$rows): " .
506
timestr(timediff($end_time, $loop_time),"all") . "\n";
508
$sel=$limits->{'order_by_unused'} ? "id2" : "id2,id3";
509
$loop_time=new Benchmark;
511
for ($i=1 ; $i <= $range_loop_count ; $i++)
513
$start=$opt_loop_count/$range_loop_count*$i;
514
$end=$start+$range_loop_count;
515
$rows+=fetch_all_rows($dbh,"select $sel from bench1 where id3>=$start and id3 <= $end order by id3",1);
516
$end_time=new Benchmark;
517
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
521
{ print "Estimated time"; }
524
print " for order_by_key2_diff ($range_loop_count:$rows): " .
525
timestr(timediff($end_time, $loop_time),"all") . "\n";
528
# Test of select on 2 different keys with or
529
# (In this case database can only use keys if they do an automatic union).
532
$loop_time=new Benchmark;
536
for ($i=1 ; $i <= $range_loop_count ; $i++)
539
my $rnd2=$random[$i];
540
$rows+=fetch_all_rows($dbh,"select id2 from bench1 where id=$rnd or id3=$rnd2",1);
542
$end_time=new Benchmark;
543
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count,
547
{ print "Estimated time"; }
550
print " for select_diff_key ($count:$rows): " .
551
timestr(timediff($end_time, $loop_time),"all") . "\n";
554
# Test select that is very popular when using ODBC
556
check_or_range("id","select_range_prefix");
557
check_or_range("id3","select_range_key2");
559
# Check reading on direct key on id and id3
561
check_select_key("*","id","select_key_prefix");
562
check_select_key2("*","id","id2","select_key");
563
check_select_key2("id,id2","id","id2","select_key_return_key");
564
check_select_key("*","id3","select_key2");
565
check_select_key("id3","id3","select_key2_return_key");
566
check_select_key("id,id2","id3","select_key2_return_prim");
569
#### A lot of simple selects on ranges
572
@Q=("select * from bench1 where !id!=3 or !id!=2 or !id!=1 or !id!=4 or !id!=16 or !id!=10",
574
"select * from bench1 where !id!>=" . ($total_rows-1) ." or !id!<1",
576
"select * from bench1 where !id!>=1 and !id!<=2",
578
"select * from bench1 where (!id!>=1 and !id!<=2) or (!id!>=1 and !id!<=2)",
580
"select * from bench1 where !id!>=1 and !id!<=10 and !id!<=5",
582
"select * from bench1 where (!id!>0 and !id!<2) or !id!>=" . ($total_rows-1),
584
"select * from bench1 where (!id!>0 and !id!<2) or (!id!>= " . ($opt_loop_count/2) . " and !id! <= " . ($opt_loop_count/2+2) . ") or !id! = " . ($opt_loop_count/2-1),
586
"select * from bench1 where (!id!>=5 and !id!<=10) or (!id!>=1 and !id!<=4)",
588
"select * from bench1 where (!id!=1 or !id!=2) and (!id!=3 or !id!=4)",
590
"select * from bench1 where (!id!=1 or !id!=2) and (!id!=2 or !id!=3)",
592
"select * from bench1 where (!id!=1 or !id!=5 or !id!=20 or !id!=40) and (!id!=1 or !id!>=20 or !id!=4)",
594
"select * from bench1 where ((!id!=1 or !id!=3) or (!id!>1 and !id!<3)) and !id!<=2",
596
"select * from bench1 where (!id! >= 0 and !id! < 4) or (!id! >=4 and !id! < 6)",
598
"select * from bench1 where !id! <= -1 or (!id! >= 0 and !id! <= 5) or (!id! >=4 and !id! < 6) or (!id! >=6 and !id! <=7) or (!id!>7 and !id! <= 8)",
600
"select * from bench1 where (!id!>=1 and !id!<=2 or !id!>=4 and !id!<=5) or (!id!>=0 and !id! <=10)",
602
"select * from bench1 where (!id!>=1 and !id!<=2 or !id!>=4 and !id!<=5) or (!id!>2 and !id! <=10)",
604
"select * from bench1 where (!id!>1 or !id! <1) and !id!<=2",
606
"select * from bench1 where !id! <= 2 and (!id!>1 or !id! <=1)",
608
"select * from bench1 where (!id!>=1 or !id! <1) and !id!<=2",
610
"select * from bench1 where (!id!>=1 or !id! <=2) and !id!<=2",
614
print "\nTest of compares with simple ranges\n";
615
check_select_range("id","select_range_prefix");
616
check_select_range("id3","select_range_key2");
619
#### Some group queries
622
if ($limits->{'group_functions'})
624
$loop_time=new Benchmark;
628
for ($tests=0 ; $tests < $small_loop_count ; $tests++)
630
$sth=$dbh->prepare($query="select count(*) from bench1") or die $DBI::errstr;
631
$sth->execute or die $sth->errstr;
632
if (($sth->fetchrow_array)[0] != $total_rows)
634
print "Warning: '$query' returned wrong result\n";
638
# min, max in keys are very normal
640
fetch_all_rows($dbh,"select min(id) from bench1");
641
fetch_all_rows($dbh,"select max(id) from bench1");
642
fetch_all_rows($dbh,"select sum(id+0.0) from bench1");
643
fetch_all_rows($dbh,"select min(id3),max(id3),sum(id3-0.0) from bench1");
644
if ($limits->{'group_func_sql_min_str'})
646
fetch_all_rows($dbh,"select min(dummy1),max(dummy1) from bench1");
649
$sth=$dbh->prepare($query="select count(*) from bench1 where id >= " .
650
($opt_loop_count*2)) or die $DBI::errstr;
651
$sth->execute or die $DBI::errstr;
652
if (($sth->fetchrow_array)[0] != $opt_loop_count)
654
print "Warning: '$query' returned wrong result\n";
659
$sth=$dbh->prepare($query="select count(*),sum(id+0.0),min(id),max(id),avg(id-0.0) from bench1") or die $DBI::errstr;
660
$sth->execute or die $DBI::errstr;
661
@row=$sth->fetchrow_array;
662
if ($row[0] != $total_rows ||
663
int($row[1]+0.5) != int((($total_rows-1)/2*$total_rows)+0.5) ||
665
$row[3] != $total_rows-1 ||
666
1-$row[4]/(($total_rows-1)/2) > 0.001)
668
# PostgreSQL 6.3 fails here
669
print "Warning: '$query' returned wrong result: @row\n";
673
if ($limits->{'func_odbc_mod'})
676
if ($limits->{'func_extra_%'})
678
$tmp="id % 10"; # For postgreSQL
681
if ($limits->{'group_by_alias'}) {
682
if (fetch_all_rows($dbh,$query=$server->query("select $tmp as last_digit,count(*) from bench1 group by last_digit")) != 10)
684
print "Warning: '$query' returned wrong number of rows\n";
686
} elsif ($limits->{'group_by_position'}) {
687
if (fetch_all_rows($dbh,$query=$server->query("select $tmp,count(*) from bench1 group by 1")) != 10)
689
print "Warning: '$query' returned wrong number of rows\n";
694
if ($limits->{'order_by_position'} && $limits->{'group_by_position'})
697
if (fetch_all_rows($dbh, $query="select id,id3,dummy1 from bench1 where id < 100+$count-$count group by id,id3,dummy1 order by id desc,id3,dummy1") != 100)
699
print "Warning: '$query' returned wrong number of rows\n";
702
$end_time=new Benchmark;
703
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
706
print_time($estimated);
707
print " for select_group ($count): " .
708
timestr(timediff($end_time, $loop_time),"all") . "\n";
710
$loop_time=new Benchmark;
712
for ($tests=1 ; $tests <= $range_loop_count*5 ; $tests++)
715
fetch_all_rows($dbh,"select min(id) from bench1");
716
fetch_all_rows($dbh,"select max(id) from bench1");
717
fetch_all_rows($dbh,"select min(id2) from bench1 where id=$tests");
718
fetch_all_rows($dbh,"select max(id2) from bench1 where id=$tests");
719
if ($limits->{'group_func_sql_min_str'})
721
fetch_all_rows($dbh,"select min(dummy1) from bench1 where id=$tests");
722
fetch_all_rows($dbh,"select max(dummy1) from bench1 where id=$tests");
724
$end_time=new Benchmark;
725
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
726
$range_loop_count*5));
729
{ print "Estimated time"; }
732
print " for min_max_on_key ($count): " .
733
timestr(timediff($end_time, $loop_time),"all") . "\n";
735
$loop_time=new Benchmark;
737
for ($tests=1 ; $tests <= $small_loop_count ; $tests++)
740
fetch_all_rows($dbh,"select min(id2) from bench1");
741
fetch_all_rows($dbh,"select max(id2) from bench1");
742
fetch_all_rows($dbh,"select min(id3) from bench1 where id2=$tests");
743
fetch_all_rows($dbh,"select max(id3) from bench1 where id2=$tests");
744
if ($limits->{'group_func_sql_min_str'})
746
fetch_all_rows($dbh,"select min(dummy1) from bench1 where id2=$tests");
747
fetch_all_rows($dbh,"select max(dummy1) from bench1 where id2=$tests");
749
$end_time=new Benchmark;
750
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
754
{ print "Estimated time"; }
757
print " for min_max ($count): " .
758
timestr(timediff($end_time, $loop_time),"all") . "\n";
760
$loop_time=new Benchmark;
762
$total=$opt_loop_count*3;
763
for ($tests=0 ; $tests < $total ; $tests+=$total/100)
766
fetch_all_rows($dbh,"select count(id) from bench1 where id < $tests");
768
$end_time=new Benchmark;
769
print "Time for count_on_key ($count): " .
770
timestr(timediff($end_time, $loop_time),"all") . "\n";
772
$loop_time=new Benchmark;
774
for ($tests=0 ; $tests < $total ; $tests+=$total/100)
777
fetch_all_rows($dbh,"select count(dummy1) from bench1 where id2 < $tests");
779
$end_time=new Benchmark;
780
print "Time for count ($count): " .
781
timestr(timediff($end_time, $loop_time),"all") . "\n";
783
if ($limits->{'group_distinct_functions'})
785
$loop_time=new Benchmark;
787
for ($tests=1 ; $tests <= $small_loop_count ; $tests++)
790
fetch_all_rows($dbh,"select count(distinct dummy1) from bench1");
791
fetch_all_rows($dbh,"select dummy1,count(distinct id) from bench1 group by dummy1");
792
$end_time=new Benchmark;
793
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
797
{ print "Estimated time"; }
800
print " for count_distinct_big ($count): " .
801
timestr(timediff($end_time, $loop_time),"all") . "\n";
806
if ($server->small_rollback_segment())
808
$dbh->disconnect; # close connection
809
$dbh = $server->connect();
813
#### Some updates on the table
816
$loop_time=new Benchmark;
818
if ($limits->{'functions'})
820
print "\nTesting update of keys with functions\n";
821
my $update_loop_count=$opt_loop_count/2;
822
for ($i=0 ; $i < $update_loop_count ; $i++)
824
my $tmp=$opt_loop_count+$random[$i]; # $opt_loop_count*2 <= $tmp < $total_rows
825
$sth = $dbh->do("update bench1 set id3=-$tmp where id3=$tmp") or die $DBI::errstr;
828
$end_time=new Benchmark;
829
print "Time for update_of_key ($update_loop_count): " .
830
timestr(timediff($end_time, $loop_time),"all") . "\n";
832
if ($opt_lock_tables)
834
do_query($dbh,"UNLOCK TABLES");
836
if ($opt_fast && defined($server->{vacuum}))
838
$server->vacuum(1,\$dbh,"bench1");
840
if ($opt_lock_tables)
842
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
845
if ($server->small_rollback_segment())
847
$dbh->disconnect; # close connection
848
$dbh = $server->connect();
851
$loop_time=new Benchmark;
853
$step=int($opt_loop_count/$range_loop_count+1);
854
for ($i= 0 ; $i < $opt_loop_count ; $i+= $step)
857
$sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= 0 and id3 <= $i") or die $DBI::errstr;
860
if ($server->small_rollback_segment())
862
$dbh->disconnect; # close connection
863
$dbh = $server->connect();
866
$sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= 0 and id3 < $opt_loop_count") or die $DBI::errstr;
868
if ($server->small_rollback_segment())
870
$dbh->disconnect; # close connection
871
$dbh = $server->connect();
874
$sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= $opt_loop_count and id3 < ". ($opt_loop_count*2)) or die $DBI::errstr;
877
# Check that everything was updated
878
# In principle we shouldn't time this in the update loop..
881
if ($server->small_rollback_segment())
883
$dbh->disconnect; # close connection
884
$dbh = $server->connect();
887
if (($sth=$dbh->prepare("select count(*) from bench1 where id3>=0"))
890
($row_count)=$sth->fetchrow;
892
$result=1 + $opt_loop_count-$update_loop_count;
893
if ($row_count != $result)
895
print "Warning: Update check returned $row_count instead of $result\n";
899
if ($server->small_rollback_segment())
901
$dbh->disconnect; # close connection
902
$dbh = $server->connect();
904
#restore id3 to 0 <= id3 < $total_rows/10 or 0<= id3 < $total_rows
906
my $func=($limits->{'func_odbc_floor'}) ? "floor((0-id3)/20)" : "0-id3";
908
$sth=$dbh->do($query="update bench1 set id3=$func where id3<0") or die $DBI::errstr;
910
$end_time=new Benchmark;
911
print "Time for update_of_key_big ($count): " .
912
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
916
print "\nTesting update of keys in loops\n";
918
# This is for mSQL that doesn't have functions. Do we really need this ????
921
$sth=$dbh->prepare("select id3 from bench1 where id3 >= 0") or die $DBI::errstr;
922
$sth->execute or die $DBI::errstr;
924
while (@tmp = $sth->fetchrow_array)
926
my $tmp1 = "-$tmp[0]";
927
my $sth1 = $dbh->do("update bench1 set id3 = $tmp1 where id3 = $tmp[0]");
929
$end_time=new Benchmark;
930
if (($end_time->[0] - $loop_time->[0]) > $opt_time_limit)
932
print "note: Aborting update loop because of timeout\n";
937
# Check that everything except id3=0 was updated
938
# In principle we shouldn't time this in the update loop..
940
if (fetch_all_rows($dbh,$query="select * from bench1 where id3>=0") != 1)
942
if ($count == $total_rows)
944
print "Warning: Wrong information after update: Found '$row_count' rows, but should have been: 1\n";
947
#restore id3 to 0 <= id3 < $total_rows
948
$sth=$dbh->prepare("select id3 from bench1 where id3 < 0") or die $DBI::errstr;
949
$sth->execute or die $DBI::errstr;
950
while (@tmp = $sth->fetchrow_array)
953
my $tmp1 = floor((0-$tmp[0])/10);
954
my $sth1 = $dbh->do("update bench1 set id3 = $tmp1 where id3 = $tmp[0]");
957
$end_time=new Benchmark;
958
$estimated=predict_query_time($loop_time,$end_time,\$count,$count,
961
{ print "Estimated time"; }
964
print " for update_of_key ($count): " .
965
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
968
if ($opt_fast && defined($server->{vacuum}))
970
if ($opt_lock_tables)
972
do_query($dbh,"UNLOCK TABLES");
974
$server->vacuum(1,\$dbh,"bench1");
975
if ($opt_lock_tables)
977
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
982
# Testing some simple updates
985
print "Testing update with key\n";
986
$loop_time=new Benchmark;
987
for ($i=0 ; $i < $opt_loop_count*3 ; $i++)
989
$sth = $dbh->do("update bench1 set dummy1='updated' where id=$i and id2=$i") or die $DBI::errstr;
992
$end_time=new Benchmark;
993
print "Time for update_with_key (" . ($opt_loop_count*3) . "): " .
994
timestr(timediff($end_time, $loop_time),"all") . "\n";
996
$loop_time=new Benchmark;
998
for ($i=1 ; $i < $opt_loop_count*3 ; $i+=3)
1000
$sth = $dbh->do("update bench1 set dummy1='updated' where id=$i") or die $DBI::errstr;
1001
$end_time=new Benchmark;
1002
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,($i-1)/3,
1006
{ print "Estimated time"; }
1009
print " for update_with_key_prefix (" . ($opt_loop_count) . "): " .
1010
timestr(timediff($end_time, $loop_time),"all") . "\n";
1012
print "\nTesting update of all rows\n";
1013
$loop_time=new Benchmark;
1014
for ($i=0 ; $i < $small_loop_count ; $i++)
1016
$sth = $dbh->do("update bench1 set dummy1='updated $i'") or die $DBI::errstr;
1018
$end_time=new Benchmark;
1019
print "Time for update_big ($small_loop_count): " .
1020
timestr(timediff($end_time, $loop_time),"all") . "\n";
1024
# Testing left outer join
1027
if ($limits->{'func_odbc_floor'} && $limits->{'left_outer_join'})
1029
if ($opt_lock_tables)
1031
$sth = $dbh->do("LOCK TABLES bench1 a READ, bench1 b READ") || die $DBI::errstr;
1033
print "\nTesting left outer join\n";
1034
$loop_time=new Benchmark;
1036
for ($i=0 ; $i < $small_loop_count ; $i++)
1038
$count+=fetch_all_rows($dbh,"select count(*) from bench1 as a left outer join bench1 as b on (a.id2=b.id3)");
1040
$end_time=new Benchmark;
1041
print "Time for outer_join_on_key ($small_loop_count:$count): " .
1042
timestr(timediff($end_time, $loop_time),"all") . "\n";
1044
$loop_time=new Benchmark;
1046
for ($i=0 ; $i < $small_loop_count ; $i++)
1048
$count+=fetch_all_rows($dbh,"select count(a.dummy1),count(b.dummy1) from bench1 as a left outer join bench1 as b on (a.id2=b.id3)");
1050
$end_time=new Benchmark;
1051
print "Time for outer_join ($small_loop_count:$count): " .
1052
timestr(timediff($end_time, $loop_time),"all") . "\n";
1055
$loop_time=new Benchmark;
1056
for ($i=0 ; $i < $small_loop_count ; $i++)
1058
$count+=fetch_all_rows($dbh,"select count(a.dummy1),count(b.dummy1) from bench1 as a left outer join bench1 as b on (a.id2=b.id3) where b.id3 is not null");
1060
$end_time=new Benchmark;
1061
print "Time for outer_join_found ($small_loop_count:$count): " .
1062
timestr(timediff($end_time, $loop_time),"all") . "\n";
1064
$count=$estimated=0;
1065
$loop_time=new Benchmark;
1066
for ($i=1 ; $i <= $small_loop_count ; $i++)
1068
$count+=fetch_all_rows($dbh,"select count(a.dummy1),count(b.dummy1) from bench1 as a left outer join bench1 as b on (a.id2=b.id3) where b.id3 is null");
1069
$end_time=new Benchmark;
1070
last if ($estimated=predict_query_time($loop_time,$end_time,
1072
$range_loop_count));
1075
{ print "Estimated time"; }
1078
print " for outer_join_not_found ($range_loop_count:$count): " .
1079
timestr(timediff($end_time, $loop_time),"all") . "\n";
1081
if ($opt_lock_tables)
1083
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
1087
if ($server->small_rollback_segment())
1089
$dbh->disconnect; # close connection
1090
$dbh = $server->connect();
1094
### Test speed of IN( value list)
1097
if ($limits->{'left_outer_join'})
1099
if ($opt_lock_tables)
1101
$sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
1104
do_many($dbh,$server->create("bench2",
1105
["id int NOT NULL"],
1106
["primary key (id)"]));
1108
$max_tests=min(($limits->{'query_size'}-50)/6, $opt_loop_count);
1110
if ($opt_lock_tables)
1112
$sth = $dbh->do("LOCK TABLES bench1 READ, bench2 WRITE") ||
1115
test_where_in("bench1","bench2","id",1,10);
1116
test_where_in("bench1","bench2","id",11,min(100,$max_tests));
1117
test_where_in("bench1","bench2","id",101,min(1000,$max_tests));
1118
if ($opt_lock_tables)
1120
$sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
1122
$sth = $dbh->do("DROP TABLE bench2" . $server->{'drop_attr'}) ||
1124
if ($opt_lock_tables)
1126
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
1131
#### Test INSERT INTO ... SELECT
1134
if ($limits->{'insert_select'})
1136
if ($opt_lock_tables)
1138
$sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
1140
print "\nTesting INSERT INTO ... SELECT\n";
1141
do_many($dbh,$server->create("bench2",
1146
["primary key (id,id2)"]));
1147
do_many($dbh,$server->create("bench3",
1152
["primary key (id,id2)",
1153
"index index_id3 (id3)"]));
1154
$loop_time=new Benchmark;
1155
$sth = $dbh->do("INSERT INTO bench2 SELECT * from bench1") ||
1157
$end_time=new Benchmark;
1158
print "Time for insert_select_1_key (1): " .
1159
timestr(timediff($end_time, $loop_time),"all") . "\n";
1160
$loop_time=new Benchmark;
1161
$sth = $dbh->do("INSERT INTO bench3 SELECT * from bench1") ||
1163
$end_time=new Benchmark;
1164
print "Time for insert_select_2_keys (1): " .
1165
timestr(timediff($end_time, $loop_time),"all") . "\n";
1166
$loop_time=new Benchmark;
1167
$sth = $dbh->do("DROP TABLE bench2" . $server->{'drop_attr'}) ||
1169
$sth = $dbh->do("DROP TABLE bench3" . $server->{'drop_attr'}) ||
1171
$end_time=new Benchmark;
1172
print "Time for drop table(2): " .
1173
timestr(timediff($end_time, $loop_time),"all") . "\n";
1175
if ($opt_fast && defined($server->{vacuum}))
1177
$server->vacuum(1,\$dbh);
1179
if ($server->small_rollback_segment())
1181
$dbh->disconnect; # close connection
1182
$dbh = $server->connect();
1184
if ($opt_lock_tables)
1186
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
1191
#### Do some deletes on the table
1194
if (!$opt_skip_delete)
1196
print "\nTesting delete\n";
1197
$loop_time=new Benchmark;
1199
for ($i=0 ; $i < $opt_loop_count ; $i+=10)
1202
$tmp=$opt_loop_count+$random[$i]; # $opt_loop_count*2 <= $tmp < $total_rows
1203
$dbh->do("delete from bench1 where id3=$tmp") or die $DBI::errstr;
1206
$end_time=new Benchmark;
1207
print "Time for delete_key ($count): " .
1208
timestr(timediff($end_time, $loop_time),"all") . "\n";
1210
if ($server->small_rollback_segment())
1212
$dbh->disconnect; # close connection
1213
$dbh = $server->connect();
1217
$loop_time=new Benchmark;
1218
for ($i= 0 ; $i < $opt_loop_count ; $i+=$opt_loop_count/10)
1220
$sth=$dbh->do("delete from bench1 where id3 >= 0 and id3 <= $i") or die $DBI::errstr;
1224
if ($server->small_rollback_segment())
1226
$dbh->disconnect; # close connection
1227
$dbh = $server->connect();
1229
$sth=$dbh->do("delete from bench1 where id3 >= 0 and id3 <= $opt_loop_count") or die $DBI::errstr;
1230
if ($server->small_rollback_segment())
1232
$dbh->disconnect; # close connection
1233
$dbh = $server->connect();
1236
$sth=$dbh->do("delete from bench1 where id >= $opt_loop_count and id <= " . ($opt_loop_count*2) ) or die $DBI::errstr;
1238
if ($server->small_rollback_segment())
1240
$dbh->disconnect; # close connection
1241
$dbh = $server->connect();
1245
$sth=$dbh->do("delete from bench1") or die $DBI::errstr;
1249
$sth = $dbh->do("delete from bench1 where id3 < " . ($total_rows)) or die $DBI::errstr;
1252
$end_time=new Benchmark;
1253
print "Time for delete_range ($count): " .
1254
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1256
if ($opt_lock_tables)
1258
$sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
1260
$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
1263
if ($server->small_rollback_segment())
1265
$dbh->disconnect; # close connection
1266
$dbh = $server->connect();
1268
if ($opt_fast && defined($server->{vacuum}))
1270
$server->vacuum(1,\$dbh);
1276
# Test of insert in table with many keys
1277
# This test assumes that the server really create the keys!
1280
my @fields=(); my @keys=();
1281
$keys=min($limits->{'max_index'},16); # 16 is more than enough
1282
$seg= min($limits->{'max_index_parts'},$keys,16); # 16 is more than enough
1284
print "Insert into table with $keys keys and with a primary key with $seg parts\n";
1286
# Make keys on the most important types
1287
@types=(0,0,0,1,0,0,0,1,1,1,1,1,1,1,1,1,1); # A 1 for each char field
1288
push(@fields,"field1 tinyint not null");
1289
push(@fields,"field_search tinyint not null");
1290
push(@fields,"field2 mediumint not null");
1291
push(@fields,"field3 smallint not null");
1292
push(@fields,"field4 char(16) not null");
1293
push(@fields,"field5 integer not null");
1294
push(@fields,"field6 float not null");
1295
push(@fields,"field7 double not null");
1296
for ($i=8 ; $i <= $keys ; $i++)
1298
push(@fields,"field$i char(6) not null"); # Should be relatively fair
1301
# First key contains many segments
1302
$query="primary key (";
1303
for ($i= 1 ; $i <= $seg ; $i++)
1305
$query.= "field$i,";
1307
substr($query,-1)=")";
1308
push (@keys,$query);
1309
push (@keys,"index index2 (field_search)");
1312
for ($i=3 ; $i <= $keys ; $i++)
1314
push(@keys,"index index$i (field$i)");
1317
do_many($dbh,$server->create("bench1",\@fields,\@keys));
1318
if ($opt_lock_tables)
1320
$dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
1323
if ($server->small_rollback_segment())
1325
$dbh->disconnect; # close connection
1326
$dbh = $server->connect();
1329
$loop_time=new Benchmark;
1330
if ($opt_fast && $server->{transactions})
1332
$dbh->{AutoCommit} = 0;
1336
if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value'})
1338
$query_size=$server->{'limits'}->{'query_size'};
1339
$query="insert into bench1 values ";
1341
for ($i=0; $i < $many_keys_loop_count; $i++)
1345
$tmp="($id,$id,$rand," . ($i & 32766) . ",'ABCDEF$rand',0,$rand,$rand.0,";
1347
for ($j=8; $j <= $fields ; $j++)
1349
$tmp.= ($types[$j] == 0) ? "$rand," : "'$rand',";
1351
substr($tmp,-1)=")";
1352
if (length($tmp)+length($res) < $query_size)
1358
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
1359
$res=$query . $tmp . ",";
1362
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
1366
for ($i=0; $i < $many_keys_loop_count; $i++)
1370
$query="insert into bench1 values ($id,$id,$rand," . ($i & 32767) .
1371
",'ABCDEF$rand',0,$rand,$rand.0,";
1373
for ($j=8; $j <= $fields ; $j++)
1375
$query.= ($types[$j] == 0) ? "$rand," : "'$rand',";
1377
substr($query,-1)=")";
1378
print "query1: $query\n" if ($opt_debug);
1379
$dbh->do($query) or die "Got error $DBI::errstr with query: $query\n";
1383
if ($opt_fast && $server->{transactions})
1386
$dbh->{AutoCommit} = 1;
1389
$end_time=new Benchmark;
1390
print "Time for insert_key ($many_keys_loop_count): " .
1391
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1393
if ($server->small_rollback_segment())
1395
$dbh->disconnect; # close connection
1396
$dbh = $server->connect();
1398
if ($opt_fast && defined($server->{vacuum}))
1400
if ($opt_lock_tables)
1402
do_query($dbh,"UNLOCK TABLES");
1404
$server->vacuum(1,\$dbh,"bench1");
1405
if ($opt_lock_tables)
1407
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
1412
# update one key of the above
1415
print "Testing update of keys\n";
1416
$loop_time=new Benchmark;
1418
if ($opt_fast && $server->{transactions})
1420
$dbh->{AutoCommit} = 0;
1423
for ($i=0 ; $i< 256; $i++)
1425
$dbh->do("update bench1 set field5=1 where field_search=$i")
1426
or die "Got error $DBI::errstr with query: update bench1 set field5=1 where field_search=$i\n";
1429
if ($opt_fast && $server->{transactions})
1432
$dbh->{AutoCommit} = 1;
1435
$end_time=new Benchmark;
1436
print "Time for update_of_primary_key_many_keys (256): " .
1437
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1439
if ($server->small_rollback_segment())
1441
$dbh->disconnect; # close connection
1442
$dbh = $server->connect();
1444
if ($opt_fast && defined($server->{vacuum}))
1446
if ($opt_lock_tables)
1448
do_query($dbh,"UNLOCK TABLES");
1450
$server->vacuum(1,\$dbh,"bench1");
1451
if ($opt_lock_tables)
1453
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
1457
if ($server->small_rollback_segment())
1459
$dbh->disconnect; # close connection
1460
$dbh = $server->connect();
1464
# Delete everything from table
1467
print "Deleting rows from the table\n";
1468
$loop_time=new Benchmark;
1471
for ($i=0 ; $i < 128 ; $i++)
1474
$dbh->do("delete from bench1 where field_search = $i") or die $DBI::errstr;
1477
$end_time=new Benchmark;
1478
print "Time for delete_big_many_keys ($count): " .
1479
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1481
if ($opt_lock_tables)
1483
$sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
1486
print "Deleting everything from table\n";
1490
$query= ($limits->{'truncate_table'} ? "truncate table bench1" :
1491
"delete from bench1");
1492
$dbh->do($query) or die $DBI::errstr;
1496
$dbh->do("delete from bench1 where field1 > 0") or die $DBI::errstr;
1499
$end_time=new Benchmark;
1500
print "Time for delete_all_many_keys ($count): " .
1501
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1503
$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
1504
if ($opt_fast && defined($server->{vacuum}))
1506
$server->vacuum(1,\$dbh);
1510
# Test multi value inserts if the server supports it
1513
if ($limits->{'insert_multi_value'})
1515
$query_size=$limits->{'query_size'}; # Same limit for all databases
1517
$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'});
1518
do_many($dbh,$server->create("bench1",
1523
["primary key (id,id2)",
1524
"index index_id3 (id3)"]));
1526
$loop_time=new Benchmark;
1528
if ($opt_lock_tables)
1530
$sth = $dbh->do("LOCK TABLES bench1 write") || die $DBI::errstr;
1532
if ($opt_fast && $server->{transactions})
1534
$dbh->{AutoCommit} = 0;
1537
print "Inserting $opt_loop_count rows with multiple values\n";
1538
$query="insert into bench1 values ";
1540
for ($i=0 ; $i < $opt_loop_count ; $i++)
1542
my $tmp= "($i,$i,$i,'EFGHIJKLM'),";
1543
if (length($i)+length($res) < $query_size)
1549
do_query($dbh,substr($res,0,length($res)-1));
1553
do_query($dbh,substr($res,0,length($res)-1));
1555
if ($opt_lock_tables)
1557
$sth = $dbh->do("UNLOCK TABLES ") || die $DBI::errstr;
1559
if ($opt_fast && $server->{transactions})
1562
$dbh->{AutoCommit} = 1;
1565
$end_time=new Benchmark;
1566
print "Time for multiple_value_insert (" . ($opt_loop_count) . "): " .
1567
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1569
if ($opt_lock_tables)
1571
$sth = $dbh->do("UNLOCK TABLES ") || die $DBI::errstr;
1574
# A big table may take a while to drop
1575
$loop_time=new Benchmark;
1576
$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
1577
$end_time=new Benchmark;
1578
print "Time for drop table(1): " .
1579
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1583
#### End of benchmark
1586
$dbh->disconnect; # close connection
1588
end_benchmark($start_time);
1591
### Some help functions
1595
# Do some sample selects on direct key
1596
# First select finds a row, the second one doesn't find.
1598
sub check_select_key
1600
my ($sel_columns,$column,$check)= @_;
1601
my ($loop_time,$end_time,$i,$tmp_var,$tmp,$count,$row_count,$estimated);
1604
$loop_time=new Benchmark;
1606
for ($i=1 ; $i <= $opt_read_key_loop_count; $i++)
1609
$tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
1610
$tmp=$tmpvar % ($total_rows);
1611
fetch_all_rows($dbh,"select $sel_columns from bench1 where $column=$tmp")
1612
or die $DBI::errstr;
1614
defined($row_count=fetch_all_rows($dbh,"select $sel_columns from bench1 where $column=$tmp")) or die $DBI::errstr;
1615
die "Found $row_count rows on impossible id: $tmp\n" if ($row_count);
1616
$end_time=new Benchmark;
1617
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i,
1621
{ print "Estimated time"; }
1624
print " for $check ($count): " .
1625
timestr(timediff($end_time, $loop_time),"all") . "\n";
1628
# Same as above, but select on 2 columns
1630
sub check_select_key2
1632
my ($sel_columns,$column,$column2,$check)= @_;
1633
my ($loop_time,$end_time,$i,$tmp_var,$tmp,$count,$row_count,$estimated);
1636
$loop_time=new Benchmark;
1638
for ($i=1 ; $i <= $opt_read_key_loop_count; $i++)
1641
$tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
1642
$tmp=$tmpvar % ($total_rows);
1643
fetch_all_rows($dbh,"select $sel_columns from bench1 where $column=$tmp and $column2=$tmp")
1644
or die $DBI::errstr;
1646
defined($row_count=fetch_all_rows($dbh,"select $sel_columns from bench1 where $column=$tmp and $column2=$tmp")) or die $DBI::errstr;
1647
die "Found $row_count rows on impossible id: $tmp\n" if ($row_count);
1648
$end_time=new Benchmark;
1649
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i,
1653
{ print "Estimated time"; }
1656
print " for $check ($count): " .
1657
timestr(timediff($end_time, $loop_time),"all") . "\n";
1661
# Search using some very simple queries
1664
sub check_select_range
1666
my ($column,$check)= @_;
1667
my ($loop_time,$end_time,$i,$tmp_var,$tmp,$query,$rows,$estimated);
1670
$loop_time=new Benchmark;
1672
for ($test=1 ; $test <= $range_loop_count; $test++)
1675
for ($i=0 ; $i < $#Q ; $i+=2)
1679
$query =~ s/!id!/$column/g;
1680
if (($row_count=fetch_all_rows($dbh,$query)) != $rows)
1682
if ($row_count == undef())
1684
die "Got error: $DBI::errstr when executing $query\n";
1686
die "'$query' returned wrong number of rows: $row_count instead of $rows\n";
1690
$end_time=new Benchmark;
1691
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$test,
1692
$range_loop_count));
1695
{ print "Estimated time"; }
1698
print " for $check ($count:$found): " .
1699
timestr(timediff($end_time, $loop_time),"all") . "\n";
1704
# SELECT * from bench where col=x or col=x or col=x ...
1709
my ($column,$check)= @_;
1710
my ($loop_time,$end_time,$i,$tmp_var,$tmp,$columns,$estimated,$found,
1711
$or_part,$count,$loop_count);
1713
$columns=min($limits->{'max_columns'},50,($limits->{'query_size'}-50)/13);
1714
$columns=$columns- ($columns % 4); # Make Divisible by 4
1717
$loop_time=new Benchmark;
1719
# The number of tests must be divisible by the following
1720
$tmp= $limits->{'func_extra_in_num'} ? 15 : 10;
1721
# We need to calculate the exact number of test to make 'Estimated' right
1722
$loop_count=$range_loop_count*10+$tmp-1;
1723
$loop_count=$loop_count- ($loop_count % $tmp);
1725
for ($count=0 ; $count < $loop_count ; )
1727
for ($rowcnt=0; $rowcnt <= $columns; $rowcnt+= $columns/4)
1729
my $query="select * from bench1 where ";
1730
my $or_part= "$column = 1";
1733
for ($i=1 ; $i < $rowcnt ; $i++)
1735
$tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
1736
$tmp=$tmpvar % ($opt_loop_count*4);
1737
$or_part.=" or $column=$tmp";
1739
print $query . $or_part . "\n" if ($opt_debug);
1740
($rows=fetch_all_rows($dbh,$query . $or_part)) or die $DBI::errstr;
1743
if ($limits->{'func_extra_in_num'})
1745
my $in_part=$or_part; # Same query, but use 'func_extra_in_num' instead.
1746
$in_part=~ s/ = / IN \(/;
1747
$in_part=~ s/ or $column=/,/g;
1749
fetch_all_rows($dbh,$query . $in_part) or die $DBI::errstr;
1752
# Do it a little harder by setting a extra range
1753
defined(($rows=fetch_all_rows($dbh,"$query($or_part) and $column < 10"))) or die $DBI::errstr;
1756
$end_time=new Benchmark;
1757
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count,
1762
{ print "Estimated time"; }
1765
print " for $check ($count:$found): " .
1766
timestr(timediff($end_time, $loop_time),"all") . "\n";
1770
# General test of SELECT ... WHERE id in(value-list)
1775
my ($t1,$t2,$id,$from,$to)= @_;
1777
return if ($from >= $to);
1779
$query="SELECT $t1.* FROM $t1 WHERE $id IN (";
1780
for ($i=1 ; $i <= $to ; $i++)
1784
$query=substr($query,0,length($query)-1) . ")";
1786
# Fill join table to have the same id's as 'query'
1787
for ($i= $from ; $i <= $to ; $i++)
1789
$dbh->do("insert into $t2 values($i)") or die $DBI::errstr;
1791
if ($opt_fast && defined($server->{vacuum}))
1793
$server->vacuum(1,\$dbh,"bench1");
1796
time_fetch_all_rows("Testing SELECT ... WHERE id in ($to values)",
1797
"select_in", $query, $dbh,
1799
time_fetch_all_rows(undef, "select_join_in",
1800
"SELECT $t1.* FROM $t2 left outer join $t1 on ($t1.$id=$t2.$id)",
1801
$dbh, $range_loop_count);