1#!/usr/bin/perl 2# Copyright (c) 2000, 2001, 2006 MySQL AB, 2009 Sun Microsystems, Inc. 3# Use is subject to license terms. 4# 5# This library is free software; you can redistribute it and/or 6# modify it under the terms of the GNU Library General Public 7# License as published by the Free Software Foundation; version 2 8# of the License. 9# 10# This library is distributed in the hope that it will be useful, 11# but WITHOUT ANY WARRANTY; without even the implied warranty of 12# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 13# Library General Public License for more details. 14# 15# You should have received a copy of the GNU Library General Public 16# License along with this library; if not, write to the Free 17# Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, 18# MA 02110-1301, USA 19# 20# a little program to generate a table of results 21# just read all the RUN-*.log files and format them nicely 22# Made by Luuk de Boer 23# Patched by Monty 24 25use Getopt::Long; 26 27$opt_server="mysql"; 28$opt_dir="output"; 29$opt_machine=$opt_cmp=""; 30$opt_relative=$opt_same_server=$opt_help=$opt_Information=$opt_skip_count=$opt_no_bars=$opt_verbose=0; 31 32GetOptions("Information","help","server=s","cmp=s","machine=s","relative","same-server","dir=s","skip-count","no-bars","html","verbose") || usage(); 33 34usage() if ($opt_help || $opt_Information); 35 36$opt_cmp=lc(join(",",sort(split(',',$opt_cmp)))); 37 38if ($opt_same_server) 39{ 40 $files="$opt_dir/RUN-$opt_server*$opt_machine"; 41} 42else 43{ 44 $files="$opt_dir/RUN-*$opt_machine"; 45} 46$files.= "-cmp-$opt_cmp" if (length($opt_cmp)); 47 48# 49# Go trough all RUN files and gather statistics. 50# 51 52if ($#ARGV == -1) 53{ 54 @ARGV=glob($files); 55 $automatic_files=1; 56} 57 58foreach (@ARGV) 59{ 60 next if (!$opt_cmp && /-cmp-/ && $automatic_files || defined($found{$_})); 61 $prog=$filename = $_; 62 $found{$_}=1; # Remove dupplicates 63 /RUN-(.*)$/; 64 $tot{$prog}{'version'}=$1; 65 push(@key_order,$prog); 66 $next = 0; 67 open(TMP, "<$filename") || die "Can't open $filename: $!\n"; 68 while (<TMP>) 69 { 70 chomp; 71 if ($next == 0) { 72 if (/Server version:\s+(\S+.*)/i) 73 { 74 $tot{$prog}{'server'} = $1; 75 } 76 elsif (/Arguments:\s+(.+)/i) 77 { 78 $arguments= $1; 79 # Remove some standard, not informative arguments 80 $arguments =~ s/--force|--log|--use-old\S*|--server=\S+|--cmp=\S+|--user=\S+|--pass=\S+|--machine=\S+|--dir=\S+//g; 81 if (($tmp=index($arguments,"--comment")) >= 0) 82 { 83 if (($end=index($arguments,$tmp+2,"--")) >= 0) 84 { 85 substr($arguments,$tmp,($end-$tmp))=""; 86 } 87 else 88 { 89 $arguments=substr($arguments,0,$tmp); 90 } 91 } 92 $arguments =~ s/\s+/ /g; 93 $tot{$prog}{'arguments'}=$arguments; 94 } 95 elsif (/Comments:\s+(.+)/i) { 96 $tot{$prog}{'comments'} = $1; 97 } elsif (/^(\S+):.*(estimated\s|)total\stime:\s+([\d.]+)\s+(wallclock\s|)secs/i) 98 { 99 $tmp = $1; $tmp =~ s/://; 100 $tot{$prog}{$tmp} = [ $3, (length($2) ? "+" : "")]; 101 $op1{$tmp} = $tmp; 102 } elsif (/Totals per operation:/i) { 103 $next = 1; 104 next; 105 } 106 } 107 elsif ($next == 1) 108 { 109 if (/^(\S+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s+([\d.]+)\s*([+|?])*/) 110 { 111 $tot1{$prog}{$1} = [$2,$6,$7]; 112 $op{$1} = $1; 113 } 114 } 115 } 116} 117 118if (!%op) 119{ 120 print "Didn't find any files matching: '$files'\n"; 121 print "Use the --cmp=server,server option to compare benchmarks\n"; 122 exit 1; 123} 124 125 126# everything is loaded ... 127# now we have to create a fancy output :-) 128 129# I prefer to redirect scripts instead to force it to file ; Monty 130# 131# open(RES, ">$resultfile") || die "Can't write to $resultfile: $!\n"; 132# select(RES) 133# 134 135if ($opt_html) { 136 html_output(); 137} else { 138 ascii_output(); 139} 140exit 0; 141 142# 143# some output + format functions; 144# 145 146sub ascii_output { 147 print <<EOF; 148This is the result file of the different benchmark tests. 149 150The number in () after each tests shows how many SQL commands the particular 151test did. As one test may have many different parameters this gives only 152a rough picture of what was done. Check the source for more information :) 153 154Keep in mind that one can\'t compare benchmarks run with different --cmp 155options. The --cmp options sets the all limits according to the worst 156limit for all server in the benchmark. 157 158Numbers marked with '+' are estimated according to previous runs because 159the query took longer than a given time-limit to finish. The estimation 160shouldn\'t be far from the real result thought. 161 162Numbers marked with '?' contains results that gave wrong result. This can only 163be used as an indication of how long it took for the server to produce a wrong 164result :) 165 166Numbers marked with '*' are tests that was run a different number times 167than the test in the first column. The reason for this is normally that the 168marked test was run with different options that affects the number of tests 169or that the test was from another version of the MySQL benchmarks. 170 171Hope this will give you some idea how each db is performing at what thing .... 172Hope you like it .... Luuk & Monty (1997) 173 174EOF 175 176 if ($opt_relative) 177 { 178 print "Column 1 is in seconds. All other columns are presented relative\n"; 179 print "to this. 1.00 is the same, bigger numbers indicates slower\n\n"; 180 } 181 182 if ($opt_verbose) 183 { 184 print "The test values is printed in the format 'time:number of tests'\n"; 185 } 186 187 if (length($opt_cmp)) 188 { 189 print "The test was run with limits from: $opt_cmp\n\n"; 190 } 191 print "The result logs which where found and the options:\n"; 192 $bar= $opt_no_bars ? " " : "|"; 193 194 # Move $opt_server first in array if not filename on command line 195 if ($automatic_files) 196 { 197 @key_order=sort {$a cmp $b} keys %tot; 198 for ($i=0; $i <= $#key_order; $i++) 199 { 200 if ($tot{$key_order[$i]}{'version'} =~ /^$opt_server-/) 201 { 202 unshift(@key_order,$key_order[$i]); 203 splice(@key_order,$i+1,1); 204 last; 205 } 206 } 207 } 208 # Print header 209 210 $column_count=0; 211 foreach $key (@key_order) 212 { 213 $tmp=$tmp=$tot{$key}{'version'}; 214 $tmp =~ s/-cmp-$opt_cmp// if (length($opt_cmp)); 215 $column_count++; 216 printf "%2d %-40.40s: %s %s\n", $column_count, $tmp, 217 $tot{$key}{'server'}, $tot{$key}{'arguments'}; 218 print " $tot{$key}{'comments'}\n" 219 if ($tot{$key}{'comments'} =~ /\w+/); 220 } 221 222 print "\n"; 223 224 $namewidth=($opt_skip_count && !$opt_verbose) ? 29 : 36; 225 $colwidth= $opt_relative ? 10 : 7; 226 $count_width=7; 227 $colwidth+=$count_width if ($opt_verbose); 228 229 print_sep("="); 230 printf "%-$namewidth.${namewidth}s${bar}", "Operation"; 231 $count = 1; 232 foreach $key (@key_order) 233 { 234 printf "%${colwidth}d${bar}", $count; 235 $count++; 236 } 237 printf "\n%-$namewidth.${namewidth}s${bar}", ""; 238 foreach $key (@key_order) 239 { 240 $ver=$tot{$key}{'version'}; 241 $ver =~ s/-[a-zA-Z0-9_\.]+-cmp-$opt-cmp$//; 242 printf "%${colwidth}.${colwidth}s${bar}", $ver; 243 } 244 print "\n"; 245 print_sep("-"); 246 print_string($opt_relative ? "Relative results per test (First column is in seconds):" : "Results per test in seconds:"); 247 print_sep("-"); 248 249 foreach $key (sort {$a cmp $b} keys %op1) 250 { 251 printf "%-$namewidth.${namewidth}s${bar}", $key; 252 $first=undef(); 253 foreach $server (@key_order) 254 { 255 print_value($first,$tot{$server}{$key}->[0],undef(),$tot{$server}{$key}->[1]); 256 $first=$tot{$server}{$key}->[0] if (!defined($first)); 257 } 258 print "\n"; 259 } 260 261 print_sep("-"); 262 print_string("The results per operation:"); 263 print_sep("-"); 264 265 foreach $key (sort {$a cmp $b} keys %op) 266 { 267 next if ($key =~ /TOTALS/i); 268 $tmp=$key; 269 $count=$tot1{$key_order[0]}{$key}->[1]; 270 $tmp.= " (" . $count . ")" if (!$skip_count); 271 printf "%-$namewidth.${namewidth}s${bar}", $tmp; 272 $first=undef(); 273 foreach $server (@key_order) 274 { 275 $tmp= $count != $tot1{$server}{$key}->[1] ? "*" : ""; 276 print_value($first,$tot1{$server}{$key}->[0],$tot1{$server}{$key}->[1], 277 $tot1{$server}{$key}->[2] . $tmp); 278 $first=$tot1{$server}{$key}->[0] if (!defined($first)); 279 } 280 print "\n"; 281 } 282 283 print_sep("-"); 284 $key="TOTALS"; 285 printf "%-$namewidth.${namewidth}s${bar}", $key; 286 $first=undef(); 287 foreach $server (@key_order) 288 { 289 print_value($first,$tot1{$server}{$key}->[0],undef(), 290 $tot1{$server}{$key}->[2]); 291 $first=$tot1{$server}{$key}->[0] if (!defined($first)); 292 } 293 print "\n"; 294 print_sep("="); 295} 296 297 298sub html_output 299{ 300 my $template="template.html"; 301 my $title="MySQL | | Information | Benchmarks | Compare with $opt_cmp"; 302 my $image="info.gif"; 303 $bar=""; 304 305 open(TEMPLATE, $template) || die; 306 while (<TEMPLATE>) 307 { 308 if (/<center>/) 309 { 310 print $_; 311 print "<!---- This is AUTOMATICALLY Generated. Do not edit here! ---->\n"; 312 } 313 elsif (/TITLE:SUBTITLE/) 314 { 315 s|TITLE:SUBTITLE|$title|; 316 print $_; 317 } 318 elsif (/TITLE:COMPARE/) 319 { 320 s|TITLE:COMPARE|$opt_cmp|; 321 print $_; 322 } 323 elsif (/ subchapter name /) 324 { 325 # Nothing here for now 326 print $_; 327 } 328 elsif (/ text of chapter /) 329 { 330 print $_; 331 print_html_body(); 332 } 333 else 334 { 335 print $_; 336 } 337 } 338 close(TEMPLATE); 339} 340 341 342sub print_html_body 343{ 344 my ($title,$count,$key); 345 print <<EOF; 346<center> 347<font size=+4><b>MySQL Benchmark Results</b></font><br> 348<font size=+1><b>Compare with $opt_cmp</b></font><p><p> 349</center> 350This is the result file of the different benchmark tests. 351<p> 352 353The number in () after each tests shows how many SQL commands the particular 354test did. As one test may have many different parameters this gives only 355a rough picture of what was done. Check the source for more information. 356<p> 357Keep in mind that one can\'t compare benchmarks run with different --cmp 358options. The --cmp options sets the all limits according to the worst 359limit for all server in the benchmark. 360<p> 361Numbers marked with '+' are estimated according to previous runs because 362the query took longer than a given time-limit to finish. The estimation 363shouldn\'t be far from the real result thought. 364<p> 365Numbers marked with '?' contains results that gave wrong result. This can only 366be used as an indication of how long it took for the server to produce a wrong 367result :) 368<p> 369Hope this will give you some idea how each db is performing at what thing .... 370<br> 371Hope you like it .... Luuk & Monty (1997) 372<p><p> 373EOF 374 375 if ($opt_relative) 376 { 377 print "Column 1 is in seconds. All other columns are presented relative<br>\n"; 378 print "to this. 1.00 is the same, bigger numbers indicates slower<p>\n\n"; 379 } 380 381 if (length($opt_cmp)) 382 { 383 print "The test was run with limits from: $opt_cmp\n\n"; 384 } 385 print "The result logs which where found and the options:<br>\n"; 386 387 # Move $opt_server first in array 388 if ($automatic_files) 389 { 390 @key_order=sort {$a cmp $b} keys %tot; 391 for ($i=0; $i <= $#key_order; $i++) 392 { 393 if ($tot{$key_order[$i]}{'version'} =~ /^$opt_server-/) 394 { 395 unshift(@key_order,$key_order[$i]); 396 splice(@key_order,$i+1,1); 397 last; 398 } 399 } 400 } 401 # Print header 402 print "<p><center><table border=1 width=100%>\n"; 403 $column_count=0; 404 foreach $key (@key_order) 405 { 406 $tmp=$tot{$key}{'version'}; 407 $tmp =~ s/-cmp-$opt_cmp// if (length($opt_cmp)); 408 $column_count++; 409# printf "<tr><td>%2d<td>%-36.36s<td>%s %s</tr>\n", $column_count, $tmp, 410 printf "<tr><td>%2d</td><td>%s</td><td>%s %s</td></tr>\n", 411 $column_count, $tmp, $tot{$key}{'server'}, $tot{$key}{'arguments'}; 412 print "<tr><td colspan=3>$tot{$key}{'comments'}</td></tr>\n" 413 if ($tot{$key}{'comments'} =~ /\w+/); 414 } 415 416 print "</table></center><p><center><table border=1 width=100%>\n"; 417 418 $namewidth=$opt_skip_count ? 22 :29; 419 $colwidth= $opt_relative ? 10 : 7; 420 $count_width=7; 421 422 printf "<tr><td><b>%s</b></td>\n", "Operation"; 423 $count = 1; 424 foreach $key (@key_order) 425 { 426 $ver=$tot{$key}{'version'}; 427 printf "<td align=center><b>%d", $count; 428 printf "<br>%${colwidth}.${colwidth}s</b></td>\n", substr($ver,0,index($ver,"-")); 429 $count++; 430 } 431 print "</tr>\n"; 432 $title = $opt_relative ? "Relative results per test (First column is in seconds):" : "Results per test in seconds:"; 433 printf "<tr><td colspan=%d><b>%s</b></td></tr>\n", $count, $title; 434 435 foreach $key (sort {$a cmp $b} keys %op1) 436 { 437 if (!$opt_html) 438 { 439 printf "<tr><td>%-$namewidth.${namewidth}s</td>", $key; 440 } 441 else 442 { 443 print "<tr><td>$key</td>"; 444 } 445 $first=undef(); 446 foreach $server (@key_order) 447 { 448 print_value($first,$tot{$server}{$key}->[0],undef(), 449 $tot{$server}{$key}->[1]); 450 $first=$tot{$server}{$key}->[0] if (!defined($first)); 451 } 452 print "</tr>\n"; 453 } 454 455 $title = "The results per operation:"; 456 printf "<tr><td colspan=%d><b>%s</b></td></tr>\n", $count, $title; 457 458 foreach $key (sort {$a cmp $b} keys %op) 459 { 460 next if ($key =~ /TOTALS/i); 461 $tmp=$key; 462 $tmp.= " (" . $tot1{$key_order[0]}{$key}->[1] . ")" if (!$skip_count); 463 if (!$opt_html) 464 { 465 printf "<tr><td>%-$namewidth.${namewidth}s</td>", $tmp; 466 } 467 else 468 { 469 print "<tr><td>$tmp</td>"; 470 } 471 $first=undef(); 472 foreach $server (@key_order) 473 { 474 print_value($first,$tot1{$server}{$key}->[0], 475 $tot1{$server}{$key}->[1], 476 $tot1{$server}{$key}->[2]); 477 $first=$tot1{$server}{$key}->[0] if (!defined($first)); 478 } 479 print "</tr>\n"; 480 } 481 482 $key="TOTALS"; 483 printf "<tr><td><b>%-$namewidth.${namewidth}s</b></td>", $key; 484 $first=undef(); 485 foreach $server (@key_order) 486 { 487 print_value($first,$tot1{$server}{$key}->[0],undef(), 488 $tot1{$server}{$key}->[2]); 489 $first=$tot1{$server}{$key}->[0] if (!defined($first)); 490 } 491 print "</tr>\n</table>\n"; 492} 493 494 495sub print_sep 496{ 497 my ($sep)=@_; 498 print $sep x ($namewidth + (($colwidth+1) * $column_count)+1),"\n"; 499} 500 501 502sub print_value 503{ 504 my ($first,$value,$count,$flags)=@_; 505 my ($tmp,$width); 506 507 if (defined($value)) 508 { 509 if (!defined($first) || !$opt_relative) 510 { 511 $tmp=sprintf("%.2f",$value); 512 } 513 else 514 { 515 $first=1 if ($first == 0); # Assume that it took one second instead of 0 516 $tmp= sprintf("%.2f",$value/$first); 517 } 518 if (defined($flags)) 519 { 520 $tmp="+".$tmp if ($flags =~ /\+/); 521 $tmp="?".$tmp if ($flags =~ /\?/); 522 $tmp="*".$tmp if ($flags =~ /\*/); 523 } 524 } 525 else 526 { 527 $tmp=""; 528 } 529 $width= ($opt_verbose ? $colwidth - $count_width : $colwidth); 530 if (!$opt_html) 531 { 532 $tmp= " " x ($width-length($tmp)) . $tmp if (length($tmp) < $width); 533 } 534 if ($opt_verbose) 535 { 536 if ($count) 537 { 538 $tmp.= ":" . " " x ($count_width-1-length($count)) . $count; 539 } 540 else 541 { 542 $tmp.= " " x ($count_width); 543 } 544 } 545 546 if (!$opt_html) { 547 print $tmp . "${bar}"; 548 } else { 549 print "<td align=right>$tmp</td>"; 550 } 551} 552 553 554sub print_string 555{ 556 my ($str)=@_; 557 if (!$opt_html) 558 { 559 my ($width); 560 $width=$namewidth + ($colwidth+1)*$column_count; 561 $str=substr($str,1,$width) if (length($str) > $width); 562 print($str," " x ($width - length($str)),"${bar}\n"); 563 } 564 else 565 { 566 print $str,"\n"; 567 } 568} 569 570 571sub usage 572{ 573 print <<EOF; 574$0 Ver 1.2 575 576This program parses all RUN files from old 'run-all-tests --log' scripts 577and makes a nice comparable table. 578 579$0 takes currently the following options: 580 581--help or --Information 582 Shows this help 583 584--cmp=server,server,server (Default $opt_cmp) 585Compares all runs that are done with the same --cmp options to run-all-tests. 586The most normal options are '--cmp=mysql,pg,solid' and '--cmp ""' 587 588--dir=... (Default $opt_dir) 589From which directory one should get the runs. All runs made by 590run-all-tests --log is saved in the 'output' directory. 591In the 'results' directory you may have some example runs from different 592databases. 593 594--html 595 Print the table in html format. 596 597--machine='full-machine-name' (Default $opt_machine) 598Use only runs that match this machine. 599 600--relative 601Show all numbers in times of the first server where the time for the 602first server is 1.0 603 604--same-server 605Compare all runs for --server=.... The --machine is not used in this case 606This is nice to compare how the same server runs on different machines. 607 608--server='server name' (Default $opt_server) 609Put this server in the first result column. 610 611--skip-count 612Do not write the number of tests after the test-name. 613 614--verbose 615Write the number of tests in each column. This is useful when some column 616is marked with '*'. 617EOF 618 619 exit(0); 620} 621
