1#!/usr/bin/perl 2# Copyright (c) 2000-2003, 2005-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########################################################## 21# this is the base file every test is using .... 22# this is made for not changing every file if we want to 23# add an option or just want to change something in 24# code what is the same in every file ... 25########################################################## 26 27# 28# The exported values are: 29 30# $opt_... Various options 31# $date Current date in ISO format 32# $server Object for current server 33# $limits Hash reference to limits for benchmark 34 35$benchmark_version="2.15"; 36use Getopt::Long; 37use POSIX; 38 39require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n"; 40 41$|=1; # Output data immediately 42 43$opt_skip_test=$opt_skip_create=$opt_skip_delete=$opt_verbose=$opt_fast_insert=$opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=$opt_log=$opt_use_old_results=$opt_help=$opt_odbc=$opt_small_test=$opt_small_tables=$opt_samll_key_tables=$opt_stage=$opt_old_headers=$opt_die_on_errors=$opt_tcpip=$opt_random=$opt_only_missing_tests=0; 44$opt_cmp=$opt_user=$opt_password=$opt_connect_options=""; 45$opt_server="mysql"; $opt_dir="output"; 46$opt_host="localhost";$opt_database="test"; 47$opt_machine=""; $opt_suffix=""; 48$opt_create_options=undef; 49$opt_optimization="None"; 50$opt_hw=""; 51$opt_threads=-1; 52 53if (!defined($opt_time_limit)) 54{ 55 $opt_time_limit=10*60; # Don't wait more than 10 min for some tests 56} 57 58$log_prog_args=join(" ", skip_arguments(\@ARGV,"comments","cmp","server", 59 "user", "host", "database", "password", 60 "use-old-results","skip-test", 61 "optimization","hw", 62 "machine", "dir", "suffix", "log")); 63GetOptions("skip-test=s","comments=s","cmp=s","server=s","user=s","host=s","database=s","password=s","loop-count=i","row-count=i","skip-create","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force","field-count=i","regions=i","groups=i","time-limit=i","log","use-old-results","machine=s","dir=s","suffix=s","help","odbc","small-test","small-tables","small-key-tables","stage=i","threads=i","random","old-headers","die-on-errors","create-options=s","hires","tcpip","silent","optimization=s","hw=s","socket=s","connect-options=s","only-missing-tests") || usage(); 64 65usage() if ($opt_help); 66$server=get_server($opt_server,$opt_host,$opt_database,$opt_odbc, 67 machine_part(), $opt_socket, $opt_connect_options); 68$limits=merge_limits($server,$opt_cmp); 69$date=date(); 70@estimated=(0.0,0.0,0.0); # For estimated time support 71 72if ($opt_threads != -1) 73{ 74 print "WARNING: Option --threads is deprecated and has no effect\n" 75} 76 77if ($opt_hires) 78{ 79 eval "use Time::HiRes;"; 80} 81 82{ 83 my $tmp= $opt_server; 84 $tmp =~ s/_odbc$//; 85 if (length($opt_cmp) && index($opt_cmp,$tmp) < 0) 86 { 87 $opt_cmp.=",$tmp"; 88 } 89} 90$opt_cmp=lc(join(",",sort(split(',',$opt_cmp)))); 91 92# 93# set opt_lock_tables if one uses --fast and drivers supports it 94# 95 96if (($opt_lock_tables || $opt_fast) && $server->{'limits'}->{'lock_tables'}) 97{ 98 $opt_lock_tables=1; 99} 100else 101{ 102 $opt_lock_tables=0; 103} 104if ($opt_fast) 105{ 106 $opt_fast_insert=1; 107 $opt_suffix="_fast" if (!length($opt_suffix)); 108} 109 110if ($opt_odbc) 111{ 112 $opt_suffix="_odbc" if (!length($opt_suffix)); 113} 114 115if (!$opt_silent) 116{ 117 print "Testing server '" . $server->version() . "' at $date\n\n"; 118} 119 120if ($opt_debug) 121{ 122 print "\nCurrent limits: \n"; 123 foreach $key (sort keys %$limits) 124 { 125 print $key . " " x (30-length($key)) . $limits->{$key} . "\n"; 126 } 127 print "\n"; 128} 129 130# 131# Some help functions 132# 133 134sub skip_arguments 135{ 136 my($argv,@skip_args)=@_; 137 my($skip,$arg,$name,@res); 138 139 foreach $arg (@$argv) 140 { 141 if ($arg =~ /^\-+([^=]*)/) 142 { 143 $name=$1; 144 foreach $skip (@skip_args) 145 { 146 if (index($skip,$name) == 0) 147 { 148 $name=""; # Don't use this parameters 149 last; 150 } 151 } 152 push (@res,$arg) if (length($name)); 153 } 154 } 155 return @res; 156} 157 158 159sub merge_limits 160{ 161 my ($server,$cmp)= @_; 162 my ($name,$tmp_server,$limits,$res_limits,$limit,$tmp_limits); 163 164 $res_limits=$server->{'limits'}; 165 if ($cmp) 166 { 167 foreach $name (split(",",$cmp)) 168 { 169 $tmp_server= (get_server($name,$opt_host, $opt_database, 170 $opt_odbc,machine_part()) 171 || die "Unknown SQL server: $name\n"); 172 $limits=$tmp_server->{'limits'}; 173 %new_limits=(); 174 foreach $limit (keys(%$limits)) 175 { 176 if (defined($res_limits->{$limit}) && defined($limits->{$limit})) 177 { 178 $new_limits{$limit}=min($res_limits->{$limit},$limits->{$limit}); 179 } 180 } 181 %tmp_limits=%new_limits; 182 $res_limits=\%tmp_limits; 183 } 184 } 185 return $res_limits; 186} 187 188sub date 189{ 190 my ($sec, $min, $hour, $mday, $mon, $year) = localtime(time()); 191 sprintf("%04d-%02d-%02d %2d:%02d:%02d", 192 1900+$year,$mon+1,$mday,$hour,$min,$sec); 193} 194 195sub min 196{ 197 my($min)=$_[0]; 198 my($i); 199 for ($i=1 ; $i <= $#_; $i++) 200 { 201 $min=$_[$i] if ($min > $_[$i]); 202 } 203 return $min; 204} 205 206sub max 207{ 208 my($max)=$_[0]; 209 my($i); 210 for ($i=1 ; $i <= $#_; $i++) 211 { 212 $max=$_[$i] if ($max < $_[$i]); 213 } 214 return $max; 215} 216 217 218# 219# Execute many statements in a row 220# 221 222sub do_many 223{ 224 my ($dbh,@statements)=@_; 225 my ($statement,$sth); 226 227 foreach $statement (@statements) 228 { 229 if (!($sth=$dbh->do($statement))) 230 { 231 die "Can't execute command '$statement'\nError: $DBI::errstr\n"; 232 } 233 } 234} 235 236sub safe_do_many 237{ 238 my ($dbh,@statements)=@_; 239 my ($statement,$sth); 240 241 foreach $statement (@statements) 242 { 243 if (!($sth=$dbh->do($statement))) 244 { 245 print STDERR "Can't execute command '$statement'\nError: $DBI::errstr\n"; 246 return 1; 247 } 248 } 249 return 0; 250} 251 252 253 254# 255# Do a query and fetch all rows from a statement and return the number of rows 256# 257 258sub fetch_all_rows 259{ 260 my ($dbh,$query,$must_get_result)=@_; 261 my ($count,$sth); 262 $count=0; 263 264 print "$query: " if ($opt_debug); 265 if (!($sth= $dbh->prepare($query))) 266 { 267 print "\n" if ($opt_debug); 268 die "Error occured with prepare($query)\n -> $DBI::errstr\n"; 269 return undef; 270 } 271 if (!$sth->execute) 272 { 273 print "\n" if ($opt_debug); 274 if (defined($server->{'error_on_execute_means_zero_rows'}) && 275 !$server->abort_if_fatal_error()) 276 { 277 if (defined($must_get_result) && $must_get_result) 278 { 279 die "Error: Query $query didn't return any rows\n"; 280 } 281 $sth->finish; 282 print "0\n" if ($opt_debug); 283 return 0; 284 } 285 die "Error occured with execute($query)\n -> $DBI::errstr\n"; 286 $sth->finish; 287 return undef; 288 } 289 while ($sth->fetchrow_arrayref) 290 { 291 $count++; 292 } 293 print "$count\n" if ($opt_debug); 294 if (defined($must_get_result) && $must_get_result && !$count) 295 { 296 die "Error: Query $query didn't return any rows\n"; 297 } 298 $sth->finish; 299 undef($sth); 300 return $count; 301} 302 303sub do_query 304{ 305 my($dbh,$query)=@_; 306 print "$query\n" if ($opt_debug); 307 $dbh->do($query) or 308 die "\nError executing '$query':\n$DBI::errstr\n"; 309} 310 311# 312# Run a query X times 313# 314 315sub time_fetch_all_rows 316{ 317 my($test_text,$result_text,$query,$dbh,$test_count)=@_; 318 my($i,$loop_time,$end_time,$count,$rows,$estimated); 319 320 print $test_text . "\n" if (defined($test_text)); 321 $count=$rows=0; 322 $loop_time=new Benchmark; 323 for ($i=1 ; $i <= $test_count ; $i++) 324 { 325 $count++; 326 $rows+=fetch_all_rows($dbh,$query) or die $DBI::errstr; 327 $end_time=new Benchmark; 328 last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i, 329 $test_count)); 330 } 331 $end_time=new Benchmark; 332 if ($estimated) 333 { print "Estimated time"; } 334 else 335 { print "Time"; } 336 print " for $result_text ($count:$rows) " . 337 timestr(timediff($end_time, $loop_time),"all") . "\n\n"; 338} 339 340 341# 342# Handle estimated time of the server is too slow 343# Returns 0 if one should continue as normal 344# 345 346sub predict_query_time 347{ 348 my ($loop_time,$end_time,$count_ref,$loop,$loop_count)= @_; 349 my ($k,$tmp); 350 351 if (($end_time->[0] - $loop_time->[0]) > $opt_time_limit) 352 { 353 # We can't wait until the SUN dies. Try to predict the end time 354 if ($loop != $loop_count) 355 { 356 $tmp=($end_time->[0] - $loop_time->[0]); 357 print "Note: Query took longer then time-limit: $opt_time_limit\nEstimating end time based on:\n"; 358 print "$$count_ref queries in $loop loops of $loop_count loops took $tmp seconds\n"; 359 for ($k=0; $k < 3; $k++) 360 { 361 $tmp=$loop_time->[$k]+($end_time->[$k]-$loop_time->[$k])/$loop* 362 $loop_count; 363 $estimated[$k]+=($tmp-$end_time->[$k]); 364 $end_time->[$k]=$tmp; 365 } 366 $$count_ref= int($$count_ref/$loop*$loop_count); 367 return 1; 368 } 369 } 370 return 0; 371} 372 373# 374# standard end of benchmark 375# 376 377sub end_benchmark 378{ 379 my ($start_time)=@_; 380 381 $end_time=new Benchmark; 382 if ($estimated[0]) 383 { 384 print "Estimated total time: "; 385 $end_time->[0]+=$estimated[0]; 386 $end_time->[1]+=$estimated[1]; 387 $end_time->[2]+=$estimated[2]; 388 } 389 else 390 { 391 print "Total time: " 392 } 393 print timestr(timediff($end_time, $start_time),"all") . "\n"; 394 exit 0; 395} 396 397sub print_time 398{ 399 my ($estimated)=@_; 400 if ($estimated) 401 { print "Estimated time"; } 402 else 403 { print "Time"; } 404} 405 406# 407# Create a filename part for the machine that can be used for log file. 408# 409 410sub machine_part 411{ 412 my ($name,$orig); 413 return $opt_machine if (length($opt_machine)); # Specified by user 414# Specified by user 415 $orig=$name=machine(); 416 $name="win9$1" if ($orig =~ /win.*9(\d)/i); 417 $name="NT_$1" if ($orig =~ /Windows NT.*(\d+\.\d+)/i); 418 $name="win2k" if ($orig =~ /Windows 2000/i); 419 $name =~ s/\s+/_/g; # Make the filenames easier to parse 420 $name =~ s/-/_/g; 421 $name =~ s/\//_/g; 422 return $name; 423} 424 425sub machine 426{ 427 my @name = POSIX::uname(); 428 my $name= $name[0] . " " . $name[2] . " " . $name[4]; 429 return $name; 430} 431 432# 433# Usage 434# 435 436sub usage 437{ 438 print <<EOF; 439The MySQL benchmarks Ver $benchmark_version 440 441All benchmarks takes the following options: 442 443--comments 444 Add a comment to the benchmark output. Comments should contain 445 extra information that 'uname -a' doesn\'t give and if the database was 446 stared with some specific, non default, options. 447 448--cmp=server[,server...] 449 Run the test with limits from the given servers. If you run all servers 450 with the same --cmp, you will get a test that is comparable between 451 the different sql servers. 452 453--create-options=# 454 Extra argument to all create statements. If you for example want to 455 create all MySQL tables as InnoDB tables use: 456 --create-options=ENGINE=InnoDB 457 458--database (Default $opt_database) 459 In which database the test tables are created. 460 461--debug 462 This is a test specific option that is only used when debugging a test. 463 Print out debugging information. 464 465--dir (Default $opt_dir) 466 Option to 'run-all-tests' to where the test results should be stored. 467 468--fast 469 Allow the database to use non standard ANSI SQL commands to make the 470 test go faster. 471 472--fast-insert 473 Use "insert into table_name values(...)" instead of 474 "insert into table_name (....) values(...)" 475 If the database supports it, some tests uses multiple value lists. 476 477--field-count 478 This is a test specific option that is only used when debugging a test. 479 This usually means how many fields there should be in the test table. 480 481--force 482 This is a test specific option that is only used when debugging a test. 483 Continue the test even if there is some error. 484 Delete tables before creating new ones. 485 486--groups (Default $opt_groups) 487 This is a test specific option that is only used when debugging a test. 488 This usually means how many different groups there should be in the test. 489 490--lock-tables 491 Allow the database to use table locking to get more speed. 492 493--log 494 Option to 'run-all-tests' to save the result to the '--dir' directory. 495 496--loop-count (Default $opt_loop_count) 497 This is a test specific option that is only used when debugging a test. 498 This usually means how many times times each test loop is executed. 499 500--help 501 Shows this help 502 503--host='host name' (Default $opt_host) 504 Host name where the database server is located. 505 506--machine="machine or os_name" 507 The machine/os name that is added to the benchmark output filename. 508 The default is the OS name + version. 509 510--odbc 511 Use the ODBC DBI driver to connect to the database. 512 513--only-missing-tests 514 Only run test that don\'t have an old test result. 515 This is useful when you want to do a re-run of tests that failed in last run. 516 517--optimization='some comments' 518 Add coments about optimization of DBMS, which was done before the test. 519 520--password='password' 521 Password for the current user. 522 523--socket='socket' 524 If the database supports connecting through a Unix socket, 525 then use this socket to connect 526 527--regions 528 This is a test specific option that is only used when debugging a test. 529 This usually means how AND levels should be tested. 530 531--old-headers 532 Get the old benchmark headers from the old RUN- file. 533 534--server='server name' (Default $opt_server) 535 Run the test on the given SQL server. 536 Known servers names are: Access, Adabas, AdabasD, Empress, Oracle, 537 Informix, DB2, mSQL, MS-SQL, MySQL, Pg, Solid and Sybase 538 539--silent 540 Don't print info about the server when starting test. 541 542--skip-delete 543 This is a test specific option that is only used when debugging a test. 544 This will keep the test tables after the test is run. 545 546--skip-test=test1[,test2,...] 547 For run-all-programs; Don\'t execute the named tests. 548 549--small-test 550 This runs some tests with smaller limits to get a faster test. 551 Can be used if you just want to verify that the database works, but 552 don't have time to run a full test. 553 554--small-tables 555 This runs some tests that generate big tables with fewer rows. 556 This can be used with databases that can\'t handle that many rows 557 because of pre-sized partitions. 558 559--suffix (Default $opt_suffix) 560 The suffix that is added to the database name in the benchmark output 561 filename. This can be used to run the benchmark multiple times with 562 different server options without overwritten old files. 563 When using --fast the suffix is automaticly set to '_fast'. 564 565--random 566 Inform test suite that we are generate random inital values for sequence of 567 test executions. It should be used for imitation of real conditions. 568 569--threads=# **DEPRECATED** 570 This option has no effect, and will be removed in a future version. 571 572--tcpip 573 Inform test suite that we are using TCP/IP to connect to the server. In 574 this case we can\t do many new connections in a row as we in this case may 575 fill the TCP/IP stack 576 577--time-limit (Default $opt_time_limit) 578 How long a test loop is allowed to take, in seconds, before the end result 579 is 'estimated'. 580 581--use-old-results 582 Option to 'run-all-tests' to use the old results from the '--dir' directory 583 instead of running the tests. 584 585--user='user_name' 586 User name to log into the SQL server. 587 588--verbose 589 This is a test specific option that is only used when debugging a test. 590 Print more information about what is going on. 591 592--hw='some comments' 593 Add coments about hardware used for this test. 594 595--connect-options='some connect options' 596 Add options, which uses at DBI connect. 597 For example --connect-options=mysql_read_default_file=/etc/my.cnf. 598 599EOF 600 exit(0); 601} 602 603 604 605#### 606#### The end of the base file ... 607#### 6081; 609