1
# This program is copyright 2009-2011 Percona Inc.
2
# Feedback and improvements are welcome.
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.
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
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
# CompareQueryTimes package $Revision: 6785 $
19
# ###########################################################################
21
# Package: CompareQueryTimes
22
# CompareQueryTimes compares query execution times.
24
package CompareQueryTimes;
27
use warnings FATAL => 'all';
28
use English qw(-no_match_vars);
29
use constant MKDEBUG => $ENV{MKDEBUG} || 0;
31
Transformers->import(qw(micro_t));
34
$Data::Dumper::Indent = 1;
35
$Data::Dumper::Sortkeys = 1;
36
$Data::Dumper::Quotekeys = 0;
38
# Significant percentage increase for each bucket. For example,
39
# 1us to 4us is a 300% increase, but in reality that is not significant.
40
# But a 500% increase to 6us may be significant. In the 1s+ range (last
41
# bucket), since the time is already so bad, even a 20% increase (e.g. 1s
42
# to 1.2s) is significant.
43
my @bucket_threshold = qw(500 100 100 500 50 50 20 1 );
44
# my @bucket_labels = qw(1us 10us 100us 1ms 10ms 100ms 1s 10s+);
52
# get_id - Callback used by report() to transform query to its ID
55
# CompareQueryTimes object
57
my ( $class, %args ) = @_;
58
my @required_args = qw(get_id);
59
foreach my $arg ( @required_args ) {
60
die "I need a $arg argument" unless $args{$arg};
67
return bless $self, $class;
71
my ( $self, %args ) = @_;
72
my @required_args = qw(event);
73
foreach my $arg ( @required_args ) {
74
die "I need a $arg argument" unless $args{$arg};
80
# Execute query if not already executed.
86
# event - Hashref with event attributes and values
87
# dbh - dbh on which to execute the event
90
# Hashref of event with Query_time attribute added
92
my ( $self, %args ) = @_;
93
my @required_args = qw(event dbh);
94
foreach my $arg ( @required_args ) {
95
die "I need a $arg argument" unless $args{$arg};
97
my ($event, $dbh) = @args{@required_args};
99
if ( exists $event->{Query_time} ) {
100
MKDEBUG && _d('Query already executed');
104
MKDEBUG && _d('Executing query');
105
my $query = $event->{arg};
106
my ( $start, $end, $query_time );
108
$event->{Query_time} = 0;
113
$query_time = sprintf '%.6f', $end - $start;
115
die "Failed to execute query: $EVAL_ERROR" if $EVAL_ERROR;
117
$event->{Query_time} = $query_time;
123
my ( $self, %args ) = @_;
124
my @required_args = qw(event);
125
foreach my $arg ( @required_args ) {
126
die "I need a $arg argument" unless $args{$arg};
132
# Compare executed events.
137
# Required Arguments:
138
# events - Arrayref of event hashrefs
141
# Hash of differences
143
my ( $self, %args ) = @_;
144
my @required_args = qw(events);
145
foreach my $arg ( @required_args ) {
146
die "I need a $arg argument" unless $args{$arg};
148
my ($events) = @args{@required_args};
150
my $different_query_times = 0;
152
my $event0 = $events->[0];
153
my $item = $event0->{fingerprint} || $event0->{arg};
154
my $sampleno = $event0->{sampleno} || 0;
155
my $t0 = $event0->{Query_time} || 0;
156
my $b0 = bucket_for($t0);
158
my $n_events = scalar @$events;
159
foreach my $i ( 1..($n_events-1) ) {
160
my $event = $events->[$i];
161
my $t = $event->{Query_time};
162
my $b = bucket_for($t);
166
my $diff = abs($t0 - $t);
167
$different_query_times++;
168
$self->{diffs}->{big}->{$item}->{$sampleno}
169
= [ micro_t($t0), micro_t($t), micro_t($diff) ];
170
$self->{samples}->{$item}->{$sampleno} = $event0->{arg};
173
my $inc = percentage_increase($t0, $t);
174
if ( $inc >= $bucket_threshold[$b0] ) {
176
$different_query_times++;
177
$self->{diffs}->{in_bucket}->{$item}->{$sampleno}
178
= [ micro_t($t0), micro_t($t), $inc, $bucket_threshold[$b0] ];
179
$self->{samples}->{$item}->{$sampleno} = $event0->{arg};
185
different_query_times => $different_query_times,
190
# Calculate bucket for value.
196
# Bucket number for value
199
die "I need a val" unless defined $val;
200
return 0 if $val == 0;
201
my $bucket = floor(log($val) / log(10)) + 6;
202
$bucket = $bucket > 7 ? 7 : $bucket < 0 ? 0 : $bucket;
206
# Sub: percentage_increase
207
# Calculate percentage increase between two values.
214
# Percentage increase from first to second value
215
sub percentage_increase {
217
return 0 if $x == $y;
226
return 1000; # This should trigger all buckets' thresholds.
229
return sprintf '%.2f', (($y - $x) / $x) * 100;
234
# Report differences found.
239
# Required Arguments:
240
# hosts - Arrayref of hosts
243
# Report text of differences
245
my ( $self, %args ) = @_;
246
my @required_args = qw(hosts);
247
foreach my $arg ( @required_args ) {
248
die "I need a $arg argument" unless $args{$arg};
250
my ($hosts) = @args{@required_args};
252
return unless keys %{$self->{diffs}};
254
# These columns are common to all the reports; make them just once.
258
my @host_cols = map {
259
my $col = { name => $_->{name} };
264
foreach my $diff ( qw(big in_bucket) ) {
265
my $report = "_report_diff_$diff";
266
push @reports, $self->$report(
267
query_id_col => $query_id_col,
268
host_cols => \@host_cols,
273
return join("\n", @reports);
276
# Sub: _report_diff_big
277
# Report big differences in query times.
282
# Required Arguments:
283
# query_id_col - Hashref <ReportFormat> column descriptor
284
# hosts - Arrayref of hosts
287
# Big query time diff report
288
sub _report_diff_big {
289
my ( $self, %args ) = @_;
290
my @required_args = qw(query_id_col hosts);
291
foreach my $arg ( @required_args ) {
292
die "I need a $arg argument" unless $args{$arg};
295
my $get_id = $self->{get_id};
297
return unless keys %{$self->{diffs}->{big}};
299
my $report = new ReportFormatter();
300
$report->set_title('Big query time differences');
301
$report->set_columns(
304
my $col = { name => $_->{name}, right_justify => 1 };
307
{ name => 'Difference', right_justify => 1 },
310
my $diff_big = $self->{diffs}->{big};
311
foreach my $item ( sort keys %$diff_big ) {
314
$get_id->($item) . '-' . $_,
315
@{$diff_big->{$item}->{$_}},
317
} sort { $a <=> $b } keys %{$diff_big->{$item}};
320
return $report->get_report();
323
# Sub: _report_diff_big
324
# Report smaller, "in bucket" query time differences.
329
# Required Arguments:
330
# query_id_col - Hashref <ReportFormat> column descriptor
331
# hosts - Arrayref of hosts
334
# In bucket query time diff report
335
sub _report_diff_in_bucket {
336
my ( $self, %args ) = @_;
337
my @required_args = qw(query_id_col hosts);
338
foreach my $arg ( @required_args ) {
339
die "I need a $arg argument" unless $args{$arg};
342
my $get_id = $self->{get_id};
344
return unless keys %{$self->{diffs}->{in_bucket}};
346
my $report = new ReportFormatter();
347
$report->set_title('Significant query time differences');
348
$report->set_columns(
351
my $col = { name => $_->{name}, right_justify => 1 };
354
{ name => '%Increase', right_justify => 1 },
355
{ name => '%Threshold', right_justify => 1 },
358
my $diff_in_bucket = $self->{diffs}->{in_bucket};
359
foreach my $item ( sort keys %$diff_in_bucket ) {
362
$get_id->($item) . '-' . $_,
363
@{$diff_in_bucket->{$item}->{$_}},
365
} sort { $a <=> $b } keys %{$diff_in_bucket->{$item}};
368
return $report->get_report();
372
# Return samples of queries with differences.
375
# $item - Query fingerprint
380
my ( $self, $item ) = @_;
383
foreach my $sampleno ( keys %{$self->{samples}->{$item}} ) {
384
push @samples, $sampleno, $self->{samples}->{$item}->{$sampleno};
391
# Reset internal state for another run.
395
$self->{samples} = {};
400
my ($package, undef, $line) = caller 0;
401
@_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
402
map { defined $_ ? $_ : 'undef' }
404
print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
409
# ###########################################################################
410
# End CompareQueryTimes package
411
# ###########################################################################