1# This program is copyright 2008-2011 Percona Ireland Ltd. 2# Feedback and improvements are welcome. 3# 4# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED 5# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF 6# MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE. 7# 8# This program is free software; you can redistribute it and/or modify it under 9# the terms of the GNU General Public License as published by the Free Software 10# Foundation, version 2; OR the Perl Artistic License. On UNIX and similar 11# systems, you can issue `man perlgpl' or `man perlartistic' to read these 12# licenses. 13# 14# You should have received a copy of the GNU General Public License along with 15# this program; if not, write to the Free Software Foundation, Inc., 59 Temple 16# Place, Suite 330, Boston, MA 02111-1307 USA. 17# ########################################################################### 18# QueryReportFormatter package 19# ########################################################################### 20{ 21# Package: QueryReportFormatter 22# QueryReportFormatter is used primarily by mk-query-digest to print reports. 23# The main sub is print_reports() which prints the various reports for 24# mk-query-digest --report-format. Each report is produced in a sub of 25# the same name; e.g. --report-format=query_report == sub query_report(). 26# The given ea (<EventAggregator> object) is expected to be "complete"; i.e. 27# fully aggregated and $ea->calculate_statistical_metrics() already called. 28# Subreports "profile" and "prepared" require the ReportFormatter module, 29# which is also in mk-query-digest. 30package QueryReportFormatter; 31 32use Lmo; 33use English qw(-no_match_vars); 34use POSIX qw(floor); 35 36Transformers->import(qw( 37 shorten micro_t parse_timestamp unix_timestamp make_checksum percentage_of 38 crc32 39)); 40 41use constant PTDEBUG => $ENV{PTDEBUG} || 0; 42use constant LINE_LENGTH => 74; 43use constant MAX_STRING_LENGTH => 10; 44 45{ local $EVAL_ERROR; eval { require Quoter } }; 46{ local $EVAL_ERROR; eval { require ReportFormatter } }; 47 48# Sub: new 49# 50# Parameters: 51# %args - Required arguments 52# 53# Required Arguments: 54# OptionParser - <OptionParser> object 55# QueryRewriter - <QueryRewriter> object 56# Quoter - <Quoter> object 57# 58# Optional arguments: 59# QueryReview - <QueryReview> object used in <query_report()> 60# dbh - dbh used in <explain_report()> 61# 62# Returns: 63# QueryReportFormatter object 64has Quoter => ( 65 is => 'ro', 66 isa => 'Quoter', 67 default => sub { Quoter->new() }, 68); 69 70has label_width => ( 71 is => 'ro', 72 isa => 'Int', 73); 74 75has global_headers => ( 76 is => 'ro', 77 isa => 'ArrayRef', 78 default => sub { [qw( total min max avg 95% stddev median)] }, 79); 80 81has event_headers => ( 82 is => 'ro', 83 isa => 'ArrayRef', 84 default => sub { [qw(pct total min max avg 95% stddev median)] }, 85); 86 87has show_all => ( 88 is => 'ro', 89 isa => 'HashRef', 90 default => sub { {} }, 91); 92 93has ReportFormatter => ( 94 is => 'ro', 95 isa => 'ReportFormatter', 96 builder => '_build_report_formatter', 97); 98 99sub _build_report_formatter { 100 return ReportFormatter->new( 101 line_width => LINE_LENGTH, 102 extend_right => 1, 103 ); 104} 105 106sub BUILDARGS { 107 my $class = shift; 108 my $args = $class->SUPER::BUILDARGS(@_); 109 110 foreach my $arg ( qw(OptionParser QueryRewriter) ) { 111 die "I need a $arg argument" unless $args->{$arg}; 112 } 113 114 # If ever someone wishes for a wider label width. 115 my $label_width = $args->{label_width} ||= 12; 116 PTDEBUG && _d('Label width:', $label_width); 117 118 my $o = delete $args->{OptionParser}; 119 my $self = { 120 %$args, 121 options => { 122 shorten => 1024, 123 report_all => $o->get('report-all'), 124 report_histogram => $o->get('report-histogram'), 125 output => $o->got('output') ? $o->get('output') : '', 126 }, 127 num_format => '# %1$-'.$label_width.'s %2$3s %3$7s %4$7s %5$7s %6$7s %7$7s %8$7s %9$7s', 128 bool_format => '# %1$-'.$label_width.'s %2$3d%% yes, %3$3d%% no', 129 string_format => '# %1$-'.$label_width.'s %2$s', 130 # MySQL 8 doesn't support partitions. 131 # sub explain_report can handle the error but we need to know if /*!50100 PARTITIONS */ 132 # was used or not, to write the correct report headers 133 no_partitions => 0, 134 hidden_attrib => { # Don't sort/print these attribs in the reports. 135 arg => 1, # They're usually handled specially, or not 136 fingerprint => 1, # printed at all. 137 pos_in_log => 1, 138 ts => 1, 139 }, 140 }; 141 if (!defined($self->{max_hostname_length})) { 142 $self->{max_hostname_length} = MAX_STRING_LENGTH; 143 } 144 if (!defined($self->{max_line_length})) { 145 $self->{max_line_length} = LINE_LENGTH; 146 } 147 return $self; 148} 149 150# Arguments: 151# * reports arrayref: reports to print 152# * ea obj: EventAggregator 153# * worst arrayref: worst items 154# * orderby scalar: attrib worst items ordered by 155# * groupby scalar: attrib worst items grouped by 156# Optional arguments: 157# * other arrayref: other items (that didn't make it into top worst) 158# * files arrayref: files read for input 159# * group hashref: don't add blank line between these reports 160# if they appear together 161# Prints the given reports (rusage, heade (global), query_report, etc.) in 162# the given order. These usually come from mk-query-digest --report-format. 163# Most of the required args are for header() and query_report(). 164sub print_reports { 165 my ( $self, %args ) = @_; 166 foreach my $arg ( qw(reports ea worst orderby groupby) ) { 167 die "I need a $arg argument" unless exists $args{$arg}; 168 } 169 my $reports = $args{reports}; 170 my $group = $args{group}; 171 my $last_report; 172 173 foreach my $report ( @$reports ) { 174 PTDEBUG && _d('Printing', $report, 'report'); 175 my $report_output = $self->$report(%args); 176 if ( $report_output ) { 177 print "\n" 178 if !$last_report || !($group->{$last_report} && $group->{$report}); 179 print $report_output; 180 } 181 else { 182 PTDEBUG && _d('No', $report, 'report'); 183 } 184 $last_report = $report; 185 } 186 187 return; 188} 189 190sub rusage { 191 my ( $self ) = @_; 192 my ( $rss, $vsz, $user, $system ) = ( 0, 0, 0, 0 ); 193 my $rusage = ''; 194 eval { 195 my $mem = `ps -o rss,vsz -p $PID 2>&1`; 196 ( $rss, $vsz ) = $mem =~ m/(\d+)/g; 197 ( $user, $system ) = times(); 198 $rusage = sprintf "# %s user time, %s system time, %s rss, %s vsz\n", 199 micro_t( $user, p_s => 1, p_ms => 1 ), 200 micro_t( $system, p_s => 1, p_ms => 1 ), 201 shorten( ($rss || 0) * 1_024 ), 202 shorten( ($vsz || 0) * 1_024 ); 203 }; 204 if ( $EVAL_ERROR ) { 205 PTDEBUG && _d($EVAL_ERROR); 206 } 207 return $rusage ? $rusage : "# Could not get rusage\n"; 208} 209 210sub date { 211 my ( $self ) = @_; 212 return "# Current date: " . (scalar localtime) . "\n"; 213} 214 215sub hostname { 216 my ( $self ) = @_; 217 my $hostname = `hostname`; 218 if ( $hostname ) { 219 chomp $hostname; 220 return "# Hostname: $hostname\n"; 221 } 222 return; 223} 224 225sub files { 226 my ( $self, %args ) = @_; 227 if ( $args{files} ) { 228 return "# Files: " . join(', ', map { $_->{name} } @{$args{files}}) . "\n"; 229 } 230 return; 231} 232 233# Arguments: 234# * ea obj: EventAggregator 235# * orderby scalar: attrib items ordered by 236# Optional arguments: 237# * select arrayref: attribs to print, mostly for testing 238# Print a report about the global statistics in the EventAggregator. 239# Formerly called "global_report()." 240sub header { 241 my ( $self, %args ) = @_; 242 foreach my $arg ( qw(ea orderby) ) { 243 die "I need a $arg argument" unless defined $args{$arg}; 244 } 245 my $ea = $args{ea}; 246 my $orderby = $args{orderby}; 247 my $results = $ea->results(); 248 my @result; 249 250 # Get global count 251 my $global_cnt = $results->{globals}->{$orderby}->{cnt} || 0; 252 253 # Calculate QPS (queries per second) by looking at the min/max timestamp. 254 my ($qps, $conc) = (0, 0); 255 if ( $global_cnt && $results->{globals}->{ts} 256 && ($results->{globals}->{ts}->{max} || '') 257 gt ($results->{globals}->{ts}->{min} || '') 258 ) { 259 eval { 260 my $min = parse_timestamp($results->{globals}->{ts}->{min}); 261 my $max = parse_timestamp($results->{globals}->{ts}->{max}); 262 my $diff = unix_timestamp($max) - unix_timestamp($min); 263 $qps = $global_cnt / ($diff || 1); 264 $conc = $results->{globals}->{$args{orderby}}->{sum} / $diff; 265 }; 266 } 267 268 # First line 269 PTDEBUG && _d('global_cnt:', $global_cnt, 'unique:', 270 scalar keys %{$results->{classes}}, 'qps:', $qps, 'conc:', $conc); 271 my $line = sprintf( 272 '# Overall: %s total, %s unique, %s QPS, %sx concurrency ', 273 shorten($global_cnt, d=>1_000), 274 shorten(scalar keys %{$results->{classes}}, d=>1_000), 275 shorten($qps || 0, d=>1_000), 276 shorten($conc || 0, d=>1_000)); 277 $line .= ('_' x (LINE_LENGTH - length($line) + $self->label_width() - 12)); 278 push @result, $line; 279 280 # Second line: time range 281 if ( my $ts = $results->{globals}->{ts} ) { 282 my $time_range = $self->format_time_range($ts) || "unknown"; 283 push @result, "# Time range: $time_range"; 284 } 285 286 # Third line: rate limiting, if any 287 if ( $results->{globals}->{rate_limit} ) { 288 print "# Rate limits apply\n"; 289 } 290 291 # Global column headers 292 push @result, $self->make_global_header(); 293 294 # Sort the attributes, removing any hidden attributes. 295 my $attribs = $self->sort_attribs( $ea ); 296 297 foreach my $type ( qw(num innodb) ) { 298 # Add "InnoDB:" sub-header before grouped InnoDB_* attributes. 299 if ( $type eq 'innodb' && @{$attribs->{$type}} ) { 300 push @result, "# InnoDB:"; 301 }; 302 303 NUM_ATTRIB: 304 foreach my $attrib ( @{$attribs->{$type}} ) { 305 next unless exists $results->{globals}->{$attrib}; 306 my $store = $results->{globals}->{$attrib}; 307 my $metrics = $ea->stats()->{globals}->{$attrib}; 308 my $func = $attrib =~ m/time|wait$/ ? \µ_t : \&shorten; 309 my @values = ( 310 @{$store}{qw(sum min max)}, 311 $store->{sum} / $store->{cnt}, 312 @{$metrics}{qw(pct_95 stddev median)}, 313 ); 314 @values = map { defined $_ ? $func->($_) : '' } @values; 315 316 push @result, 317 sprintf $self->{num_format}, 318 $self->make_label($attrib), '', @values; 319 } 320 } 321 322 if ( @{$attribs->{bool}} ) { 323 push @result, "# Boolean:"; 324 my $printed_bools = 0; 325 BOOL_ATTRIB: 326 foreach my $attrib ( @{$attribs->{bool}} ) { 327 next unless exists $results->{globals}->{$attrib}; 328 329 my $store = $results->{globals}->{$attrib}; 330 if ( $store->{sum} > 0 ) { 331 push @result, 332 sprintf $self->{bool_format}, 333 $self->make_label($attrib), $self->bool_percents($store); 334 $printed_bools = 1; 335 } 336 } 337 pop @result unless $printed_bools; 338 } 339 340 return join("\n", map { s/\s+$//; $_ } @result) . "\n"; 341} 342 343sub query_report_values { 344 my ($self, %args) = @_; 345 foreach my $arg ( qw(ea worst orderby groupby) ) { 346 die "I need a $arg argument" unless defined $arg; 347 } 348 my $ea = $args{ea}; 349 my $groupby = $args{groupby}; 350 my $worst = $args{worst}; 351 352 my $q = $self->Quoter; 353 my $qv = $self->{QueryReview}; 354 my $qr = $self->{QueryRewriter}; 355 356 my @values; 357 # Print each worst item: its stats/metrics (sum/min/max/95%/etc.), 358 # Query_time distro chart, tables, EXPLAIN, fingerprint, etc. 359 # Items are usually unique queries/fingerprints--depends on how 360 # the events were grouped. 361 ITEM: 362 foreach my $top_event ( @$worst ) { 363 my $item = $top_event->[0]; 364 my $reason = $args{explain_why} ? $top_event->[1] : ''; 365 my $rank = $top_event->[2]; 366 my $stats = $ea->results->{classes}->{$item}; 367 my $sample = $ea->results->{samples}->{$item}; 368 my $samp_query = ($self->{options}->{output} eq 'secure-slowlog') ? $sample->{fingerprint} || '' : $sample->{arg} || ''; 369 370 my %item_vals = ( 371 item => $item, 372 samp_query => $samp_query, 373 rank => ($rank || 0), 374 reason => $reason, 375 ); 376 377 # ############################################################### 378 # Possibly skip item for --review. 379 # ############################################################### 380 my $review_vals; 381 if ( $qv ) { 382 $review_vals = $qv->get_review_info($item); 383 next ITEM if $review_vals->{reviewed_by} && !$self->{options}->{report_all}; 384 for my $col ( $qv->review_cols() ) { 385 push @{$item_vals{review_vals}}, [$col, $review_vals->{$col}]; 386 } 387 } 388 389 $item_vals{default_db} = $sample->{db} ? $sample->{db} 390 : $stats->{db}->{unq} ? keys %{$stats->{db}->{unq}} 391 : undef; 392 $item_vals{tables} = [$self->{QueryParser}->extract_tables( 393 query => $samp_query, 394 default_db => $item_vals{default_db}, 395 Quoter => $self->Quoter, 396 )]; 397 398 if ( $samp_query && ($args{variations} && @{$args{variations}}) ) { 399 $item_vals{crc} = crc32($samp_query); 400 } 401 402 push @values, \%item_vals; 403 } 404 return \@values; 405} 406 407# Arguments: 408# * ea obj: EventAggregator 409# * worst arrayref: worst items 410# * orderby scalar: attrib worst items ordered by 411# * groupby scalar: attrib worst items grouped by 412# Optional arguments: 413# * select arrayref: attribs to print, mostly for test 414# * explain_why bool: print reason why item is reported 415# * print_header bool: "Report grouped by" header 416sub query_report { 417 my ( $self, %args ) = @_; 418 419 my $ea = $args{ea}; 420 my $groupby = $args{groupby}; 421 my $report_values = $self->query_report_values(%args); 422 423 my $qr = $self->{QueryRewriter}; 424 425 my $report = ''; 426 427 if ( $args{print_header} ) { 428 $report .= "# " . ( '#' x 72 ) . "\n" 429 . "# Report grouped by $groupby\n" 430 . '# ' . ( '#' x 72 ) . "\n\n"; 431 } 432 433 # Sort the attributes, removing any hidden attributes. 434 my $attribs = $self->sort_attribs( $ea ); 435 436 # Print each worst item: its stats/metrics (sum/min/max/95%/etc.), 437 # Query_time distro chart, tables, EXPLAIN, fingerprint, etc. 438 # Items are usually unique queries/fingerprints--depends on how 439 # the events were grouped. 440 ITEM: 441 foreach my $vals ( @$report_values ) { 442 my $item = $vals->{item}; 443 # ############################################################### 444 # Print the standard query analysis report. 445 # ############################################################### 446 $report .= "\n" if $vals->{rank} > 1; # space between each event report 447 $report .= $self->event_report( 448 %args, 449 item => $item, 450 sample => $ea->results->{samples}->{$item}, 451 rank => $vals->{rank}, 452 reason => $vals->{reason}, 453 attribs => $attribs, 454 db => $vals->{default_db}, 455 ); 456 457 if ( $self->{options}->{report_histogram} ) { 458 $report .= $self->chart_distro( 459 %args, 460 attrib => $self->{options}->{report_histogram}, 461 item => $vals->{item}, 462 ); 463 } 464 465 if ( $vals->{review_vals} ) { 466 # Print the review information that is already in the table 467 # before putting anything new into the table. 468 $report .= "# Review information\n"; 469 foreach my $elem ( @{$vals->{review_vals}} ) { 470 my ($col, $val) = @$elem; 471 if ( !$val || $val ne '0000-00-00 00:00:00' ) { # issue 202 472 $report .= sprintf "# %13s: %-s\n", $col, ($val ? $val : ''); 473 } 474 } 475 } 476 477 my $partitions_msg = $self->{no_partitions} ? '' : '/*!50100 PARTITIONS*/'; 478 if ( $groupby eq 'fingerprint' ) { 479 # Shorten it if necessary (issue 216 and 292). 480 my $samp_query = $qr->shorten($vals->{samp_query}, $self->{options}->{shorten}) 481 if $self->{options}->{shorten}; 482 483 # Print query fingerprint. 484 PTDEBUG && _d("Fingerprint\n# $vals->{item}\n"); 485 486 # Print tables used by query. 487 $report .= $self->tables_report($vals->{tables}, \%args); 488 489 # Print sample (worst) query's CRC % 1_000. We mod 1_000 because 490 # that's actually the value stored in the ea, not the full checksum. 491 # So the report will print something like, 492 # # arg crc 685 (2/66%), 159 (1/33%) 493 # Thus we want our "CRC" line to be 685 and not 18547302820. 494 if ( $vals->{crc} ) { 495 $report.= "# CRC " . ($vals->{crc} % 1_000) . "\n"; 496 } 497 498 my $log_type = $args{log_type} || ''; 499 my $mark = $args{no_v_format} ? '' : '\G'; 500 501 if ( $item =~ m/^(?:[\(\s]*select|insert|replace)/ ) { 502 if ( $item =~ m/^(?:insert|replace)/ ) { # No EXPLAIN 503 $report .= "$samp_query${mark}\n"; 504 } 505 else { 506 $report .= "# EXPLAIN $partitions_msg\n$samp_query${mark}\n"; 507 $report .= $self->explain_report($samp_query, $vals->{default_db}); 508 } 509 } 510 else { 511 $report .= "$samp_query${mark}\n"; 512 my $converted = $qr->convert_to_select($samp_query); 513 if ( $converted 514 && $converted =~ m/^[\(\s]*select/i ) { 515 # It converted OK to a SELECT 516 $report .= "# Converted for EXPLAIN\n# EXPLAIN $partitions_msg\n$converted${mark}\n"; 517 } 518 } 519 } 520 else { 521 if ( $groupby eq 'tables' ) { 522 my ( $db, $tbl ) = $self->Quoter->split_unquote($item); 523 $report .= $self->tables_report([ [$db, $tbl] ], \%args); 524 } 525 $report .= "$item\n"; 526 } 527 } 528 529 return $report; 530} 531 532# Arguments: 533# * ea obj: EventAggregator 534# * item scalar: Item in ea results 535# * orderby scalar: attribute that events are ordered by 536# Optional arguments: 537# * select arrayref: attribs to print, mostly for testing 538# * reason scalar: why this item is being reported (top|outlier) 539# * rank scalar: item rank among the worst 540# Print a report about the statistics in the EventAggregator. 541# Called by query_report(). 542sub event_report_values { 543 my ($self, %args) = @_; 544 545 my $ea = $args{ea}; 546 my $item = $args{item}; 547 my $orderby = $args{orderby}; 548 my $results = $ea->results(); 549 550 my %vals; 551 552 # Return unless the item exists in the results (it should). 553 my $store = $results->{classes}->{$item}; 554 555 return unless $store; 556 557 # Pick the first attribute to get counts 558 my $global_cnt = $results->{globals}->{$orderby}->{cnt}; 559 my $class_cnt = $store->{$orderby}->{cnt}; 560 561 # Calculate QPS (queries per second) by looking at the min/max timestamp. 562 my ($qps, $conc) = (0, 0); 563 if ( $global_cnt && $store->{ts} 564 && ($store->{ts}->{max} || '') 565 gt ($store->{ts}->{min} || '') 566 ) { 567 eval { 568 my $min = parse_timestamp($store->{ts}->{min}); 569 my $max = parse_timestamp($store->{ts}->{max}); 570 my $diff = unix_timestamp($max) - unix_timestamp($min); 571 $qps = $class_cnt / $diff; 572 $conc = $store->{$orderby}->{sum} / $diff; 573 }; 574 } 575 576 $vals{groupby} = $ea->{groupby}; 577 $vals{qps} = $qps || 0; 578 $vals{concurrency} = $conc || 0; 579 $vals{checksum} = make_checksum($item); 580 $vals{pos_in_log} = $results->{samples}->{$item}->{pos_in_log} || 0; 581 $vals{reason} = $args{reason}; 582 $vals{variance_to_mean} = do { 583 my $query_time = $ea->metrics(where => $item, attrib => 'Query_time'); 584 $query_time->{stddev}**2 / ($query_time->{avg} || 1) 585 }; 586 587 $vals{counts} = { 588 class_cnt => $class_cnt, 589 global_cnt => $global_cnt, 590 }; 591 592 if ( my $ts = $store->{ts}) { 593 $vals{time_range} = $self->format_time_range($ts) || "unknown"; 594 } 595 596 # Sort the attributes, removing any hidden attributes, if they're not 597 # already given to us. In mk-query-digest, this sub is called from 598 # query_report(), but in testing it's called directly. query_report() 599 # will sort and pass the attribs so they're not for every event. 600 my $attribs = $args{attribs}; 601 if ( !$attribs ) { 602 $attribs = $self->sort_attribs( $ea ); 603 } 604 605 $vals{attributes} = { map { $_ => [] } qw(num innodb bool string) }; 606 607 foreach my $type ( qw(num innodb) ) { 608 # Add "InnoDB:" sub-header before grouped InnoDB_* attributes. 609 610 NUM_ATTRIB: 611 foreach my $attrib ( @{$attribs->{$type}} ) { 612 next NUM_ATTRIB unless exists $store->{$attrib}; 613 my $vals = $store->{$attrib}; 614 next unless scalar %$vals; 615 616 my $pct; 617 my $func = $attrib =~ m/time|wait$/ ? \µ_t : \&shorten; 618 my $metrics = $ea->stats()->{classes}->{$item}->{$attrib}; 619 my @values = ( 620 @{$vals}{qw(sum min max)}, 621 $vals->{sum} / $vals->{cnt}, 622 @{$metrics}{qw(pct_95 stddev median)}, 623 ); 624 @values = map { defined $_ ? $func->($_) : '' } @values; 625 $pct = percentage_of( 626 $vals->{sum}, $results->{globals}->{$attrib}->{sum}); 627 628 push @{$vals{attributes}{$type}}, 629 [ $attrib, $pct, @values ]; 630 } 631 } 632 633 if ( @{$attribs->{bool}} ) { 634 BOOL_ATTRIB: 635 foreach my $attrib ( @{$attribs->{bool}} ) { 636 next BOOL_ATTRIB unless exists $store->{$attrib}; 637 my $vals = $store->{$attrib}; 638 next unless scalar %$vals; 639 640 if ( $vals->{sum} > 0 ) { 641 push @{$vals{attributes}{bool}}, 642 [ $attrib, $self->bool_percents($vals) ]; 643 } 644 } 645 } 646 647 if ( @{$attribs->{string}} ) { 648 STRING_ATTRIB: 649 foreach my $attrib ( @{$attribs->{string}} ) { 650 next STRING_ATTRIB unless exists $store->{$attrib}; 651 my $vals = $store->{$attrib}; 652 next unless scalar %$vals; 653 654 push @{$vals{attributes}{string}}, 655 [ $attrib, $vals ]; 656 } 657 } 658 659 660 return \%vals; 661} 662 663# TODO I maybe've broken the groupby report 664 665sub event_report { 666 my ( $self, %args ) = @_; 667 foreach my $arg ( qw(ea item orderby) ) { 668 die "I need a $arg argument" unless defined $args{$arg}; 669 } 670 671 my $item = $args{item}; 672 my $val = $self->event_report_values(%args); 673 my @result; 674 675 return "# No such event $item\n" unless $val; 676 677 # First line like: 678 # Query 1: 9 QPS, 0x concurrency, ID 0x7F7D57ACDD8A346E at byte 5 ________ 679 my $line = sprintf( 680 '# %s %d: %s QPS, %sx concurrency, ID 0x%s at byte %.f ', 681 ($val->{groupby} eq 'fingerprint' ? 'Query' : 'Item'), 682 $args{rank} || 0, 683 shorten($val->{qps}, d=>1_000), 684 shorten($val->{concurrency}, d=>1_000), 685 $val->{checksum}, 686 $val->{pos_in_log}, 687 ); 688 my $underscores = LINE_LENGTH - length($line) + $self->label_width() - 12; 689 if ( $underscores < 0 ) { 690 $underscores = 0; 691 } 692 $line .= ('_' x $underscores); 693 push @result, $line; 694 695 # Second line: reason why this class is being reported. 696 if ( $val->{reason} ) { 697 push @result, 698 "# This item is included in the report because it matches " 699 . ($val->{reason} eq 'top' ? '--limit.' : '--outliers.'); 700 } 701 702 # Third line: Variance-to-mean (V/M) ratio, like: 703 # Scores: V/M = 1.5 704 push @result, 705 sprintf("# Scores: V/M = %.2f", $val->{variance_to_mean} ); 706 707 # Time range 708 if ( $val->{time_range} ) { 709 push @result, "# Time range: $val->{time_range}"; 710 } 711 712 # Column header line 713 push @result, $self->make_event_header(); 714 715 # Count line 716 push @result, 717 sprintf $self->{num_format}, 'Count', 718 percentage_of($val->{counts}{class_cnt}, $val->{counts}{global_cnt}), 719 $val->{counts}{class_cnt}, 720 map { '' } (1..8); 721 722 723 my $attribs = $val->{attributes}; 724 725 foreach my $type ( qw(num innodb) ) { 726 # Add "InnoDB:" sub-header before grouped InnoDB_* attributes. 727 if ( $type eq 'innodb' && @{$attribs->{$type}} ) { 728 push @result, "# InnoDB:"; 729 }; 730 731 NUM_ATTRIB: 732 foreach my $attrib ( @{$attribs->{$type}} ) { 733 my ($attrib_name, @vals) = @$attrib; 734 push @result, 735 sprintf $self->{num_format}, 736 $self->make_label($attrib_name), @vals; 737 } 738 } 739 740 if ( @{$attribs->{bool}} ) { 741 push @result, "# Boolean:"; 742 BOOL_ATTRIB: 743 foreach my $attrib ( @{$attribs->{bool}} ) { 744 my ($attrib_name, @vals) = @$attrib; 745 push @result, 746 sprintf $self->{bool_format}, 747 $self->make_label($attrib_name), @vals; 748 } 749 } 750 751 if ( @{$attribs->{string}} ) { 752 push @result, "# String:"; 753 STRING_ATTRIB: 754 foreach my $attrib ( @{$attribs->{string}} ) { 755 my ($attrib_name, $vals) = @$attrib; 756 push @result, 757 sprintf $self->{string_format}, 758 $self->make_label($attrib_name), 759 $self->format_string_list($attrib_name, $vals, $val->{counts}{class_cnt}); 760 } 761 } 762 763 764 return join("\n", map { s/\s+$//; $_ } @result) . "\n"; 765} 766 767# Arguments: 768# * ea obj: EventAggregator 769# * item scalar: item in ea results 770# * attrib scalar: item's attribute to chart 771# Creates a chart of value distributions in buckets. Right now it bucketizes 772# into 8 buckets, powers of ten starting with .000001. 773sub chart_distro { 774 my ( $self, %args ) = @_; 775 foreach my $arg ( qw(ea item attrib) ) { 776 die "I need a $arg argument" unless defined $args{$arg}; 777 } 778 my $ea = $args{ea}; 779 my $item = $args{item}; 780 my $attrib = $args{attrib}; 781 782 my $results = $ea->results(); 783 my $store = $results->{classes}->{$item}->{$attrib}; 784 my $vals = $store->{all}; 785 return "" unless defined $vals && scalar %$vals; 786 787 # TODO: this is broken. 788 my @buck_tens = $ea->buckets_of(10); 789 my @distro = map { 0 } (0 .. 7); 790 791 # See similar code in EventAggregator::_calc_metrics() or 792 # http://code.google.com/p/maatkit/issues/detail?id=866 793 my @buckets = map { 0 } (0..999); 794 map { $buckets[$_] = $vals->{$_} } keys %$vals; 795 $vals = \@buckets; # repoint vals from given hashref to our array 796 797 map { $distro[$buck_tens[$_]] += $vals->[$_] } (1 .. @$vals - 1); 798 799 my $vals_per_mark; # number of vals represented by 1 #-mark 800 my $max_val = 0; 801 my $max_disp_width = 64; 802 my $bar_fmt = "# %5s%s"; 803 my @distro_labels = qw(1us 10us 100us 1ms 10ms 100ms 1s 10s+); 804 my @results = "# $attrib distribution"; 805 806 # Find the distro with the most values. This will set 807 # vals_per_mark and become the bar at max_disp_width. 808 foreach my $n_vals ( @distro ) { 809 $max_val = $n_vals if $n_vals > $max_val; 810 } 811 $vals_per_mark = $max_val / $max_disp_width; 812 813 foreach my $i ( 0 .. $#distro ) { 814 my $n_vals = $distro[$i]; 815 my $n_marks = $n_vals / ($vals_per_mark || 1); 816 817 # Always print at least 1 mark for any bucket that has at least 818 # 1 value. This skews the graph a tiny bit, but it allows us to 819 # see all buckets that have values. 820 $n_marks = 1 if $n_marks < 1 && $n_vals > 0; 821 822 my $bar = ($n_marks ? ' ' : '') . '#' x $n_marks; 823 push @results, sprintf $bar_fmt, $distro_labels[$i], $bar; 824 } 825 826 return join("\n", @results) . "\n"; 827} 828 829# Profile subreport (issue 381). 830# Arguments: 831# * ea obj: EventAggregator 832# * worst arrayref: worst items 833# * groupby scalar: attrib worst items grouped by 834# Optional arguments: 835# * other arrayref: other items (that didn't make it into top worst) 836# * distill_args hashref: extra args for distill() 837sub profile { 838 my ( $self, %args ) = @_; 839 foreach my $arg ( qw(ea worst groupby) ) { 840 die "I need a $arg argument" unless defined $arg; 841 } 842 my $ea = $args{ea}; 843 my $worst = $args{worst}; 844 my $other = $args{other}; 845 my $groupby = $args{groupby}; 846 847 my $qr = $self->{QueryRewriter}; 848 849 # Total response time of all events. 850 my $results = $ea->results(); 851 my $total_r = $results->{globals}->{Query_time}->{sum} || 0; 852 853 my @profiles; 854 foreach my $top_event ( @$worst ) { 855 my $item = $top_event->[0]; 856 my $rank = $top_event->[2]; 857 my $stats = $ea->results->{classes}->{$item}; 858 my $sample = $ea->results->{samples}->{$item}; 859 my $samp_query = $sample->{arg} || ''; 860 my $query_time = $ea->metrics(where => $item, attrib => 'Query_time'); 861 862 my %profile = ( 863 rank => $rank, 864 r => $stats->{Query_time}->{sum}, 865 cnt => $stats->{Query_time}->{cnt}, 866 sample => $groupby eq 'fingerprint' ? 867 $qr->distill($samp_query, %{$args{distill_args}}) : $item, 868 id => $groupby eq 'fingerprint' ? make_checksum($item) : '', 869 vmr => ($query_time->{stddev}**2) / ($query_time->{avg} || 1), 870 ); 871 872 push @profiles, \%profile; 873 } 874 875 my $report = $self->ReportFormatter(); 876 $report->title('Profile'); 877 my @cols = ( 878 { name => 'Rank', right_justify => 1, }, 879 { name => 'Query ID', }, 880 { name => 'Response time', right_justify => 1, }, 881 { name => 'Calls', right_justify => 1, }, 882 { name => 'R/Call', right_justify => 1, }, 883 { name => 'V/M', right_justify => 1, width => 5, }, 884 { name => 'Item', }, 885 ); 886 $report->set_columns(@cols); 887 888 foreach my $item ( sort { $a->{rank} <=> $b->{rank} } @profiles ) { 889 my $rt = sprintf('%10.4f', $item->{r}); 890 my $rtp = sprintf('%4.1f%%', $item->{r} / ($total_r || 1) * 100); 891 my $rc = sprintf('%8.4f', $item->{r} / $item->{cnt}); 892 my $vmr = sprintf('%4.2f', $item->{vmr}); 893 my @vals = ( 894 $item->{rank}, 895 "0x$item->{id}", 896 "$rt $rtp", 897 $item->{cnt}, 898 $rc, 899 $vmr, 900 $item->{sample}, 901 ); 902 $report->add_line(@vals); 903 } 904 905 # The last line of the profile is for all the other, non-worst items. 906 # http://code.google.com/p/maatkit/issues/detail?id=1043 907 if ( $other && @$other ) { 908 my $misc = { 909 r => 0, 910 cnt => 0, 911 }; 912 foreach my $other_event ( @$other ) { 913 my $item = $other_event->[0]; 914 my $stats = $ea->results->{classes}->{$item}; 915 $misc->{r} += $stats->{Query_time}->{sum}; 916 $misc->{cnt} += $stats->{Query_time}->{cnt}; 917 } 918 my $rt = sprintf('%10.4f', $misc->{r}); 919 my $rtp = sprintf('%4.1f%%', $misc->{r} / ($total_r || 1) * 100); 920 my $rc = sprintf('%8.4f', $misc->{r} / $misc->{cnt}); 921 $report->add_line( 922 "MISC", 923 "0xMISC", 924 "$rt $rtp", 925 $misc->{cnt}, 926 $rc, 927 '0.0', # variance-to-mean ratio is not meaningful here 928 "<".scalar @$other." ITEMS>", 929 ); 930 } 931 932 return $report->get_report(); 933} 934 935# Prepared statements subreport (issue 740). 936# Arguments: 937# * ea obj: EventAggregator 938# * worst arrayref: worst items 939# * groupby scalar: attrib worst items grouped by 940# Optional arguments: 941# * distill_args hashref: extra args for distill() 942sub prepared { 943 my ( $self, %args ) = @_; 944 foreach my $arg ( qw(ea worst groupby) ) { 945 die "I need a $arg argument" unless defined $arg; 946 } 947 my $ea = $args{ea}; 948 my $worst = $args{worst}; 949 my $groupby = $args{groupby}; 950 951 my $qr = $self->{QueryRewriter}; 952 953 my @prepared; # prepared statements 954 my %seen_prepared; # report each PREP-EXEC pair once 955 my $total_r = 0; 956 957 foreach my $top_event ( @$worst ) { 958 my $item = $top_event->[0]; 959 my $rank = $top_event->[2]; 960 my $stats = $ea->results->{classes}->{$item}; 961 my $sample = $ea->results->{samples}->{$item}; 962 my $samp_query = $sample->{arg} || ''; 963 964 $total_r += $stats->{Query_time}->{sum}; 965 next unless $stats->{Statement_id} && $item =~ m/^(?:prepare|execute) /; 966 967 # Each PREPARE (probably) has some EXECUTE and each EXECUTE (should) 968 # have some PREPARE. But these are only the top N events so we can get 969 # here a PREPARE but not its EXECUTE or vice-versa. The prepared 970 # statements report requires both so this code gets the missing pair 971 # from the ea stats. 972 my ($prep_stmt, $prep, $prep_r, $prep_cnt); 973 my ($exec_stmt, $exec, $exec_r, $exec_cnt); 974 975 if ( $item =~ m/^prepare / ) { 976 $prep_stmt = $item; 977 ($exec_stmt = $item) =~ s/^prepare /execute /; 978 } 979 else { 980 ($prep_stmt = $item) =~ s/^execute /prepare /; 981 $exec_stmt = $item; 982 } 983 984 # Report each PREPARE/EXECUTE pair once. 985 if ( !$seen_prepared{$prep_stmt}++ ) { 986 if ( exists $ea->results->{classes}->{$exec_stmt} ) { 987 $exec = $ea->results->{classes}->{$exec_stmt}; 988 $exec_r = $exec->{Query_time}->{sum}; 989 $exec_cnt = $exec->{Query_time}->{cnt}; 990 } 991 else { 992 PTDEBUG && _d('Statement prepared but not executed:', $item); 993 $exec_r = 0; 994 $exec_cnt = 0; 995 } 996 997 if ( exists $ea->results->{classes}->{$prep_stmt} ) { 998 $prep = $ea->results->{classes}->{$prep_stmt}; 999 $prep_r = $prep->{Query_time}->{sum}; 1000 $prep_cnt = scalar keys %{$prep->{Statement_id}->{unq}}, 1001 } 1002 else { 1003 PTDEBUG && _d('Statement executed but not prepared:', $item); 1004 $prep_r = 0; 1005 $prep_cnt = 0; 1006 } 1007 1008 push @prepared, { 1009 prep_r => $prep_r, 1010 prep_cnt => $prep_cnt, 1011 exec_r => $exec_r, 1012 exec_cnt => $exec_cnt, 1013 rank => $rank, 1014 sample => $groupby eq 'fingerprint' 1015 ? $qr->distill($samp_query, %{$args{distill_args}}) 1016 : $item, 1017 id => $groupby eq 'fingerprint' ? make_checksum($item) 1018 : '', 1019 }; 1020 } 1021 } 1022 1023 # Return unless there are prepared statements to report. 1024 return unless scalar @prepared; 1025 1026 my $report = $self->ReportFormatter(); 1027 $report->title('Prepared statements'); 1028 $report->set_columns( 1029 { name => 'Rank', right_justify => 1, }, 1030 { name => 'Query ID', }, 1031 { name => 'PREP', right_justify => 1, }, 1032 { name => 'PREP Response', right_justify => 1, }, 1033 { name => 'EXEC', right_justify => 1, }, 1034 { name => 'EXEC Response', right_justify => 1, }, 1035 { name => 'Item', }, 1036 ); 1037 1038 foreach my $item ( sort { $a->{rank} <=> $b->{rank} } @prepared ) { 1039 my $exec_rt = sprintf('%10.4f', $item->{exec_r}); 1040 my $exec_rtp = sprintf('%4.1f%%',$item->{exec_r}/($total_r || 1) * 100); 1041 my $prep_rt = sprintf('%10.4f', $item->{prep_r}); 1042 my $prep_rtp = sprintf('%4.1f%%',$item->{prep_r}/($total_r || 1) * 100); 1043 $report->add_line( 1044 $item->{rank}, 1045 "0x$item->{id}", 1046 $item->{prep_cnt} || 0, 1047 "$prep_rt $prep_rtp", 1048 $item->{exec_cnt} || 0, 1049 "$exec_rt $exec_rtp", 1050 $item->{sample}, 1051 ); 1052 } 1053 return $report->get_report(); 1054} 1055 1056sub make_global_header { 1057 my ( $self ) = @_; 1058 my @lines; 1059 1060 # First line: 1061 # Attribute total min max avg 95% stddev median 1062 push @lines, 1063 sprintf $self->{num_format}, "Attribute", '', @{$self->global_headers()}; 1064 1065 # Underline first line: 1066 # ========= ======= ======= ======= ======= ======= ======= ======= 1067 # The numbers 7, 7, 7, etc. are the field widths from make_header(). 1068 # Hard-coded values aren't ideal but this code rarely changes. 1069 push @lines, 1070 sprintf $self->{num_format}, 1071 (map { "=" x $_ } $self->label_width()), 1072 (map { " " x $_ } qw(3)), # no pct column in global header 1073 (map { "=" x $_ } qw(7 7 7 7 7 7 7)); 1074 1075 # End result should be like: 1076 # Attribute total min max avg 95% stddev median 1077 # ========= ======= ======= ======= ======= ======= ======= ======= 1078 return @lines; 1079} 1080 1081sub make_event_header { 1082 my ( $self ) = @_; 1083 1084 # Event headers are all the same so we just make them once. 1085 return @{$self->{event_header_lines}} if $self->{event_header_lines}; 1086 1087 my @lines; 1088 push @lines, 1089 sprintf $self->{num_format}, "Attribute", @{$self->event_headers()}; 1090 1091 # The numbers 6, 7, 7, etc. are the field widths from make_header(). 1092 # Hard-coded values aren't ideal but this code rarely changes. 1093 push @lines, 1094 sprintf $self->{num_format}, 1095 map { "=" x $_ } ($self->label_width(), qw(3 7 7 7 7 7 7 7)); 1096 1097 # End result should be like: 1098 # Attribute pct total min max avg 95% stddev median 1099 # ========= ====== ======= ======= ======= ======= ======= ======= ======= 1100 $self->{event_header_lines} = \@lines; 1101 return @lines; 1102} 1103 1104# Convert attribute names into labels 1105sub make_label { 1106 my ( $self, $val ) = @_; 1107 return '' unless $val; 1108 1109 $val =~ s/_/ /g; 1110 1111 if ( $val =~ m/^InnoDB/ ) { 1112 $val =~ s/^InnoDB //; 1113 $val = $val eq 'trx id' ? "InnoDB trxID" 1114 : substr($val, 0, $self->label_width()); 1115 } 1116 1117 $val = $val eq 'user' ? 'Users' 1118 : $val eq 'db' ? 'Databases' 1119 : $val eq 'Query time' ? 'Exec time' 1120 : $val eq 'host' ? 'Hosts' 1121 : $val eq 'Error no' ? 'Errors' 1122 : $val eq 'bytes' ? 'Query size' 1123 : $val eq 'Tmp disk tables' ? 'Tmp disk tbl' 1124 : $val eq 'Tmp table sizes' ? 'Tmp tbl size' 1125 : substr($val, 0, $self->label_width); 1126 1127 return $val; 1128} 1129 1130sub bool_percents { 1131 my ( $self, $vals ) = @_; 1132 # Since the value is either 1 or 0, the sum is the number of 1133 # all true events and the number of false events is the total 1134 # number of events minus those that were true. 1135 my $p_true = percentage_of($vals->{sum}, $vals->{cnt}); 1136 my $p_false = percentage_of(($vals->{cnt} - $vals->{sum}), $vals->{cnt}); 1137 return $p_true, $p_false; 1138} 1139 1140# Does pretty-printing for lists of strings like users, hosts, db. 1141sub format_string_list { 1142 my ( $self, $attrib, $vals, $class_cnt ) = @_; 1143 1144 # Only class result values have unq. So if unq doesn't exist, 1145 # then we've been given global values. 1146 if ( !exists $vals->{unq} ) { 1147 return ($vals->{cnt}); 1148 } 1149 1150 my $show_all = $self->show_all(); 1151 1152 my $cnt_for = $vals->{unq}; 1153 if ( 1 == keys %$cnt_for ) { 1154 my ($str) = keys %$cnt_for; 1155 # - 30 for label, spacing etc. 1156 $str = substr($str, 0, LINE_LENGTH - 30) . '...' 1157 if length $str > LINE_LENGTH - 30; 1158 return $str; 1159 } 1160 my $line = ''; 1161 my @top = sort { $cnt_for->{$b} <=> $cnt_for->{$a} || $a cmp $b } 1162 keys %$cnt_for; 1163 my $i = 0; 1164 foreach my $str ( @top ) { 1165 my $print_str; 1166 if ( $str =~ m/(?:\d+\.){3}\d+/ ) { 1167 $print_str = $str; # Do not shorten IP addresses. 1168 } 1169 elsif ( $self->{max_hostname_length} > 0 and length $str > $self->{max_hostname_length} ) { 1170 $print_str = substr($str, 0, $self->{max_hostname_length}) . '...'; 1171 } else { 1172 $print_str = $str; 1173 } 1174 my $p = percentage_of($cnt_for->{$str}, $class_cnt); 1175 $print_str .= " ($cnt_for->{$str}/$p%)"; 1176 my $trim_length = LINE_LENGTH; 1177 if ($self->{max_hostname_length} == 0 or $self->{max_hostname_length} > LINE_LENGTH) { 1178 $trim_length = $self->{max_hostname_length}; 1179 } 1180 if ( $self->{max_line_length} > 0 and !$show_all->{$attrib} ) { 1181 last if (length $line) + (length $print_str) > $self->{max_line_length} - 27; 1182 } 1183 $line .= "$print_str, "; 1184 $i++; 1185 } 1186 1187 $line =~ s/, $//; 1188 1189 if ( $i < @top ) { 1190 $line .= "... " . (@top - $i) . " more"; 1191 } 1192 1193 return $line; 1194} 1195 1196sub sort_attribs { 1197 my ( $self, $ea ) = @_; 1198 my $attribs = $ea->get_attributes(); 1199 return unless $attribs && @$attribs; 1200 PTDEBUG && _d("Sorting attribs:", @$attribs); 1201 1202 # Sort order for numeric attribs. Attribs not listed here come after these 1203 # in alphabetical order. 1204 my @num_order = qw( 1205 Query_time 1206 Exec_orig_time 1207 Transmit_time 1208 Lock_time 1209 Rows_sent 1210 Rows_examined 1211 Rows_affected 1212 Rows_read 1213 Bytes_sent 1214 Merge_passes 1215 Tmp_tables 1216 Tmp_disk_tables 1217 Tmp_table_sizes 1218 bytes 1219 ); 1220 my $i = 0; 1221 my %num_order = map { $_ => $i++ } @num_order; 1222 1223 my (@num, @innodb, @bool, @string); 1224 ATTRIB: 1225 foreach my $attrib ( @$attribs ) { 1226 next if $self->{hidden_attrib}->{$attrib}; 1227 1228 # Default type is string in EventAggregator::make_handler(). 1229 my $type = $ea->type_for($attrib) || 'string'; 1230 if ( $type eq 'num' ) { 1231 if ( $attrib =~ m/^InnoDB_/ ) { 1232 push @innodb, $attrib; 1233 } 1234 else { 1235 push @num, $attrib; 1236 } 1237 } 1238 elsif ( $type eq 'bool' ) { 1239 push @bool, $attrib; 1240 } 1241 elsif ( $type eq 'string' ) { 1242 push @string, $attrib; 1243 } 1244 else { 1245 PTDEBUG && _d("Unknown attrib type:", $type, "for", $attrib); 1246 } 1247 } 1248 1249 @num = sort { pref_sort($a, $num_order{$a}, $b, $num_order{$b}) } @num; 1250 @innodb = sort { uc $a cmp uc $b } @innodb; 1251 @bool = sort { uc $a cmp uc $b } @bool; 1252 @string = sort { uc $a cmp uc $b } @string; 1253 1254 return { 1255 num => \@num, 1256 innodb => \@innodb, 1257 string => \@string, 1258 bool => \@bool, 1259 }; 1260} 1261 1262sub pref_sort { 1263 my ( $attrib_a, $order_a, $attrib_b, $order_b ) = @_; 1264 1265 # Neither has preferred order so sort by attrib name alphabetically. 1266 if ( !defined $order_a && !defined $order_b ) { 1267 return $attrib_a cmp $attrib_b; 1268 } 1269 1270 # By have a preferred order so sort by their order. 1271 if ( defined $order_a && defined $order_b ) { 1272 return $order_a <=> $order_b; 1273 } 1274 1275 # Only one has a preferred order so sort it first. 1276 if ( !defined $order_a ) { 1277 return 1; 1278 } 1279 else { 1280 return -1; 1281 } 1282} 1283 1284# Gets an arrayref of [db, tbl] arrayrefs pairs to print out 1285sub tables_report { 1286 my ( $self, $tables_ref, $args_ref ) = @_; 1287 return '' unless @$tables_ref; 1288 my $q = $self->Quoter(); 1289 my $tables = ""; 1290 my $mark = $args_ref->{no_v_format} ? '' : '\G'; 1291 foreach my $db_tbl ( @$tables_ref ) { 1292 my ( $db, $tbl ) = @$db_tbl; 1293 $tables .= '# SHOW TABLE STATUS' 1294 . ($db ? " FROM `$db`" : '') 1295 . " LIKE '$tbl'${mark}\n"; 1296 $tables .= "# SHOW CREATE TABLE " 1297 . $q->quote(grep { $_ } @$db_tbl) 1298 . "${mark}\n"; 1299 } 1300 return $tables ? "# Tables\n$tables" : "# No tables\n"; 1301} 1302 1303sub explain_report { 1304 my ( $self, $query, $db ) = @_; 1305 return '' unless $query; 1306 1307 my $dbh = $self->{dbh}; 1308 my $q = $self->Quoter(); 1309 my $qp = $self->{QueryParser}; 1310 return '' unless $dbh && $q && $qp; 1311 1312 my $explain = ''; 1313 eval { 1314 if ( !$qp->has_derived_table($query) ) { 1315 if ( $db ) { 1316 PTDEBUG && _d($dbh, "USE", $db); 1317 $dbh->do("USE " . $q->quote($db)); 1318 } 1319 my $sth; 1320 eval { 1321 $sth = $dbh->prepare("EXPLAIN /*!50100 PARTITIONS*/ $query"); 1322 $sth->execute(); 1323 }; 1324 if ($EVAL_ERROR) { # MySQL 8.0+ doesn't support PARTITIONS 1325 $self->{no_partitions} = 1; 1326 $sth = $dbh->prepare("EXPLAIN $query"); 1327 $sth->execute(); 1328 } 1329 $sth->execute(); 1330 my $i = 1; 1331 while ( my @row = $sth->fetchrow_array() ) { 1332 $explain .= "# *************************** $i. " 1333 . "row ***************************\n"; 1334 foreach my $j ( 0 .. $#row ) { 1335 my $value_format = $sth->{NAME}->[$j] eq 'filtered' ? "%.02f" : "%s"; 1336 $explain .= sprintf "# %13s: $value_format\n", $sth->{NAME}->[$j], 1337 defined $row[$j] ? $row[$j] : 'NULL'; 1338 } 1339 $i++; # next row number 1340 } 1341 } 1342 }; 1343 if ( $EVAL_ERROR ) { 1344 PTDEBUG && _d("EXPLAIN failed:", $query, $EVAL_ERROR); 1345 } 1346 return $explain ? $explain : "# EXPLAIN failed: $EVAL_ERROR"; 1347} 1348 1349sub format_time_range { 1350 my ( $self, $vals ) = @_; 1351 my $min = parse_timestamp($vals->{min} || ''); 1352 my $max = parse_timestamp($vals->{max} || ''); 1353 1354 if ( $min && $max && $min eq $max ) { 1355 return "all events occurred at $min"; 1356 } 1357 1358 # Remove common prefix (day). 1359 my ($min_day) = split(' ', $min) if $min; 1360 my ($max_day) = split(' ', $max) if $max; 1361 if ( ($min_day || '') eq ($max_day || '') ) { 1362 (undef, $max) = split(' ', $max); 1363 } 1364 1365 return $min && $max ? "$min to $max" : ''; 1366} 1367 1368sub _d { 1369 my ($package, undef, $line) = caller 0; 1370 @_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; } 1371 map { defined $_ ? $_ : 'undef' } 1372 @_; 1373 print STDERR "# $package:$line $PID ", join(' ', @_), "\n"; 1374} 1375 1376no Lmo; 13771; 1378} 1379# ########################################################################### 1380# End QueryReportFormatter package 1381# ########################################################################### 1382