1#!/usr/bin/perl 2# Copyright (c) 2000-2003, 2006, 2007 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# 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 23# 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 29# 30##################### Standard benchmark inits ############################## 31 32use Cwd; 33use DBI; 34use Benchmark; 35use Data::Dumper; 36 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; 42 43$pwd = cwd(); $pwd = "." if ($pwd eq ''); 44require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; 45 46if ($opt_small_test) 47{ 48 $opt_loop_count/=100; 49 $many_keys_loop_count=$opt_loop_count/10; 50 $range_loop_count=10; 51 $opt_read_key_loop_count=10; 52} 53elsif ($opt_small_tables) 54{ 55 $opt_loop_count=10000; # number of rows/3 56 $many_keys_loop_count=$opt_loop_count; 57 $opt_read_key_loop_count=10; 58} 59elsif ($opt_small_key_tables) 60{ 61 $many_keys_loop_count/=10; 62} 63 64if ($opt_loop_count < 100) 65{ 66 $opt_loop_count=100; # Some tests must have some data to work! 67} 68$range_loop_count=min($opt_loop_count,$range_loop_count); 69 70 71print "Testing the speed of inserting data into 1 table and do some selects on it.\n"; 72print "The tests are done with a table that has $opt_loop_count rows.\n\n"; 73 74#### 75#### Generating random keys 76#### 77 78print "Generating random keys\n"; 79$random[$opt_loop_count]=0; 80for ($i=0 ; $i < $opt_loop_count ; $i++) 81{ 82 $random[$i]=$i+$opt_loop_count; 83} 84 85my $tmpvar=1; 86for ($i=0 ; $i < $opt_loop_count ; $i++) 87{ 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; 91} 92 93$total_rows=$opt_loop_count*3; 94 95#### 96#### Connect and start timeing 97#### 98$start_time=new Benchmark; 99$dbh = $server->connect(); 100#### 101#### Create needed tables 102#### 103 104goto keys_test if ($opt_stage == 2); 105goto select_test if ($opt_skip_create); 106 107print "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'}); 111do_many($dbh,$server->create("bench1", 112 ["id int NOT NULL", 113 "id2 int NOT NULL", 114 "id3 int NOT NULL", 115 "dummy1 char(30)"], 116 ["primary key (id,id2)", 117 "index ix_id3 (id3)"])); 118 119if ($opt_lock_tables) 120{ 121 $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr; 122} 123 124#### 125#### Insert $total_rows records in order, in reverse order and random. 126#### 127 128$loop_time=new Benchmark; 129 130if ($opt_fast_insert) 131{ 132 $query="insert into bench1 values "; 133} 134else 135{ 136 $query="insert into bench1 (id,id2,id3,dummy1) values "; 137} 138 139if ($opt_fast && $server->{transactions}) 140{ 141 $dbh->{AutoCommit} = 0; 142 print "Transactions enabled\n" if ($opt_debug); 143} 144 145if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value'}) 146{ 147 $query_size=$server->{'limits'}->{'query_size'}; 148 149 print "Inserting $opt_loop_count multiple-value rows in order\n"; 150 $res=$query; 151 for ($i=0 ; $i < $opt_loop_count ; $i++) 152 { 153 $tmp= "($i,$i,$i,'ABCDEFGHIJ'),"; 154 if (length($tmp)+length($res) < $query_size) 155 { 156 $res.= $tmp; 157 } 158 else 159 { 160 $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr; 161 $res=$query . $tmp; 162 } 163 } 164 print "Inserting $opt_loop_count multiple-value rows in reverse order\n"; 165 for ($i=0 ; $i < $opt_loop_count ; $i++) 166 { 167 $tmp= "(" . ($total_rows-1-$i) . "," .($total_rows-1-$i) . 168 "," .($total_rows-1-$i) . ",'BCDEFGHIJK'),"; 169 if (length($tmp)+length($res) < $query_size) 170 { 171 $res.= $tmp; 172 } 173 else 174 { 175 $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr; 176 $res=$query . $tmp; 177 } 178 } 179 print "Inserting $opt_loop_count multiple-value rows in random order\n"; 180 for ($i=0 ; $i < $opt_loop_count ; $i++) 181 { 182 $tmp= "(" . $random[$i] . "," . $random[$i] . "," . $random[$i] . 183 ",'CDEFGHIJKL')," or die $DBI::errstr; 184 if (length($tmp)+length($res) < $query_size) 185 { 186 $res.= $tmp; 187 } 188 else 189 { 190 $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr; 191 $res=$query . $tmp; 192 } 193 } 194 $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr; 195} 196else 197{ 198 print "Inserting $opt_loop_count rows in order\n"; 199 for ($i=0 ; $i < $opt_loop_count ; $i++) 200 { 201 $sth = $dbh->do($query . "($i,$i,$i,'ABCDEFGHIJ')") or die $DBI::errstr; 202 } 203 204 print "Inserting $opt_loop_count rows in reverse order\n"; 205 for ($i=0 ; $i < $opt_loop_count ; $i++) 206 { 207 $sth = $dbh->do($query . "(" . ($total_rows-1-$i) . "," . 208 ($total_rows-1-$i) . "," . 209 ($total_rows-1-$i) . ",'BCDEFGHIJK')") 210 or die $DBI::errstr; 211 } 212 213 print "Inserting $opt_loop_count rows in random order\n"; 214 215 for ($i=0 ; $i < $opt_loop_count ; $i++) 216 { 217 $sth = $dbh->do($query . "(". $random[$i] . "," . $random[$i] . 218 "," . $random[$i] . ",'CDEFGHIJKL')") or die $DBI::errstr; 219 } 220} 221 222if ($opt_fast && $server->{transactions}) 223{ 224 $dbh->commit; 225 $dbh->{AutoCommit} = 1; 226} 227 228$end_time=new Benchmark; 229print "Time for insert (" . ($total_rows) . "): " . 230 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 231 232if ($opt_lock_tables) 233{ 234 $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; 235} 236if ($opt_fast && defined($server->{vacuum})) 237{ 238 $server->vacuum(1,\$dbh,"bench1"); 239} 240if ($opt_lock_tables) 241{ 242 $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr; 243} 244 245#### 246#### insert $opt_loop_count records with duplicate id 247#### 248 249if ($limits->{'unique_index'}) 250{ 251 print "Testing insert of duplicates\n"; 252 $loop_time=new Benchmark; 253 254 if ($opt_fast && $server->{transactions}) 255 { 256 $dbh->{AutoCommit} = 0; 257 } 258 259 for ($i=0 ; $i < $opt_loop_count ; $i++) 260 { 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)) 265 { 266 die "Didn't get an error when inserting duplicate record $tmp\n"; 267 } 268 } 269 if ($opt_fast && $server->{transactions}) 270 { 271 $dbh->commit; 272 $dbh->{AutoCommit} = 1; 273 } 274 275 $end_time=new Benchmark; 276 print "Time for insert_duplicates (" . ($opt_loop_count) . "): " . 277 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 278} 279 280 281#### 282#### Do some selects on the table 283#### 284 285select_test: 286 287# ----------------- prepared+executed/prepared*executed tests 288 289print "Test of prepared+execute/once prepared many execute selects\n"; 290$loop_time=new Benchmark; 291 292for ($i=1 ; $i <= $opt_loop_count ; $i++) 293{ 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); 298 if (! $sth) 299 { 300 die "error in prepare select with id = $key_value : $DBI::errstr"; 301 }; 302 if (! $sth->execute) 303 { 304 die "cannot execute prepare select with id = $key_value : $DBI::errstr"; 305 } 306 while ($sth->fetchrow_arrayref) { }; 307 $sth->finish; 308}; 309$end_time=new Benchmark; 310print "Time for prepared_select ($opt_loop_count): " . 311 timestr(timediff($end_time, $loop_time),"all") . "\n"; 312 313$loop_time=new Benchmark; 314$query= "select * from bench1 where id=?"; 315$sth = $dbh->prepare($query); 316if (! $sth) 317{ 318 die "cannot prepare select: $DBI::errstr"; 319}; 320 321for ($i=1 ; $i <= $opt_loop_count ; $i++) 322{ 323 my ($key_value)=$random[$i]; 324 $sth->bind_param(1,$key_value); 325 print "$query , id = $key_value\n" if ($opt_debug); 326 if (! $sth->execute) 327 { 328 die "cannot execute prepare select with id = $key_value : $DBI::errstr"; 329 } 330 while ($sth->fetchrow_arrayref) { }; 331}; 332$sth->finish; 333$end_time=new Benchmark; 334print "Time for once_prepared_select ($opt_loop_count): " . 335 timestr(timediff($end_time, $loop_time),"all") . "\n"; 336 337 338print "Retrieving data from the table\n"; 339$loop_time=new Benchmark; 340$error=0; 341 342# It's really a small table, so we can try a select on everything 343 344$count=0; 345for ($i=1 ; $i <= $small_loop_count ; $i++) 346{ 347 if (($found_rows=fetch_all_rows($dbh,"select id from bench1")) != 348 $total_rows) 349 { 350 if (!$error++) 351 { 352 print "Warning: Got $found_rows rows when selecting a whole table of " . ($total_rows) . " rows\nContact the database or DBD author!\n"; 353 } 354 } 355 $count+=$found_rows; 356} 357 358$end_time=new Benchmark; 359print "Time for select_big ($small_loop_count:$count): " . 360 timestr(timediff($end_time, $loop_time),"all") . "\n"; 361 362# 363# Do a lot of different ORDER BY queries 364# 365 366$loop_time=new Benchmark; 367$estimated=$rows=0; 368for ($i=1 ; $i <= $small_loop_count ; $i++) 369{ 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, 373 $small_loop_count)); 374} 375if ($estimated) 376{ print "Estimated time"; } 377else 378{ print "Time"; } 379print " for order_by_big_key ($small_loop_count:$rows): " . 380 timestr(timediff($end_time, $loop_time),"all") . "\n"; 381 382 383$loop_time=new Benchmark; 384$estimated=$rows=0; 385for ($i=1 ; $i <= $small_loop_count ; $i++) 386{ 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, 390 $small_loop_count)); 391} 392if ($estimated) 393{ print "Estimated time"; } 394else 395{ print "Time"; } 396print " for order_by_big_key_desc ($small_loop_count:$rows): " . 397 timestr(timediff($end_time, $loop_time),"all") . "\n"; 398 399 400$loop_time=new Benchmark; 401$estimated=$rows=0; 402for ($i=1 ; $i <= $small_loop_count ; $i++) 403{ 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, 407 $small_loop_count)); 408} 409if ($estimated) 410{ print "Estimated time"; } 411else 412{ print "Time"; } 413print " for order_by_big_key_prefix ($small_loop_count:$rows): " . 414 timestr(timediff($end_time, $loop_time),"all") . "\n"; 415 416 417$loop_time=new Benchmark; 418$estimated=$rows=0; 419for ($i=1 ; $i <= $small_loop_count ; $i++) 420{ 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, 424 $small_loop_count)); 425} 426if ($estimated) 427{ print "Estimated time"; } 428else 429{ print "Time"; } 430print " for order_by_big_key2 ($small_loop_count:$rows): " . 431 timestr(timediff($end_time, $loop_time),"all") . "\n"; 432 433 434$sel=$limits->{'order_by_unused'} ? "id2" : "*"; 435$loop_time=new Benchmark; 436$estimated=$rows=0; 437for ($i=1 ; $i <= $small_loop_count ; $i++) 438{ 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, 442 $small_loop_count)); 443} 444if ($estimated) 445{ print "Estimated time"; } 446else 447{ print "Time"; } 448print " for order_by_big_key_diff ($small_loop_count:$rows): " . 449 timestr(timediff($end_time, $loop_time),"all") . "\n"; 450 451 452$sel=$limits->{'order_by_unused'} ? "id" : "*"; 453$loop_time=new Benchmark; 454$estimated=$rows=0; 455for ($i=1 ; $i <= $small_loop_count ; $i++) 456{ 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, 460 $small_loop_count)); 461} 462if ($estimated) 463{ print "Estimated time"; } 464else 465{ print "Time"; } 466print " for order_by_big ($small_loop_count:$rows): " . 467 timestr(timediff($end_time, $loop_time),"all") . "\n"; 468 469 470$sel=$limits->{'order_by_unused'} ? "dummy1" : "dummy1,id3"; 471$loop_time=new Benchmark; 472$estimated=$rows=0; 473for ($i=1 ; $i <= $range_loop_count ; $i++) 474{ 475 $start=$opt_loop_count/$range_loop_count*$i; 476 $end=$start+$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, 480 $range_loop_count)); 481} 482if ($estimated) 483{ print "Estimated time"; } 484else 485{ print "Time"; } 486print " for order_by_range ($range_loop_count:$rows): " . 487 timestr(timediff($end_time, $loop_time),"all") . "\n"; 488 489$sel=$limits->{'order_by_unused'} ? "dummy1" : "dummy1,id"; 490$loop_time=new Benchmark; 491$estimated=$rows=0; 492for ($i=1 ; $i <= $range_loop_count ; $i++) 493{ 494 $start=$opt_loop_count/$range_loop_count*$i; 495 $end=$start+$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, 499 $range_loop_count)); 500} 501if ($estimated) 502{ print "Estimated time"; } 503else 504{ print "Time"; } 505print " for order_by_key_prefix ($range_loop_count:$rows): " . 506 timestr(timediff($end_time, $loop_time),"all") . "\n"; 507 508$sel=$limits->{'order_by_unused'} ? "id2" : "id2,id3"; 509$loop_time=new Benchmark; 510$estimated=$rows=0; 511for ($i=1 ; $i <= $range_loop_count ; $i++) 512{ 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, 518 $range_loop_count)); 519} 520if ($estimated) 521{ print "Estimated time"; } 522else 523{ print "Time"; } 524print " for order_by_key2_diff ($range_loop_count:$rows): " . 525 timestr(timediff($end_time, $loop_time),"all") . "\n"; 526 527# 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). 530# 531 532$loop_time=new Benchmark; 533$estimated=0; 534$rows=0; 535$count=0; 536for ($i=1 ; $i <= $range_loop_count ; $i++) 537{ 538 my $rnd=$i; 539 my $rnd2=$random[$i]; 540 $rows+=fetch_all_rows($dbh,"select id2 from bench1 where id=$rnd or id3=$rnd2",1); 541 $count++; 542 $end_time=new Benchmark; 543 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count, 544 $range_loop_count)); 545} 546if ($estimated) 547{ print "Estimated time"; } 548else 549{ print "Time"; } 550print " for select_diff_key ($count:$rows): " . 551 timestr(timediff($end_time, $loop_time),"all") . "\n"; 552 553 554# Test select that is very popular when using ODBC 555 556check_or_range("id","select_range_prefix"); 557check_or_range("id3","select_range_key2"); 558 559# Check reading on direct key on id and id3 560 561check_select_key("*","id","select_key_prefix"); 562check_select_key2("*","id","id2","select_key"); 563check_select_key2("id,id2","id","id2","select_key_return_key"); 564check_select_key("*","id3","select_key2"); 565check_select_key("id3","id3","select_key2_return_key"); 566check_select_key("id,id2","id3","select_key2_return_prim"); 567 568#### 569#### A lot of simple selects on ranges 570#### 571 572@Q=("select * from bench1 where !id!=3 or !id!=2 or !id!=1 or !id!=4 or !id!=16 or !id!=10", 573 6, 574 "select * from bench1 where !id!>=" . ($total_rows-1) ." or !id!<1", 575 2, 576 "select * from bench1 where !id!>=1 and !id!<=2", 577 2, 578 "select * from bench1 where (!id!>=1 and !id!<=2) or (!id!>=1 and !id!<=2)", 579 2, 580 "select * from bench1 where !id!>=1 and !id!<=10 and !id!<=5", 581 5, 582 "select * from bench1 where (!id!>0 and !id!<2) or !id!>=" . ($total_rows-1), 583 2, 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), 585 5, 586 "select * from bench1 where (!id!>=5 and !id!<=10) or (!id!>=1 and !id!<=4)", 587 10, 588 "select * from bench1 where (!id!=1 or !id!=2) and (!id!=3 or !id!=4)", 589 0, 590 "select * from bench1 where (!id!=1 or !id!=2) and (!id!=2 or !id!=3)", 591 1, 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)", 593 3, 594 "select * from bench1 where ((!id!=1 or !id!=3) or (!id!>1 and !id!<3)) and !id!<=2", 595 2, 596 "select * from bench1 where (!id! >= 0 and !id! < 4) or (!id! >=4 and !id! < 6)", 597 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)", 599 9, 600 "select * from bench1 where (!id!>=1 and !id!<=2 or !id!>=4 and !id!<=5) or (!id!>=0 and !id! <=10)", 601 11, 602 "select * from bench1 where (!id!>=1 and !id!<=2 or !id!>=4 and !id!<=5) or (!id!>2 and !id! <=10)", 603 10, 604 "select * from bench1 where (!id!>1 or !id! <1) and !id!<=2", 605 2, 606 "select * from bench1 where !id! <= 2 and (!id!>1 or !id! <=1)", 607 3, 608 "select * from bench1 where (!id!>=1 or !id! <1) and !id!<=2", 609 3, 610 "select * from bench1 where (!id!>=1 or !id! <=2) and !id!<=2", 611 3 612 ); 613 614print "\nTest of compares with simple ranges\n"; 615check_select_range("id","select_range_prefix"); 616check_select_range("id3","select_range_key2"); 617 618#### 619#### Some group queries 620#### 621 622if ($limits->{'group_functions'}) 623{ 624 $loop_time=new Benchmark; 625 $count=1; 626 627 $estimated=0; 628 for ($tests=0 ; $tests < $small_loop_count ; $tests++) 629 { 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) 633 { 634 print "Warning: '$query' returned wrong result\n"; 635 } 636 $sth->finish; 637 638 # min, max in keys are very normal 639 $count+=7; 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'}) 645 { 646 fetch_all_rows($dbh,"select min(dummy1),max(dummy1) from bench1"); 647 } 648 $count++; 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) 653 { 654 print "Warning: '$query' returned wrong result\n"; 655 } 656 $sth->finish; 657 658 $count++; 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) || 664 $row[2] != 0 || 665 $row[3] != $total_rows-1 || 666 1-$row[4]/(($total_rows-1)/2) > 0.001) 667 { 668 # PostgreSQL 6.3 fails here 669 print "Warning: '$query' returned wrong result: @row\n"; 670 } 671 $sth->finish; 672 673 if ($limits->{'func_odbc_mod'}) 674 { 675 $tmp="mod(id,10)"; 676 if ($limits->{'func_extra_%'}) 677 { 678 $tmp="id % 10"; # For postgreSQL 679 } 680 $count++; 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) 683 { 684 print "Warning: '$query' returned wrong number of rows\n"; 685 } 686 } elsif ($limits->{'group_by_position'}) { 687 if (fetch_all_rows($dbh,$query=$server->query("select $tmp,count(*) from bench1 group by 1")) != 10) 688 { 689 print "Warning: '$query' returned wrong number of rows\n"; 690 } 691 } 692 } 693 694 if ($limits->{'order_by_position'} && $limits->{'group_by_position'}) 695 { 696 $count++; 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) 698 { 699 print "Warning: '$query' returned wrong number of rows\n"; 700 } 701 } 702 $end_time=new Benchmark; 703 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests, 704 $small_loop_count)); 705 } 706 print_time($estimated); 707 print " for select_group ($count): " . 708 timestr(timediff($end_time, $loop_time),"all") . "\n"; 709 710 $loop_time=new Benchmark; 711 $count=$estimated=0; 712 for ($tests=1 ; $tests <= $range_loop_count*5 ; $tests++) 713 { 714 $count+=6; 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'}) 720 { 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"); 723 } 724 $end_time=new Benchmark; 725 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests, 726 $range_loop_count*5)); 727 } 728 if ($estimated) 729 { print "Estimated time"; } 730 else 731 { print "Time"; } 732 print " for min_max_on_key ($count): " . 733 timestr(timediff($end_time, $loop_time),"all") . "\n"; 734 735 $loop_time=new Benchmark; 736 $count=$estimated=0; 737 for ($tests=1 ; $tests <= $small_loop_count ; $tests++) 738 { 739 $count+=6; 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'}) 745 { 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"); 748 } 749 $end_time=new Benchmark; 750 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests, 751 $range_loop_count)); 752 } 753 if ($estimated) 754 { print "Estimated time"; } 755 else 756 { print "Time"; } 757 print " for min_max ($count): " . 758 timestr(timediff($end_time, $loop_time),"all") . "\n"; 759 760 $loop_time=new Benchmark; 761 $count=0; 762 $total=$opt_loop_count*3; 763 for ($tests=0 ; $tests < $total ; $tests+=$total/100) 764 { 765 $count+=1; 766 fetch_all_rows($dbh,"select count(id) from bench1 where id < $tests"); 767 } 768 $end_time=new Benchmark; 769 print "Time for count_on_key ($count): " . 770 timestr(timediff($end_time, $loop_time),"all") . "\n"; 771 772 $loop_time=new Benchmark; 773 $count=0; 774 for ($tests=0 ; $tests < $total ; $tests+=$total/100) 775 { 776 $count+=1; 777 fetch_all_rows($dbh,"select count(dummy1) from bench1 where id2 < $tests"); 778 } 779 $end_time=new Benchmark; 780 print "Time for count ($count): " . 781 timestr(timediff($end_time, $loop_time),"all") . "\n"; 782 783 if ($limits->{'group_distinct_functions'}) 784 { 785 $loop_time=new Benchmark; 786 $count=$estimated=0; 787 for ($tests=1 ; $tests <= $small_loop_count ; $tests++) 788 { 789 $count+=2; 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, 794 $small_loop_count)); 795 } 796 if ($estimated) 797 { print "Estimated time"; } 798 else 799 { print "Time"; } 800 print " for count_distinct_big ($count): " . 801 timestr(timediff($end_time, $loop_time),"all") . "\n"; 802 } 803} 804 805 806if ($server->small_rollback_segment()) 807{ 808 $dbh->disconnect; # close connection 809 $dbh = $server->connect(); 810} 811 812#### 813#### Some updates on the table 814#### 815 816$loop_time=new Benchmark; 817 818if ($limits->{'functions'}) 819{ 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++) 823 { 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; 826 } 827 828 $end_time=new Benchmark; 829 print "Time for update_of_key ($update_loop_count): " . 830 timestr(timediff($end_time, $loop_time),"all") . "\n"; 831 832 if ($opt_lock_tables) 833 { 834 do_query($dbh,"UNLOCK TABLES"); 835 } 836 if ($opt_fast && defined($server->{vacuum})) 837 { 838 $server->vacuum(1,\$dbh,"bench1"); 839 } 840 if ($opt_lock_tables) 841 { 842 $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr; 843 } 844 845 if ($server->small_rollback_segment()) 846 { 847 $dbh->disconnect; # close connection 848 $dbh = $server->connect(); 849 } 850 851 $loop_time=new Benchmark; 852 $count=0; 853 $step=int($opt_loop_count/$range_loop_count+1); 854 for ($i= 0 ; $i < $opt_loop_count ; $i+= $step) 855 { 856 $count++; 857 $sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= 0 and id3 <= $i") or die $DBI::errstr; 858 } 859 860 if ($server->small_rollback_segment()) 861 { 862 $dbh->disconnect; # close connection 863 $dbh = $server->connect(); 864 } 865 $count++; 866 $sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= 0 and id3 < $opt_loop_count") or die $DBI::errstr; 867 868 if ($server->small_rollback_segment()) 869 { 870 $dbh->disconnect; # close connection 871 $dbh = $server->connect(); 872 } 873 $count++; 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; 875 876 # 877 # Check that everything was updated 878 # In principle we shouldn't time this in the update loop.. 879 # 880 881 if ($server->small_rollback_segment()) 882 { 883 $dbh->disconnect; # close connection 884 $dbh = $server->connect(); 885 } 886 $row_count=0; 887 if (($sth=$dbh->prepare("select count(*) from bench1 where id3>=0")) 888 && $sth->execute) 889 { 890 ($row_count)=$sth->fetchrow; 891 } 892 $result=1 + $opt_loop_count-$update_loop_count; 893 if ($row_count != $result) 894 { 895 print "Warning: Update check returned $row_count instead of $result\n"; 896 } 897 898 $sth->finish; 899 if ($server->small_rollback_segment()) 900 { 901 $dbh->disconnect; # close connection 902 $dbh = $server->connect(); 903 } 904 #restore id3 to 0 <= id3 < $total_rows/10 or 0<= id3 < $total_rows 905 906 my $func=($limits->{'func_odbc_floor'}) ? "floor((0-id3)/20)" : "0-id3"; 907 $count++; 908 $sth=$dbh->do($query="update bench1 set id3=$func where id3<0") or die $DBI::errstr; 909 910 $end_time=new Benchmark; 911 print "Time for update_of_key_big ($count): " . 912 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 913} 914else 915{ 916 print "\nTesting update of keys in loops\n"; 917 # 918 # This is for mSQL that doesn't have functions. Do we really need this ???? 919 # 920 921 $sth=$dbh->prepare("select id3 from bench1 where id3 >= 0") or die $DBI::errstr; 922 $sth->execute or die $DBI::errstr; 923 $count=0; 924 while (@tmp = $sth->fetchrow_array) 925 { 926 my $tmp1 = "-$tmp[0]"; 927 my $sth1 = $dbh->do("update bench1 set id3 = $tmp1 where id3 = $tmp[0]"); 928 $count++; 929 $end_time=new Benchmark; 930 if (($end_time->[0] - $loop_time->[0]) > $opt_time_limit) 931 { 932 print "note: Aborting update loop because of timeout\n"; 933 last; 934 } 935 } 936 $sth->finish; 937 # Check that everything except id3=0 was updated 938 # In principle we shouldn't time this in the update loop.. 939 # 940 if (fetch_all_rows($dbh,$query="select * from bench1 where id3>=0") != 1) 941 { 942 if ($count == $total_rows) 943 { 944 print "Warning: Wrong information after update: Found '$row_count' rows, but should have been: 1\n"; 945 } 946 } 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) 951 { 952 $count++; 953 my $tmp1 = floor((0-$tmp[0])/10); 954 my $sth1 = $dbh->do("update bench1 set id3 = $tmp1 where id3 = $tmp[0]"); 955 } 956 $sth->finish; 957 $end_time=new Benchmark; 958 $estimated=predict_query_time($loop_time,$end_time,\$count,$count, 959 $opt_loop_count*6); 960 if ($estimated) 961 { print "Estimated time"; } 962 else 963 { print "Time"; } 964 print " for update_of_key ($count): " . 965 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 966} 967 968if ($opt_fast && defined($server->{vacuum})) 969{ 970 if ($opt_lock_tables) 971 { 972 do_query($dbh,"UNLOCK TABLES"); 973 } 974 $server->vacuum(1,\$dbh,"bench1"); 975 if ($opt_lock_tables) 976 { 977 $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr; 978 } 979} 980 981# 982# Testing some simple updates 983# 984 985print "Testing update with key\n"; 986$loop_time=new Benchmark; 987for ($i=0 ; $i < $opt_loop_count*3 ; $i++) 988{ 989 $sth = $dbh->do("update bench1 set dummy1='updated' where id=$i and id2=$i") or die $DBI::errstr; 990} 991 992$end_time=new Benchmark; 993print "Time for update_with_key (" . ($opt_loop_count*3) . "): " . 994 timestr(timediff($end_time, $loop_time),"all") . "\n"; 995 996$loop_time=new Benchmark; 997$count=0; 998for ($i=1 ; $i < $opt_loop_count*3 ; $i+=3) 999{ 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, 1003 $opt_loop_count)); 1004} 1005if ($estimated) 1006{ print "Estimated time"; } 1007else 1008{ print "Time"; } 1009print " for update_with_key_prefix (" . ($opt_loop_count) . "): " . 1010 timestr(timediff($end_time, $loop_time),"all") . "\n"; 1011 1012print "\nTesting update of all rows\n"; 1013$loop_time=new Benchmark; 1014for ($i=0 ; $i < $small_loop_count ; $i++) 1015{ 1016 $sth = $dbh->do("update bench1 set dummy1='updated $i'") or die $DBI::errstr; 1017} 1018$end_time=new Benchmark; 1019print "Time for update_big ($small_loop_count): " . 1020 timestr(timediff($end_time, $loop_time),"all") . "\n"; 1021 1022 1023# 1024# Testing left outer join 1025# 1026 1027if ($limits->{'func_odbc_floor'} && $limits->{'left_outer_join'}) 1028{ 1029 if ($opt_lock_tables) 1030 { 1031 $sth = $dbh->do("LOCK TABLES bench1 a READ, bench1 b READ") || die $DBI::errstr; 1032 } 1033 print "\nTesting left outer join\n"; 1034 $loop_time=new Benchmark; 1035 $count=0; 1036 for ($i=0 ; $i < $small_loop_count ; $i++) 1037 { 1038 $count+=fetch_all_rows($dbh,"select count(*) from bench1 as a left outer join bench1 as b on (a.id2=b.id3)"); 1039 } 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"; 1043 1044 $loop_time=new Benchmark; 1045 $count=0; 1046 for ($i=0 ; $i < $small_loop_count ; $i++) 1047 { 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)"); 1049 } 1050 $end_time=new Benchmark; 1051 print "Time for outer_join ($small_loop_count:$count): " . 1052 timestr(timediff($end_time, $loop_time),"all") . "\n"; 1053 1054 $count=0; 1055 $loop_time=new Benchmark; 1056 for ($i=0 ; $i < $small_loop_count ; $i++) 1057 { 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"); 1059 } 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"; 1063 1064 $count=$estimated=0; 1065 $loop_time=new Benchmark; 1066 for ($i=1 ; $i <= $small_loop_count ; $i++) 1067 { 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, 1071 \$count,$i, 1072 $range_loop_count)); 1073 } 1074 if ($estimated) 1075 { print "Estimated time"; } 1076 else 1077 { print "Time"; } 1078 print " for outer_join_not_found ($range_loop_count:$count): " . 1079 timestr(timediff($end_time, $loop_time),"all") . "\n"; 1080 1081 if ($opt_lock_tables) 1082 { 1083 $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr; 1084 } 1085} 1086 1087if ($server->small_rollback_segment()) 1088{ 1089 $dbh->disconnect; # close connection 1090 $dbh = $server->connect(); 1091} 1092 1093### 1094### Test speed of IN( value list) 1095### 1096 1097if ($limits->{'left_outer_join'}) 1098{ 1099 if ($opt_lock_tables) 1100 { 1101 $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; 1102 } 1103 print "\n"; 1104 do_many($dbh,$server->create("bench2", 1105 ["id int NOT NULL"], 1106 ["primary key (id)"])); 1107 1108 $max_tests=min(($limits->{'query_size'}-50)/6, $opt_loop_count); 1109 1110 if ($opt_lock_tables) 1111 { 1112 $sth = $dbh->do("LOCK TABLES bench1 READ, bench2 WRITE") || 1113 die $DBI::errstr; 1114 } 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) 1119 { 1120 $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; 1121 } 1122 $sth = $dbh->do("DROP TABLE bench2" . $server->{'drop_attr'}) || 1123 die $DBI::errstr; 1124 if ($opt_lock_tables) 1125 { 1126 $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr; 1127 } 1128} 1129 1130#### 1131#### Test INSERT INTO ... SELECT 1132#### 1133 1134if ($limits->{'insert_select'}) 1135{ 1136 if ($opt_lock_tables) 1137 { 1138 $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; 1139 } 1140 print "\nTesting INSERT INTO ... SELECT\n"; 1141 do_many($dbh,$server->create("bench2", 1142 ["id int NOT NULL", 1143 "id2 int NOT NULL", 1144 "id3 int NOT NULL", 1145 "dummy1 char(30)"], 1146 ["primary key (id,id2)"])); 1147 do_many($dbh,$server->create("bench3", 1148 ["id int NOT NULL", 1149 "id2 int NOT NULL", 1150 "id3 int NOT NULL", 1151 "dummy1 char(30)"], 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") || 1156 die $DBI::errstr; 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") || 1162 die $DBI::errstr; 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'}) || 1168 die $DBI::errstr; 1169 $sth = $dbh->do("DROP TABLE bench3" . $server->{'drop_attr'}) || 1170 die $DBI::errstr; 1171 $end_time=new Benchmark; 1172 print "Time for drop table(2): " . 1173 timestr(timediff($end_time, $loop_time),"all") . "\n"; 1174 1175 if ($opt_fast && defined($server->{vacuum})) 1176 { 1177 $server->vacuum(1,\$dbh); 1178 } 1179 if ($server->small_rollback_segment()) 1180 { 1181 $dbh->disconnect; # close connection 1182 $dbh = $server->connect(); 1183 } 1184 if ($opt_lock_tables) 1185 { 1186 $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr; 1187 } 1188} 1189 1190#### 1191#### Do some deletes on the table 1192#### 1193 1194if (!$opt_skip_delete) 1195{ 1196 print "\nTesting delete\n"; 1197 $loop_time=new Benchmark; 1198 $count=0; 1199 for ($i=0 ; $i < $opt_loop_count ; $i+=10) 1200 { 1201 $count++; 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; 1204 } 1205 1206 $end_time=new Benchmark; 1207 print "Time for delete_key ($count): " . 1208 timestr(timediff($end_time, $loop_time),"all") . "\n"; 1209 1210 if ($server->small_rollback_segment()) 1211 { 1212 $dbh->disconnect; # close connection 1213 $dbh = $server->connect(); 1214 } 1215 1216 $count=0; 1217 $loop_time=new Benchmark; 1218 for ($i= 0 ; $i < $opt_loop_count ; $i+=$opt_loop_count/10) 1219 { 1220 $sth=$dbh->do("delete from bench1 where id3 >= 0 and id3 <= $i") or die $DBI::errstr; 1221 $count++; 1222 } 1223 $count+=2; 1224 if ($server->small_rollback_segment()) 1225 { 1226 $dbh->disconnect; # close connection 1227 $dbh = $server->connect(); 1228 } 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()) 1231 { 1232 $dbh->disconnect; # close connection 1233 $dbh = $server->connect(); 1234 } 1235 1236 $sth=$dbh->do("delete from bench1 where id >= $opt_loop_count and id <= " . ($opt_loop_count*2) ) or die $DBI::errstr; 1237 1238 if ($server->small_rollback_segment()) 1239 { 1240 $dbh->disconnect; # close connection 1241 $dbh = $server->connect(); 1242 } 1243 if ($opt_fast) 1244 { 1245 $sth=$dbh->do("delete from bench1") or die $DBI::errstr; 1246 } 1247 else 1248 { 1249 $sth = $dbh->do("delete from bench1 where id3 < " . ($total_rows)) or die $DBI::errstr; 1250 } 1251 1252 $end_time=new Benchmark; 1253 print "Time for delete_range ($count): " . 1254 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 1255 1256 if ($opt_lock_tables) 1257 { 1258 $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; 1259 } 1260 $sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr; 1261} 1262 1263if ($server->small_rollback_segment()) 1264{ 1265 $dbh->disconnect; # close connection 1266 $dbh = $server->connect(); 1267} 1268if ($opt_fast && defined($server->{vacuum})) 1269{ 1270 $server->vacuum(1,\$dbh); 1271} 1272 1273 1274keys_test: 1275# 1276# Test of insert in table with many keys 1277# This test assumes that the server really create the keys! 1278# 1279 1280my @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 1283 1284print "Insert into table with $keys keys and with a primary key with $seg parts\n"; 1285 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 1288push(@fields,"field1 tinyint not null"); 1289push(@fields,"field_search tinyint not null"); 1290push(@fields,"field2 mediumint not null"); 1291push(@fields,"field3 smallint not null"); 1292push(@fields,"field4 char(16) not null"); 1293push(@fields,"field5 integer not null"); 1294push(@fields,"field6 float not null"); 1295push(@fields,"field7 double not null"); 1296for ($i=8 ; $i <= $keys ; $i++) 1297{ 1298 push(@fields,"field$i char(6) not null"); # Should be relatively fair 1299} 1300 1301# First key contains many segments 1302$query="primary key ("; 1303for ($i= 1 ; $i <= $seg ; $i++) 1304{ 1305 $query.= "field$i,"; 1306} 1307substr($query,-1)=")"; 1308push (@keys,$query); 1309push (@keys,"index index2 (field_search)"); 1310 1311#Create other keys 1312for ($i=3 ; $i <= $keys ; $i++) 1313{ 1314 push(@keys,"index index$i (field$i)"); 1315} 1316 1317do_many($dbh,$server->create("bench1",\@fields,\@keys)); 1318if ($opt_lock_tables) 1319{ 1320 $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr; 1321} 1322 1323if ($server->small_rollback_segment()) 1324{ 1325 $dbh->disconnect; # close connection 1326 $dbh = $server->connect(); 1327} 1328 1329$loop_time=new Benchmark; 1330if ($opt_fast && $server->{transactions}) 1331{ 1332 $dbh->{AutoCommit} = 0; 1333} 1334 1335$fields=$#fields; 1336if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value'}) 1337{ 1338 $query_size=$server->{'limits'}->{'query_size'}; 1339 $query="insert into bench1 values "; 1340 $res=$query; 1341 for ($i=0; $i < $many_keys_loop_count; $i++) 1342 { 1343 $id= $i & 127; 1344 $rand=$random[$i]; 1345 $tmp="($id,$id,$rand," . ($i & 32766) . ",'ABCDEF$rand',0,$rand,$rand.0,"; 1346 1347 for ($j=8; $j <= $fields ; $j++) 1348 { 1349 $tmp.= ($types[$j] == 0) ? "$rand," : "'$rand',"; 1350 } 1351 substr($tmp,-1)=")"; 1352 if (length($tmp)+length($res) < $query_size) 1353 { 1354 $res.= $tmp . ","; 1355 } 1356 else 1357 { 1358 $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr; 1359 $res=$query . $tmp . ","; 1360 } 1361 } 1362 $sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr; 1363} 1364else 1365{ 1366 for ($i=0; $i < $many_keys_loop_count; $i++) 1367 { 1368 $id= $i & 127; 1369 $rand=$random[$i]; 1370 $query="insert into bench1 values ($id,$id,$rand," . ($i & 32767) . 1371 ",'ABCDEF$rand',0,$rand,$rand.0,"; 1372 1373 for ($j=8; $j <= $fields ; $j++) 1374 { 1375 $query.= ($types[$j] == 0) ? "$rand," : "'$rand',"; 1376 } 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"; 1380 } 1381} 1382 1383if ($opt_fast && $server->{transactions}) 1384{ 1385 $dbh->commit; 1386 $dbh->{AutoCommit} = 1; 1387} 1388 1389$end_time=new Benchmark; 1390print "Time for insert_key ($many_keys_loop_count): " . 1391 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 1392 1393if ($server->small_rollback_segment()) 1394{ 1395 $dbh->disconnect; # close connection 1396 $dbh = $server->connect(); 1397} 1398if ($opt_fast && defined($server->{vacuum})) 1399{ 1400 if ($opt_lock_tables) 1401 { 1402 do_query($dbh,"UNLOCK TABLES"); 1403 } 1404 $server->vacuum(1,\$dbh,"bench1"); 1405 if ($opt_lock_tables) 1406 { 1407 $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr; 1408 } 1409} 1410 1411# 1412# update one key of the above 1413# 1414 1415print "Testing update of keys\n"; 1416$loop_time=new Benchmark; 1417 1418if ($opt_fast && $server->{transactions}) 1419{ 1420 $dbh->{AutoCommit} = 0; 1421} 1422 1423for ($i=0 ; $i< 256; $i++) 1424{ 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"; 1427} 1428 1429if ($opt_fast && $server->{transactions}) 1430{ 1431 $dbh->commit; 1432 $dbh->{AutoCommit} = 1; 1433} 1434 1435$end_time=new Benchmark; 1436print "Time for update_of_primary_key_many_keys (256): " . 1437 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 1438 1439if ($server->small_rollback_segment()) 1440{ 1441 $dbh->disconnect; # close connection 1442 $dbh = $server->connect(); 1443} 1444if ($opt_fast && defined($server->{vacuum})) 1445{ 1446 if ($opt_lock_tables) 1447 { 1448 do_query($dbh,"UNLOCK TABLES"); 1449 } 1450 $server->vacuum(1,\$dbh,"bench1"); 1451 if ($opt_lock_tables) 1452 { 1453 $sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr; 1454 } 1455} 1456 1457if ($server->small_rollback_segment()) 1458{ 1459 $dbh->disconnect; # close connection 1460 $dbh = $server->connect(); 1461} 1462 1463# 1464# Delete everything from table 1465# 1466 1467print "Deleting rows from the table\n"; 1468$loop_time=new Benchmark; 1469$count=0; 1470 1471for ($i=0 ; $i < 128 ; $i++) 1472{ 1473 $count++; 1474 $dbh->do("delete from bench1 where field_search = $i") or die $DBI::errstr; 1475} 1476 1477$end_time=new Benchmark; 1478print "Time for delete_big_many_keys ($count): " . 1479timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 1480 1481if ($opt_lock_tables) 1482{ 1483 $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; 1484} 1485 1486print "Deleting everything from table\n"; 1487$count=1; 1488if ($opt_fast) 1489{ 1490 $query= ($limits->{'truncate_table'} ? "truncate table bench1" : 1491 "delete from bench1"); 1492 $dbh->do($query) or die $DBI::errstr; 1493} 1494else 1495{ 1496 $dbh->do("delete from bench1 where field1 > 0") or die $DBI::errstr; 1497} 1498 1499$end_time=new Benchmark; 1500print "Time for delete_all_many_keys ($count): " . 1501 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 1502 1503$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr; 1504if ($opt_fast && defined($server->{vacuum})) 1505{ 1506 $server->vacuum(1,\$dbh); 1507} 1508 1509# 1510# Test multi value inserts if the server supports it 1511# 1512 1513if ($limits->{'insert_multi_value'}) 1514{ 1515 $query_size=$limits->{'query_size'}; # Same limit for all databases 1516 1517 $sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}); 1518 do_many($dbh,$server->create("bench1", 1519 ["id int NOT NULL", 1520 "id2 int NOT NULL", 1521 "id3 int NOT NULL", 1522 "dummy1 char(30)"], 1523 ["primary key (id,id2)", 1524 "index index_id3 (id3)"])); 1525 1526 $loop_time=new Benchmark; 1527 1528 if ($opt_lock_tables) 1529 { 1530 $sth = $dbh->do("LOCK TABLES bench1 write") || die $DBI::errstr; 1531 } 1532 if ($opt_fast && $server->{transactions}) 1533 { 1534 $dbh->{AutoCommit} = 0; 1535 } 1536 1537 print "Inserting $opt_loop_count rows with multiple values\n"; 1538 $query="insert into bench1 values "; 1539 $res=$query; 1540 for ($i=0 ; $i < $opt_loop_count ; $i++) 1541 { 1542 my $tmp= "($i,$i,$i,'EFGHIJKLM'),"; 1543 if (length($i)+length($res) < $query_size) 1544 { 1545 $res.= $tmp; 1546 } 1547 else 1548 { 1549 do_query($dbh,substr($res,0,length($res)-1)); 1550 $res=$query .$tmp; 1551 } 1552 } 1553 do_query($dbh,substr($res,0,length($res)-1)); 1554 1555 if ($opt_lock_tables) 1556 { 1557 $sth = $dbh->do("UNLOCK TABLES ") || die $DBI::errstr; 1558 } 1559 if ($opt_fast && $server->{transactions}) 1560 { 1561 $dbh->commit; 1562 $dbh->{AutoCommit} = 1; 1563 } 1564 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"; 1568 1569 if ($opt_lock_tables) 1570 { 1571 $sth = $dbh->do("UNLOCK TABLES ") || die $DBI::errstr; 1572 } 1573 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"; 1580} 1581 1582#### 1583#### End of benchmark 1584#### 1585 1586$dbh->disconnect; # close connection 1587 1588end_benchmark($start_time); 1589 1590### 1591### Some help functions 1592### 1593 1594 1595# Do some sample selects on direct key 1596# First select finds a row, the second one doesn't find. 1597 1598sub check_select_key 1599{ 1600 my ($sel_columns,$column,$check)= @_; 1601 my ($loop_time,$end_time,$i,$tmp_var,$tmp,$count,$row_count,$estimated); 1602 1603 $estimated=0; 1604 $loop_time=new Benchmark; 1605 $count=0; 1606 for ($i=1 ; $i <= $opt_read_key_loop_count; $i++) 1607 { 1608 $count+=2; 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; 1613 $tmp+=$total_rows; 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, 1618 $opt_loop_count)); 1619 } 1620 if ($estimated) 1621 { print "Estimated time"; } 1622 else 1623 { print "Time"; } 1624 print " for $check ($count): " . 1625 timestr(timediff($end_time, $loop_time),"all") . "\n"; 1626} 1627 1628# Same as above, but select on 2 columns 1629 1630sub check_select_key2 1631{ 1632 my ($sel_columns,$column,$column2,$check)= @_; 1633 my ($loop_time,$end_time,$i,$tmp_var,$tmp,$count,$row_count,$estimated); 1634 1635 $estimated=0; 1636 $loop_time=new Benchmark; 1637 $count=0; 1638 for ($i=1 ; $i <= $opt_read_key_loop_count; $i++) 1639 { 1640 $count+=2; 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; 1645 $tmp+=$total_rows; 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, 1650 $opt_loop_count)); 1651 } 1652 if ($estimated) 1653 { print "Estimated time"; } 1654 else 1655 { print "Time"; } 1656 print " for $check ($count): " . 1657 timestr(timediff($end_time, $loop_time),"all") . "\n"; 1658} 1659 1660# 1661# Search using some very simple queries 1662# 1663 1664sub check_select_range 1665{ 1666 my ($column,$check)= @_; 1667 my ($loop_time,$end_time,$i,$tmp_var,$tmp,$query,$rows,$estimated); 1668 1669 $estimated=0; 1670 $loop_time=new Benchmark; 1671 $found=$count=0; 1672 for ($test=1 ; $test <= $range_loop_count; $test++) 1673 { 1674 $count+=$#Q+1; 1675 for ($i=0 ; $i < $#Q ; $i+=2) 1676 { 1677 $query=$Q[$i]; 1678 $rows=$Q[$i+1]; 1679 $query =~ s/!id!/$column/g; 1680 if (($row_count=fetch_all_rows($dbh,$query)) != $rows) 1681 { 1682 if ($row_count == undef()) 1683 { 1684 die "Got error: $DBI::errstr when executing $query\n"; 1685 } 1686 die "'$query' returned wrong number of rows: $row_count instead of $rows\n"; 1687 } 1688 $found+=$row_count; 1689 } 1690 $end_time=new Benchmark; 1691 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$test, 1692 $range_loop_count)); 1693 } 1694 if ($estimated) 1695 { print "Estimated time"; } 1696 else 1697 { print "Time"; } 1698 print " for $check ($count:$found): " . 1699 timestr(timediff($end_time, $loop_time),"all") . "\n"; 1700} 1701 1702 1703# 1704# SELECT * from bench where col=x or col=x or col=x ... 1705 1706 1707sub check_or_range 1708{ 1709 my ($column,$check)= @_; 1710 my ($loop_time,$end_time,$i,$tmp_var,$tmp,$columns,$estimated,$found, 1711 $or_part,$count,$loop_count); 1712 1713 $columns=min($limits->{'max_columns'},50,($limits->{'query_size'}-50)/13); 1714 $columns=$columns- ($columns % 4); # Make Divisible by 4 1715 1716 $estimated=0; 1717 $loop_time=new Benchmark; 1718 $found=0; 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); 1724 1725 for ($count=0 ; $count < $loop_count ; ) 1726 { 1727 for ($rowcnt=0; $rowcnt <= $columns; $rowcnt+= $columns/4) 1728 { 1729 my $query="select * from bench1 where "; 1730 my $or_part= "$column = 1"; 1731 $count+=2; 1732 1733 for ($i=1 ; $i < $rowcnt ; $i++) 1734 { 1735 $tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count); 1736 $tmp=$tmpvar % ($opt_loop_count*4); 1737 $or_part.=" or $column=$tmp"; 1738 } 1739 print $query . $or_part . "\n" if ($opt_debug); 1740 ($rows=fetch_all_rows($dbh,$query . $or_part)) or die $DBI::errstr; 1741 $found+=$rows; 1742 1743 if ($limits->{'func_extra_in_num'}) 1744 { 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; 1748 $in_part.= ")"; 1749 fetch_all_rows($dbh,$query . $in_part) or die $DBI::errstr; 1750 $count++; 1751 } 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; 1754 $found+=$rows; 1755 } 1756 $end_time=new Benchmark; 1757 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count, 1758 $loop_count)); 1759 } 1760 1761 if ($estimated) 1762 { print "Estimated time"; } 1763 else 1764 { print "Time"; } 1765 print " for $check ($count:$found): " . 1766 timestr(timediff($end_time, $loop_time),"all") . "\n"; 1767} 1768 1769# 1770# General test of SELECT ... WHERE id in(value-list) 1771# 1772 1773sub test_where_in 1774{ 1775 my ($t1,$t2,$id,$from,$to)= @_; 1776 1777 return if ($from >= $to); 1778 1779 $query="SELECT $t1.* FROM $t1 WHERE $id IN ("; 1780 for ($i=1 ; $i <= $to ; $i++) 1781 { 1782 $query.="$i,"; 1783 } 1784 $query=substr($query,0,length($query)-1) . ")"; 1785 1786 # Fill join table to have the same id's as 'query' 1787 for ($i= $from ; $i <= $to ; $i++) 1788 { 1789 $dbh->do("insert into $t2 values($i)") or die $DBI::errstr; 1790 } 1791 if ($opt_fast && defined($server->{vacuum})) 1792 { 1793 $server->vacuum(1,\$dbh,"bench1"); 1794 } 1795 1796 time_fetch_all_rows("Testing SELECT ... WHERE id in ($to values)", 1797 "select_in", $query, $dbh, 1798 $range_loop_count); 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); 1802} 1803