4
die "The PERCONA_TOOLKIT_BRANCH environment variable is not set.\n"
5
unless $ENV{PERCONA_TOOLKIT_BRANCH} && -d $ENV{PERCONA_TOOLKIT_BRANCH};
6
unshift @INC, "$ENV{PERCONA_TOOLKIT_BRANCH}/lib";
10
use warnings FATAL => 'all';
11
use English qw(-no_match_vars);
12
use Test::More tests => 43;
15
$Data::Dumper::Indent = 1;
16
$Data::Dumper::Sortkeys = 1;
17
$Data::Dumper::Quotekeys = 0;
20
use QueryReportFormatter;
33
my $dp = new DSNParser(opts=>$dsn_opts);
34
my $sb = new Sandbox(basedir => '/tmp', DSNParser => $dp);
35
my $dbh = $sb->get_dbh_for('master', {no_lc=>1}); # for explain sparkline
37
my ($result, $events, $expected);
40
my $qp = new QueryParser();
41
my $qr = new QueryRewriter(QueryParser=>$qp);
42
my $o = new OptionParser(description=>'qrf');
43
my $ex = new ExplainAnalyzer(QueryRewriter => $qr, QueryParser => $qp);
45
$o->get_specs("$trunk/bin/pt-query-digest");
47
my $qrf = new QueryReportFormatter(
52
ExplainAnalyzer => $ex,
55
my $ea = new EventAggregator(
56
groupby => 'fingerprint',
57
worst => 'Query_time',
59
Query_time => [qw(Query_time)],
60
Lock_time => [qw(Lock_time)],
63
Rows_sent => [qw(Rows_sent)],
64
Rows_examined => [qw(Rows_examined)],
69
isa_ok($qrf, 'QueryReportFormatter');
71
$result = $qrf->rusage();
74
qr/^# \S+ user time, \S+ system time, \S+ rss, \S+ vsz/s,
79
{ ts => '071015 21:43:52',
84
arg => "SELECT id FROM users WHERE name='foo'",
85
Query_time => '8.000652',
86
Lock_time => '0.000109',
92
{ ts => '071015 21:43:52',
98
"INSERT IGNORE INTO articles (id, body,)VALUES(3558268,'sample text')",
99
Query_time => '1.001943',
100
Lock_time => '0.000145',
106
{ ts => '071015 21:43:53',
111
arg => "SELECT id FROM users WHERE name='bar'",
112
Query_time => '1.000682',
113
Lock_time => '0.000201',
121
# Here's the breakdown of values for those three events:
123
# ATTRIBUTE VALUE BUCKET VALUE RANGE
124
# Query_time => 8.000652 326 7.700558026 range [7.700558026, 8.085585927)
125
# Query_time => 1.001943 284 0.992136979 range [0.992136979, 1.041743827)
126
# Query_time => 1.000682 284 0.992136979 range [0.992136979, 1.041743827)
127
# -------- -----------
128
# 10.003277 9.684831984
130
# Lock_time => 0.000109 97 0.000108186 range [0.000108186, 0.000113596)
131
# Lock_time => 0.000145 103 0.000144980 range [0.000144980, 0.000152229)
132
# Lock_time => 0.000201 109 0.000194287 range [0.000194287, 0.000204002)
133
# -------- -----------
134
# 0.000455 0.000447453
136
# Rows_sent => 1 284 0.992136979 range [0.992136979, 1.041743827)
138
# Rows_sent => 1 284 0.992136979 range [0.992136979, 1.041743827)
139
# -------- -----------
142
# Rows_exam => 1 284 0.992136979 range [0.992136979, 1.041743827)
144
# Rows_exam => 2 298 1.964363355, range [1.964363355, 2.062581523)
145
# -------- -----------
148
# I hand-checked these values with my TI-83 calculator.
149
# They are, without a doubt, correct.
151
foreach my $event (@$events) {
152
$event->{fingerprint} = $qr->fingerprint( $event->{arg} );
153
$ea->aggregate($event);
155
$ea->calculate_statistical_metrics(apdex_t=>1);
156
$result = $qrf->header(
158
select => [ qw(Query_time Lock_time Rows_sent Rows_examined ts) ],
159
orderby => 'Query_time',
165
"t/lib/samples/QueryReportFormatter/report006.txt",
168
'Global (header) report'
171
$result = $qrf->event_report(
173
# "users" is here to try to cause a failure
174
select => [ qw(Query_time Lock_time Rows_sent Rows_examined ts db user users) ],
175
item => 'select id from users where name=?',
177
orderby => 'Query_time',
184
"t/lib/samples/QueryReportFormatter/report007.txt",
190
$result = $qrf->chart_distro(
192
attrib => 'Query_time',
193
item => 'select id from users where name=?',
199
"t/lib/samples/QueryReportFormatter/report008.txt",
206
skip 'Wider labels not used, not tested', 1;
207
$qrf = new QueryReportFormatter(label_width => 15);
209
$result = $qrf->event_report(
211
# "users" is here to try to cause a failure
212
select => [ qw(Query_time Lock_time Rows_sent Rows_examined ts db user users) ],
213
where => 'select id from users where name=?',
215
worst => 'Query_time',
222
"t/lib/samples/QueryReportFormatter/report017.txt",
225
'Event report with wider label'
228
$qrf = new QueryReportFormatter;
231
# ########################################################################
232
# This one is all about an event that's all zeroes.
233
# ########################################################################
234
$ea = new EventAggregator(
235
groupby => 'fingerprint',
236
worst => 'Query_time',
238
Query_time => [qw(Query_time)],
239
Lock_time => [qw(Lock_time)],
242
Rows_sent => [qw(Rows_sent)],
243
Rows_examined => [qw(Rows_examined)],
252
arg => 'administrator command: Connect',
253
fingerprint => 'administrator command: Connect',
258
No_good_index_used => 'No',
259
ts => '090412 11:00:13.118191',
260
No_index_used => 'No',
270
foreach my $event (@$events) {
271
$event->{fingerprint} = $qr->fingerprint( $event->{arg} );
272
$ea->aggregate($event);
274
$ea->calculate_statistical_metrics(apdex_t=>1);
276
$result = $qrf->header(
278
select => [ qw(Query_time Lock_time Rows_sent Rows_examined ts) ],
279
orderby => 'Query_time',
285
"t/lib/samples/QueryReportFormatter/report018.txt",
288
'Global report with all zeroes'
291
$result = $qrf->event_report(
293
select => [ qw(Query_time Lock_time Rows_sent Rows_examined ts db user users) ],
294
item => 'administrator command: Connect',
296
orderby => 'Query_time',
303
"t/lib/samples/QueryReportFormatter/report009.txt",
306
'Event report with all zeroes'
309
# This used to cause illegal division by zero in some cases.
310
$result = $qrf->chart_distro(
312
attrib => 'Query_time',
313
item => 'administrator command: Connect',
319
"t/lib/samples/QueryReportFormatter/report019.txt",
322
'Chart distro with all zeroes'
325
# #############################################################################
326
# Test bool (Yes/No) pretty printing.
327
# #############################################################################
329
{ ts => '071015 21:43:52',
331
arg => "SELECT id FROM users WHERE name='foo'",
332
Query_time => '8.000652',
333
Lock_time => '0.002300',
336
InnoDB_IO_r_bytes => 2,
337
InnoDB_pages_distinct => 20,
339
{ ts => '071015 21:43:52',
341
arg => "SELECT id FROM users WHERE name='foo'",
342
Query_time => '1.001943',
343
Lock_time => '0.002320',
346
InnoDB_IO_r_bytes => 2,
347
InnoDB_pages_distinct => 18,
349
{ ts => '071015 21:43:53',
351
arg => "SELECT id FROM users WHERE name='bar'",
352
Query_time => '1.000682',
353
Lock_time => '0.003301',
356
InnoDB_IO_r_bytes => 3,
357
InnoDB_pages_distinct => 11,
361
$ea = new EventAggregator(
362
groupby => 'fingerprint',
363
worst => 'Query_time',
364
ignore_attributes => [qw(arg cmd)],
366
foreach my $event (@$events) {
367
$event->{fingerprint} = $qr->fingerprint( $event->{arg} );
368
$ea->aggregate($event);
370
$ea->calculate_statistical_metrics();
371
$result = $qrf->header(
373
orderby => 'Query_time',
379
"t/lib/samples/QueryReportFormatter/report020.txt",
382
'Bool (Yes/No) pretty printer'
386
{ ts => '071015 21:43:52',
388
arg => "SELECT id FROM users WHERE name='foo'",
389
Query_time => '8.000652',
390
Lock_time => '0.002300',
393
InnoDB_IO_r_bytes => 2,
394
InnoDB_pages_distinct => 20,
397
$ea->reset_aggregated_data();
398
foreach my $event (@$events) {
399
$event->{fingerprint} = $qr->fingerprint( $event->{arg} );
400
$ea->aggregate($event);
402
$ea->calculate_statistical_metrics();
404
$result = $qrf->header(
406
orderby => 'Query_time',
411
"t/lib/samples/QueryReportFormatter/report023.txt",
414
'No Boolean sub-header in gobal report when all zero bools'
417
$result = $qrf->event_report(
419
orderby => 'Query_time',
420
item => 'select id from users where name=?',
425
"t/lib/samples/QueryReportFormatter/report024.txt",
428
'No Boolean sub-header in event report with all zero bools'
431
# #############################################################################
432
# Test attrib sorting.
433
# #############################################################################
435
# This test uses the $ea from the Bool pretty printer test above.
436
my $sorted = $qrf->sort_attribs($ea->get_attributes(), $ea);
440
num => [qw(Query_time Lock_time)],
441
innodb => [qw(InnoDB_IO_r_bytes InnoDB_pages_distinct)],
442
bool => [qw(Filesort QC_Hit)],
446
) or print Dumper($sorted);
448
# Make an ea with most of the common attributes.
450
{ ts => '071015 21:43:52',
452
arg => "SELECT id FROM users WHERE name='foo'",
453
bytes => length("SELECT id FROM users WHERE name='foo'"),
458
Query_time => '8.000652',
459
Lock_time => '0.002300',
461
Rows_examined => 5000000,
462
Rows_read => 123456789,
466
InnoDB_IO_r_bytes => 2,
467
InnoDB_pages_distinct => 20,
471
$ea = new EventAggregator(
472
groupby => 'fingerprint',
473
worst => 'Query_time',
474
ignore_attributes => [qw(arg cmd)],
475
type_for => { Thread_id => 'string' },
477
foreach my $event (@$events) {
478
$event->{fingerprint} = $qr->fingerprint( $event->{arg} );
479
$ea->aggregate($event);
481
$ea->calculate_statistical_metrics();
483
$sorted = $qrf->sort_attribs($ea->get_attributes(), $ea);
487
num => [qw(Query_time Lock_time Rows_sent Rows_examined Rows_read Merge_passes bytes)],
488
innodb => [qw(InnoDB_IO_r_bytes InnoDB_pages_distinct)],
489
bool => [qw(Filesort QC_Hit)],
490
string => [qw(db host Thread_id user)],
492
'more sort_attribs()'
493
) or print Dumper($sorted);
495
# ############################################################################
496
# Test that --[no]zero-bool removes 0% vals.
497
# ############################################################################
499
{ ts => '071015 21:43:52',
501
arg => "SELECT id FROM users WHERE name='foo'",
502
Query_time => '8.000652',
503
Lock_time => '0.002300',
507
{ ts => '071015 21:43:52',
509
arg => "SELECT id FROM users WHERE name='foo'",
510
Query_time => '1.001943',
511
Lock_time => '0.002320',
515
{ ts => '071015 21:43:53',
517
arg => "SELECT id FROM users WHERE name='bar'",
518
Query_time => '1.000682',
519
Lock_time => '0.003301',
525
$ea = new EventAggregator(
526
groupby => 'fingerprint',
527
worst => 'Query_time',
528
ignore_attributes => [qw(arg cmd)],
530
foreach my $event (@$events) {
531
$event->{fingerprint} = $qr->fingerprint( $event->{arg} );
532
$ea->aggregate($event);
534
$ea->calculate_statistical_metrics();
535
$result = $qrf->header(
537
# select => [ $ea->get_attributes() ],
538
orderby => 'Query_time',
545
"t/lib/samples/QueryReportFormatter/report021.txt",
551
# #############################################################################
552
# Issue 458: mk-query-digest Use of uninitialized value in division (/) at
554
# #############################################################################
556
my $p = new SlowLogParser();
558
sub report_from_file {
559
my $ea2 = new EventAggregator(
560
groupby => 'fingerprint',
561
worst => 'Query_time',
564
$file = "$trunk/$file";
567
push @callbacks, sub {
569
my $group_by_val = $event->{arg};
570
return 0 unless defined $group_by_val;
571
$event->{fingerprint} = $qr->fingerprint($group_by_val);
574
push @callbacks, sub {
578
open my $fh, "<", $file or die "Cannot open $file: $OS_ERROR";
580
next_event => sub { return <$fh>; },
581
tell => sub { return tell($fh); },
583
while ( my $e = $p->parse_event(%args) ) {
584
$_->($e) for @callbacks;
588
die $EVAL_ERROR if $EVAL_ERROR;
589
$ea2->calculate_statistical_metrics();
591
attrib => 'Query_time',
596
my ($worst, $other) = $ea2->top_events(%top_spec);
597
my $top_n = scalar @$worst;
599
foreach my $rank ( 1 .. $top_n ) {
600
$report .= $qrf->event_report(
602
# select => [ $ea2->get_attributes() ],
603
item => $worst->[$rank - 1]->[0],
605
orderby => 'Query_time',
612
# The real bug is in QueryReportFormatter, and there's nothing particularly
613
# interesting about this sample, but we just want to make sure that the
614
# timestamp prop shows up only in the one event. The bug is that it appears
616
report_from_file('t/lib/samples/slowlogs/slow029.txt');
621
'event_report() does not die on empty attributes (issue 458)'
624
# #############################################################################
625
# Test that format_string_list() truncates long strings.
626
# #############################################################################
629
{ ts => '071015 21:43:52',
631
arg => "SELECT id FROM users WHERE name='foo'",
633
foo => "Hi. I'm a very long string. I'm way over the 78 column width that we try to keep lines limited to so text wrapping doesn't make things look all funky and stuff.",
637
$ea = new EventAggregator(
638
groupby => 'fingerprint',
639
worst => 'Query_time',
640
ignore_attributes => [qw(arg cmd)],
642
foreach my $event (@$events) {
643
$event->{fingerprint} = $qr->fingerprint( $event->{arg} );
644
$ea->aggregate($event);
646
$ea->calculate_statistical_metrics();
647
$result = $qrf->event_report(
649
select => [ qw(Query_time foo) ],
650
item => 'select id from users where name=?',
652
orderby => 'Query_time',
659
"t/lib/samples/QueryReportFormatter/report010.txt",
662
'Truncate one long string'
665
$ea->reset_aggregated_data();
667
{ ts => '071015 21:43:55',
669
arg => "SELECT id FROM users WHERE name='foo'",
671
foo => "Me too! I'm a very long string yay! I'm also over the 78 column width that we try to keep lines limited to."
674
foreach my $event (@$events) {
675
$event->{fingerprint} = $qr->fingerprint( $event->{arg} );
676
$ea->aggregate($event);
678
$ea->calculate_statistical_metrics();
679
$result = $qrf->event_report(
681
select => [ qw(Query_time foo) ],
682
item => 'select id from users where name=?',
684
orderby => 'Query_time',
691
"t/lib/samples/QueryReportFormatter/report011.txt",
694
'Truncate multiple long strings'
697
$ea->reset_aggregated_data();
699
{ ts => '071015 21:43:55',
701
arg => "SELECT id FROM users WHERE name='foo'",
703
foo => 'Number 3 long string, but I\'ll exceed the line length so I\'ll only show up as "more" :-('
706
foreach my $event (@$events) {
707
$event->{fingerprint} = $qr->fingerprint( $event->{arg} );
708
$ea->aggregate($event);
710
$ea->calculate_statistical_metrics();
711
$result = $qrf->event_report(
713
select => [ qw(Query_time foo) ],
714
item => 'select id from users where name=?',
716
orderby => 'Query_time',
723
"t/lib/samples/QueryReportFormatter/report012.txt",
726
'Truncate multiple strings longer than whole line'
729
# #############################################################################
730
# Issue 478: mk-query-digest doesn't count errors and hosts right
731
# #############################################################################
733
# We decided that string attribs shouldn't be listed in the global header.
737
arg => "SELECT id FROM users WHERE name='foo'",
738
Query_time => '8.000652',
743
arg => "SELECT id FROM users WHERE name='foo'",
744
Query_time => '1.001943',
749
arg => "SELECT id FROM users WHERE name='bar'",
750
Query_time => '1.000682',
755
$ea = new EventAggregator(
756
groupby => 'fingerprint',
757
worst => 'Query_time',
758
ignore_attributes => [qw(arg cmd)],
760
foreach my $event (@$events) {
761
$event->{fingerprint} = $qr->fingerprint( $event->{arg} );
762
$ea->aggregate($event);
764
$ea->calculate_statistical_metrics();
765
$result = $qrf->header(
767
select => $ea->get_attributes(),
768
orderby => 'Query_time',
774
"t/lib/samples/QueryReportFormatter/report022.txt",
777
'No string attribs in global report (issue 478)'
780
# #############################################################################
781
# Issue 744: Option to show all Hosts
782
# #############################################################################
784
# Don't shorten IP addresses.
789
Query_time => '8.000652',
790
host => '123.123.123.456',
795
Query_time => '8.000652',
796
host => '123.123.123.789',
800
$ea = new EventAggregator(
802
worst => 'Query_time',
803
ignore_attributes => [qw(arg cmd)],
805
foreach my $event (@$events) {
806
$ea->aggregate($event);
808
$ea->calculate_statistical_metrics();
809
$result = $qrf->event_report(
811
select => [ qw(Query_time host) ],
814
orderby => 'Query_time',
820
"t/lib/samples/QueryReportFormatter/report013.txt",
826
# Add another event so we get "... N more" to make sure that IPs
827
# are still not shortened.
832
Query_time => '8.000652',
833
host => '123.123.123.999',
835
$ea->aggregate($events->[-1]);
836
$ea->calculate_statistical_metrics();
837
$result = $qrf->event_report(
839
select => [ qw(Query_time host) ],
842
orderby => 'Query_time',
848
"t/lib/samples/QueryReportFormatter/report014.txt",
851
"IPs not shortened with more"
855
@ARGV = qw(--show-all host);
857
$result = $qrf->event_report(
859
select => [ qw(Query_time host) ],
862
orderby => 'Query_time',
868
"t/lib/samples/QueryReportFormatter/report015.txt",
874
# #############################################################################
875
# Issue 948: mk-query-digest treats InnoDB_rec_lock_wait value as number
877
# #############################################################################
883
Query_time => '8.000652',
884
InnoDB_rec_lock_wait => 0.001,
885
InnoDB_IO_r_wait => 0.002,
886
InnoDB_queue_wait => 0.003,
890
$ea = new EventAggregator(
892
worst => 'Query_time',
893
ignore_attributes => [qw(arg cmd)],
895
foreach my $event (@$events) {
896
$ea->aggregate($event);
898
$ea->calculate_statistical_metrics();
899
$result = $qrf->event_report(
901
select => [ qw(Query_time InnoDB_rec_lock_wait InnoDB_IO_r_wait InnoDB_queue_wait) ],
904
orderby => 'Query_time',
910
"t/lib/samples/QueryReportFormatter/report016.txt",
913
"_wait attribs treated as times (issue 948)"
916
# #############################################################################
918
# #############################################################################
922
arg => "select col from tbl where id=42",
923
fingerprint => "select col from tbl where id=?",
924
Query_time => '1.000652',
925
Lock_time => '0.001292',
926
ts => '071015 21:43:52',
931
$ea = new EventAggregator(
932
groupby => 'fingerprint',
933
worst => 'Query_time',
935
foreach my $event ( @$events ) {
936
$ea->aggregate($event);
938
$ea->calculate_statistical_metrics(apdex_t=>1);
940
# Reset opts in case anything above left something set.
944
# Normally, the report subs will make their own ReportFormatter but
945
# that package isn't visible to QueryReportFormatter right now so we
946
# make ReportFormatters and pass them in. Since ReporFormatters can't
947
# be shared, we can only test one subreport at a time, else the
948
# prepared statements subreport will reuse/reprint stuff from the
949
# profile subreport. And the line width is 82 because that's the new
950
# default to accommodate the EXPLAIN sparkline (issue 1141).
951
my $report = new ReportFormatter(line_width=>82);
952
$qrf->set_report_formatter(report=>'profile', formatter=>$report);
955
sub { $qrf->print_reports(
956
reports => [qw(header query_report profile)],
958
worst => [['select col from tbl where id=?','top',1]],
960
orderby => 'Query_time',
961
groupby => 'fingerprint',
962
variations => [qw(arg)],
964
"t/lib/samples/QueryReportFormatter/report001.txt",
966
"print_reports(header, query_report, profile)"
969
$report = new ReportFormatter(line_width=>82);
970
$qrf->set_report_formatter(report=>'profile', formatter=>$report);
973
sub { $qrf->print_reports(
974
reports => [qw(profile query_report header)],
976
worst => [['select col from tbl where id=?','top',1]],
977
orderby => 'Query_time',
978
groupby => 'fingerprint',
979
variations => [qw(arg)],
981
"t/lib/samples/QueryReportFormatter/report003.txt",
983
"print_reports(profile, query_report, header)",
988
Query_time => '0.000286',
990
arg => 'PREPARE SELECT i FROM d.t WHERE i=?',
991
fingerprint => 'prepare select i from d.t where i=?',
996
ts => '091208 09:23:49.637394',
1000
Query_time => '0.030281',
1002
arg => 'EXECUTE SELECT i FROM d.t WHERE i="3"',
1003
fingerprint => 'execute select i from d.t where i=?',
1008
ts => '091208 09:23:49.637892',
1012
$ea = new EventAggregator(
1013
groupby => 'fingerprint',
1014
worst => 'Query_time',
1016
foreach my $event ( @$events ) {
1017
$ea->aggregate($event);
1019
$ea->calculate_statistical_metrics();
1020
$report = new ReportFormatter(
1024
$qrf->set_report_formatter(report=>'prepared', formatter=>$report);
1028
$qrf->print_reports(
1029
reports => ['query_report','prepared'],
1032
['execute select i from d.t where i=?', 'top',1],
1033
['prepare select i from d.t where i=?', 'top',2],
1035
orderby => 'Query_time',
1036
groupby => 'fingerprint',
1037
variations => [qw(arg)],
1040
"t/lib/samples/QueryReportFormatter/report002.txt",
1042
"print_reports(query_report, prepared)"
1047
Query_time => '1.030281',
1048
arg => 'update foo set col=1 where 1',
1049
fingerprint => 'update foo set col=? where ?',
1053
ts => '091208 09:23:49.637892',
1055
$ea = new EventAggregator(
1056
groupby => 'fingerprint',
1057
worst => 'Query_time',
1059
foreach my $event ( @$events ) {
1060
$ea->aggregate($event);
1062
$ea->calculate_statistical_metrics();
1063
$report = new ReportFormatter(
1067
$qrf->set_report_formatter(report=>'profile', formatter=>$report);
1071
$qrf->print_reports(
1072
reports => ['profile'],
1075
['update foo set col=? where ?', 'top',1]
1078
['execute select i from d.t where i=?','misc',2],
1079
['prepare select i from d.t where i=?','misc',3],
1081
orderby => 'Query_time',
1082
groupby => 'fingerprint',
1085
"t/lib/samples/QueryReportFormatter/report004.txt",
1087
"MISC items in profile"
1090
# #############################################################################
1092
# #############################################################################
1094
skip 'Cannot connect to sandbox master', 3 unless $dbh;
1095
$sb->load_file('master', "t/lib/samples/QueryReportFormatter/table.sql");
1097
@ARGV = qw(--explain F=/tmp/12345/my.sandbox.cnf);
1100
my $qrf = new QueryReportFormatter(
1102
QueryRewriter => $qr,
1106
ExplainAnalyzer => $ex,
1109
my $explain = load_file(
1110
$sandbox_version ge '5.1'
1111
? "t/lib/samples/QueryReportFormatter/report025.txt"
1112
: "t/lib/samples/QueryReportFormatter/report026.txt");
1115
$qrf->explain_report("select * from qrf.t where i=2", 'qrf'),
1120
my $arg = "select t1.i from t as t1 join t as t2 where t1.i < t2.i and t1.v is not null order by t1.i";
1121
my $fingerprint = $qr->fingerprint($arg);
1125
Query_time => '0.000286',
1127
fingerprint => $fingerprint,
1128
bytes => length $arg,
1132
ts => '091208 09:23:49.637394',
1135
$ea = new EventAggregator(
1136
groupby => 'fingerprint',
1137
worst => 'Query_time',
1139
foreach my $event ( @$events ) {
1140
$ea->aggregate($event);
1142
$ea->calculate_statistical_metrics();
1144
# Make sure that explain_sparkline() does USE db like explain_report()
1145
# does because by mqd defaults expalin_sparline() is called by profile()
1146
# so if it doesn't USE db then the EXPLAIN will fail. Here we reset
1147
# the db to something else because we already called explain_report()
1148
# above which did USE qrf.
1149
$dbh->do("USE mysql");
1150
my $explain_sparkline = $qrf->explain_sparkline($arg, 'qrf');
1154
"explain_sparkling() uses db"
1157
$report = new ReportFormatter(
1161
$qrf->set_report_formatter(report=>'profile', formatter=>$report);
1162
$dbh->do("USE mysql"); # same reason as above ^; force use db from event
1166
$qrf->print_reports(
1167
reports => ['profile', 'query_report'],
1169
worst => [ [$fingerprint, 'top', 1], ],
1170
other => [ [$fingerprint, 'misc', 2], ],
1171
orderby => 'Query_time',
1172
groupby => 'fingerprint',
1175
($sandbox_version ge '5.1' ?
1176
"t/lib/samples/QueryReportFormatter/report027.txt"
1177
: "t/lib/samples/QueryReportFormatter/report029.txt"),
1179
"EXPLAIN sparkline (issue 1141)"
1182
$sb->wipe_clean($dbh);
1186
# #############################################################################
1187
# files and date reports.
1188
# #############################################################################
1191
qr/# Current date: .+?\d+:\d+:\d+/,
1196
$qrf->files(files=>[qw(foo bar)]),
1197
"# Files: foo, bar\n",
1203
qr/# Hostname: .+?/,
1207
# #############################################################################
1208
# Test report grouping.
1209
# #############################################################################
1213
arg => "select col from tbl where id=42",
1214
fingerprint => "select col from tbl where id=?",
1215
Query_time => '1.000652',
1216
Lock_time => '0.001292',
1217
ts => '071015 21:43:52',
1222
$ea = new EventAggregator(
1223
groupby => 'fingerprint',
1224
worst => 'Query_time',
1226
foreach my $event ( @$events ) {
1227
$ea->aggregate($event);
1229
$ea->calculate_statistical_metrics();
1232
$report = new ReportFormatter(line_width=>82);
1233
$qrf = new QueryReportFormatter(
1235
QueryRewriter => $qr,
1238
ExplainAnalyzer => $ex,
1240
$qrf->set_report_formatter(report=>'profile', formatter=>$report);
1241
my $output = output(
1242
sub { $qrf->print_reports(
1243
reports => [qw(rusage date files header query_report profile)],
1245
worst => [['select col from tbl where id=?','top',1]],
1246
orderby => 'Query_time',
1247
groupby => 'fingerprint',
1248
files => [qw(foo bar)],
1249
group => {map {$_=>1} qw(rusage date files header)},
1255
^#\s.+?\suser time.+?vsz$
1256
^#\sCurrent date:.+?$
1257
^#\sFiles:\sfoo,\sbar$
1262
# #############################################################################
1263
# Issue 1124: Make mk-query-digest profile include variance-to-mean ratio
1264
# #############################################################################
1268
Query_time => "1.000000",
1269
arg => "select c from t where id=1",
1270
fingerprint => "select c from t where id=?",
1275
Query_time => "5.500000",
1276
arg => "select c from t where id=2",
1277
fingerprint => "select c from t where id=?",
1282
Query_time => "2.000000",
1283
arg => "select c from t where id=3",
1284
fingerprint => "select c from t where id=?",
1289
Query_time => "9.000000",
1290
arg => "select c from t where id=4",
1291
fingerprint => "select c from t where id=?",
1296
$ea = new EventAggregator(
1297
groupby => 'fingerprint',
1298
worst => 'Query_time',
1300
foreach my $event ( @$events ) {
1301
$ea->aggregate($event);
1303
$ea->calculate_statistical_metrics();
1304
$report = new ReportFormatter(
1308
$qrf->set_report_formatter(report=>'profile', formatter=>$report);
1312
$qrf->print_reports(
1313
reports => ['profile'],
1316
['select c from t where id=?', 'top',1],
1318
orderby => 'Query_time',
1319
groupby => 'fingerprint',
1322
"t/lib/samples/QueryReportFormatter/report005.txt",
1324
"Variance-to-mean ration (issue 1124)"
1327
# #############################################################################
1328
# Issue 1141: Add "spark charts" to mk-query-digest profile
1329
# #############################################################################
1332
my ($arg, $attrib, $vals) = @args{qw(arg attrib vals)};
1334
my $bytes = length $arg;
1335
my $fingerprint = $qr->fingerprint($arg);
1338
foreach my $val ( @$vals ) {
1342
fingerprint => $fingerprint,
1347
$ea = new EventAggregator(
1348
groupby => 'fingerprint',
1349
worst => 'Query_time',
1351
foreach my $event (@$events) {
1352
$ea->aggregate($event);
1354
$ea->calculate_statistical_metrics(apdex_t=>1);
1356
# Seeing the full chart helps determine what the
1357
# sparkline should look like.
1358
if ( $args{chart} ) {
1359
$result = $qrf->chart_distro(
1361
item => 'select c from t',
1362
attrib => 'Query_time',
1370
# Test sparklines in isolation.
1372
arg => 'select c from t',
1373
attrib => 'Query_time',
1374
vals => [qw(0 0 0)],
1376
$result = $qrf->distro_sparkline(
1378
item => 'select c from t',
1379
attrib => 'Query_time',
1384
"Sparkchart line - all zeros"
1389
# 100us ################################################
1390
# 1ms ################################
1391
# 10ms ################################
1392
# 100ms ################################################################
1393
# 1s ################
1396
arg => 'select c from t',
1397
attrib => 'Query_time',
1398
vals => [qw(0.100000 0.500000 0.000600 0.008000 0.990000 1.000000 0.400000 0.003000 0.000200 0.000100 0.010000 0.020000)],
1400
$result = $qrf->distro_sparkline(
1402
item => 'select c from t',
1403
attrib => 'Query_time',
1415
# 10ms ################################
1416
# 100ms ################################################################
1420
arg => 'select c from t',
1421
attrib => 'Query_time',
1422
vals => [qw(0.01 0.03 0.08 0.09 0.3 0.5 0.5 0.6 0.7 0.5 0.5 0.9 1.0)],
1424
$result = $qrf->distro_sparkline(
1426
item => 'select c from t',
1427
attrib => 'Query_time',
1435
# 1us ################################################################
1436
# 10us ################################################################
1437
# 100us ################################################################
1438
# 1ms ################################################################
1439
# 10ms ################################################################
1440
# 100ms ################################################################
1441
# 1s ################################################################
1444
arg => 'select c from t',
1445
attrib => 'Query_time',
1446
vals => [qw(0.000003 0.000030 0.000300 0.003000 0.030000 0.300000 3)],
1448
$result = $qrf->distro_sparkline(
1450
item => 'select c from t',
1451
attrib => 'Query_time',
1456
"Sparkchart line - vals in all ranges except 10s+"
1460
# 1us ################################################################
1461
# 10us ################################################################
1466
# 1s ################################################################
1467
# 10s+ ################################################################
1469
arg => 'select c from t',
1470
attrib => 'Query_time',
1471
vals => [qw(0.000003 0.000030 0.000003 0.000030 3 3 30 30)],
1473
$result = $qrf->distro_sparkline(
1475
item => 'select c from t',
1476
attrib => 'Query_time',
1481
"Sparkchart line - twin peaks"
1484
# Test that that ^ sparkchart appears in the event header properly.
1485
$result = $qrf->event_report(
1487
select => [ qw(Query_time) ],
1488
item => 'select c from t',
1490
orderby => 'Query_time',
1496
"t/lib/samples/QueryReportFormatter/report028.txt",
1499
'Sparkchart in event header'
1502
# #############################################################################
1504
# #############################################################################
1508
open STDERR, '>', \$output;
1509
$qrf->_d('Complete test coverage');
1513
qr/Complete test coverage/,