Percona Toolkit moved to https://jira.percona.com/projects/PT

Merge lp:~percona-toolkit-dev/percona-toolkit/pqd-json-with-histograms into lp:~percona-toolkit-dev/percona-toolkit/release-2.2.5

  1. pqd-json-with-histograms
  2. Merge into release-2.2.5
Proposed by Daniel Nichter
Status: Merged
Approved by: Daniel Nichter
Approved revision: 591
Merged at revision: 591
Proposed branch: lp:~percona-toolkit-dev/percona-toolkit/pqd-json-with-histograms
Merge into: lp:~percona-toolkit-dev/percona-toolkit/release-2.2.5
Diff against target: 539 lines (+460/-0)
7 files modified
bin/pt-query-digest (+11/-0)
lib/JSONReportFormatter.pm (+14/-0)
t/lib/JSONReportFormatter.t (+170/-0)
t/lib/samples/JSONReportFormatter/report001.json (+205/-0)
t/pt-query-digest/samples/json/slow002-anon.txt (+12/-0)
t/pt-query-digest/samples/json/slow002.txt (+12/-0)
t/pt-query-digest/samples/json/tcpdump021.txt (+36/-0)
To merge this branch: bzr merge lp:~percona-toolkit-dev/percona-toolkit/pqd-json-with-histograms
Reviewer Review Type Date Requested Status
Daniel Nichter Approve
Review via email: mp+180677@code.launchpad.net
To post a comment you must log in.
Revision history for this message
Daniel Nichter (daniel-nichter) :
review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'bin/pt-query-digest'
2--- bin/pt-query-digest 2013-08-08 19:28:36 +0000
3+++ bin/pt-query-digest 2013-08-17 01:25:29 +0000
4@@ -8023,6 +8023,17 @@
5 }
6 }
7
8+ my $vals = $stats->{Query_time}->{all};
9+ if ( defined $vals && scalar %$vals ) {
10+ my @buck_tens = $ea->buckets_of(10);
11+ my @distro = map { 0 } (0 .. 7);
12+ my @buckets = map { 0 } (0..999);
13+ map { $buckets[$_] = $vals->{$_} } keys %$vals;
14+ $vals = \@buckets; # repoint vals from given hashref to our array
15+ map { $distro[$buck_tens[$_]] += $vals->[$_] } (1 .. @$vals - 1);
16+ $class->{histograms}->{Query_time} = \@distro;
17+ } # histogram
18+
19 $class->{metrics} = \%metrics;
20 if ( @tables ) {
21 $class->{tables} = \@tables;
22
23=== modified file 'lib/JSONReportFormatter.pm'
24--- lib/JSONReportFormatter.pm 2013-07-01 20:59:12 +0000
25+++ lib/JSONReportFormatter.pm 2013-08-17 01:25:29 +0000
26@@ -368,6 +368,20 @@
27 }
28 }
29
30+ # Add reponse time histogram for Query_time
31+ my $vals = $stats->{Query_time}->{all};
32+ if ( defined $vals && scalar %$vals ) {
33+ # TODO: this is broken.
34+ my @buck_tens = $ea->buckets_of(10);
35+ my @distro = map { 0 } (0 .. 7);
36+ my @buckets = map { 0 } (0..999);
37+ map { $buckets[$_] = $vals->{$_} } keys %$vals;
38+ $vals = \@buckets; # repoint vals from given hashref to our array
39+ map { $distro[$buck_tens[$_]] += $vals->[$_] } (1 .. @$vals - 1);
40+ # @distro = qw(1us 10us 100us 1ms 10ms 100ms 1s 10s+)
41+ $class->{histograms}->{Query_time} = \@distro;
42+ } # histogram
43+
44 $class->{metrics} = \%metrics;
45 if ( @tables ) {
46 $class->{tables} = \@tables;
47
48=== added file 't/lib/JSONReportFormatter.t'
49--- t/lib/JSONReportFormatter.t 1970-01-01 00:00:00 +0000
50+++ t/lib/JSONReportFormatter.t 2013-08-17 01:25:29 +0000
51@@ -0,0 +1,170 @@
52+#!/usr/bin/perl
53+
54+BEGIN {
55+ die "The PERCONA_TOOLKIT_BRANCH environment variable is not set.\n"
56+ unless $ENV{PERCONA_TOOLKIT_BRANCH} && -d $ENV{PERCONA_TOOLKIT_BRANCH};
57+ unshift @INC, "$ENV{PERCONA_TOOLKIT_BRANCH}/lib";
58+ $ENV{PTTEST_PRETTY_JSON} = 1;
59+
60+};
61+
62+use strict;
63+use warnings FATAL => 'all';
64+use English qw(-no_match_vars);
65+use Test::More;
66+
67+use Data::Dumper;
68+$Data::Dumper::Indent = 1;
69+$Data::Dumper::Sortkeys = 1;
70+$Data::Dumper::Quotekeys = 0;
71+
72+use JSONReportFormatter;
73+use OptionParser;
74+use DSNParser;
75+use EventAggregator;
76+use QueryRewriter;
77+use QueryParser;
78+use Quoter;
79+use PerconaTest;
80+
81+my ($result, $events, $expected);
82+
83+my $o = new OptionParser(description=>'JSONReportFormatter');
84+my $q = new Quoter();
85+my $qp = new QueryParser();
86+my $qr = new QueryRewriter(QueryParser=>$qp);
87+
88+$o->get_specs("$trunk/bin/pt-query-digest");
89+
90+my $j = new JSONReportFormatter(
91+ OptionParser => $o,
92+ QueryRewriter => $qr,
93+ QueryParser => $qp,
94+ Quoter => $q,
95+);
96+
97+my $ea = new EventAggregator(
98+ groupby => 'fingerprint',
99+ worst => 'Query_time',
100+ attributes => {
101+ Query_time => [qw(Query_time)],
102+ Lock_time => [qw(Lock_time)],
103+ user => [qw(user)],
104+ ts => [qw(ts)],
105+ Rows_sent => [qw(Rows_sent)],
106+ Rows_examined => [qw(Rows_examined)],
107+ db => [qw(db)],
108+ },
109+);
110+
111+isa_ok(
112+ $j,
113+ 'JSONReportFormatter'
114+) or die "Cannot create a JSONReportFormatter";
115+
116+$events = [
117+ { ts => '071015 21:43:52',
118+ cmd => 'Query',
119+ user => 'root',
120+ host => 'localhost',
121+ ip => '',
122+ arg => "SELECT id FROM users WHERE name='foo'",
123+ Query_time => '8.000652',
124+ Lock_time => '0.000109',
125+ Rows_sent => 1,
126+ Rows_examined => 1,
127+ pos_in_log => 1,
128+ db => 'test3',
129+ },
130+ { ts => '071015 21:43:52',
131+ cmd => 'Query',
132+ user => 'root',
133+ host => 'localhost',
134+ ip => '',
135+ arg =>
136+ "INSERT IGNORE INTO articles (id, body,)VALUES(3558268,'sample text')",
137+ Query_time => '1.001943',
138+ Lock_time => '0.000145',
139+ Rows_sent => 0,
140+ Rows_examined => 0,
141+ pos_in_log => 2,
142+ db => 'test1',
143+ },
144+ { ts => '071015 21:43:53',
145+ cmd => 'Query',
146+ user => 'bob',
147+ host => 'localhost',
148+ ip => '',
149+ arg => "SELECT id FROM users WHERE name='bar'",
150+ Query_time => '1.000682',
151+ Lock_time => '0.000201',
152+ Rows_sent => 1,
153+ Rows_examined => 2,
154+ pos_in_log => 5,
155+ db => 'test1',
156+ }
157+];
158+
159+# Here's the breakdown of values for those three events:
160+#
161+# ATTRIBUTE VALUE BUCKET VALUE RANGE
162+# Query_time => 8.000652 326 7.700558026 range [7.700558026, 8.085585927)
163+# Query_time => 1.001943 284 0.992136979 range [0.992136979, 1.041743827)
164+# Query_time => 1.000682 284 0.992136979 range [0.992136979, 1.041743827)
165+# -------- -----------
166+# 10.003277 9.684831984
167+#
168+# Lock_time => 0.000109 97 0.000108186 range [0.000108186, 0.000113596)
169+# Lock_time => 0.000145 103 0.000144980 range [0.000144980, 0.000152229)
170+# Lock_time => 0.000201 109 0.000194287 range [0.000194287, 0.000204002)
171+# -------- -----------
172+# 0.000455 0.000447453
173+#
174+# Rows_sent => 1 284 0.992136979 range [0.992136979, 1.041743827)
175+# Rows_sent => 0 0 0
176+# Rows_sent => 1 284 0.992136979 range [0.992136979, 1.041743827)
177+# -------- -----------
178+# 2 1.984273958
179+#
180+# Rows_exam => 1 284 0.992136979 range [0.992136979, 1.041743827)
181+# Rows_exam => 0 0 0
182+# Rows_exam => 2 298 1.964363355, range [1.964363355, 2.062581523)
183+# -------- -----------
184+# 3 2.956500334
185+
186+# I hand-checked these values with my TI-83 calculator.
187+# They are, without a doubt, correct.
188+
189+foreach my $event (@$events) {
190+ $event->{fingerprint} = $qr->fingerprint( $event->{arg} );
191+ $ea->aggregate($event);
192+}
193+$ea->calculate_statistical_metrics(apdex_t=>1);
194+my %top_spec = (
195+ attrib => 'Query_time',
196+ orderby => 'sum',
197+ total => 100,
198+ count => undef,
199+);
200+@top_spec{qw(ol_attrib ol_limit ol_freq)} = split(/:/, "Query_time:1:10");
201+my ($worst, $other) = $ea->top_events(%top_spec);
202+$result = $j->query_report(
203+ ea => $ea,
204+ worst => $worst,
205+ orderby => 'Query_time',
206+ groupby => 'fingerprint',
207+);
208+
209+ok(
210+ no_diff(
211+ $result,
212+ "t/lib/samples/JSONReportFormatter/report001.json",
213+ cmd_output => 1,
214+ ),
215+ 'Basic output'
216+) or diag($test_diff);
217+
218+# #############################################################################
219+# Done.
220+# #############################################################################
221+done_testing;
222
223=== added directory 't/lib/samples/JSONReportFormatter'
224=== added file 't/lib/samples/JSONReportFormatter/report001.json'
225--- t/lib/samples/JSONReportFormatter/report001.json 1970-01-01 00:00:00 +0000
226+++ t/lib/samples/JSONReportFormatter/report001.json 2013-08-17 01:25:29 +0000
227@@ -0,0 +1,205 @@
228+{
229+ "classes" : [
230+ {
231+ "attribute" : "fingerprint",
232+ "checksum" : "82860EDA9A88FCC5",
233+ "distillate" : "SELECT users",
234+ "example" : {
235+ "query" : "SELECT id FROM users WHERE name='foo'",
236+ "ts" : "2007-10-15 21:43:52"
237+ },
238+ "fingerprint" : "select id from users where name=?",
239+ "histograms" : {
240+ "Query_time" : [
241+ 0,
242+ 0,
243+ 0,
244+ 0,
245+ 0,
246+ 0,
247+ 2,
248+ 0
249+ ]
250+ },
251+ "metrics" : {
252+ "Lock_time" : {
253+ "avg" : "0.000155",
254+ "max" : "0.000201",
255+ "median" : "0.000155",
256+ "min" : "0.000109",
257+ "pct" : "0.666667",
258+ "pct_95" : "0.000201",
259+ "stddev" : "0.000065",
260+ "sum" : "0.000310"
261+ },
262+ "Query_time" : {
263+ "avg" : "4.500667",
264+ "max" : "8.000652",
265+ "median" : "4.500667",
266+ "min" : "1.000682",
267+ "pct" : "0.666667",
268+ "pct_95" : "8.000652",
269+ "stddev" : "4.949726",
270+ "sum" : "9.001334"
271+ },
272+ "Rows_examined" : {
273+ "avg" : "1",
274+ "max" : "2",
275+ "median" : "1",
276+ "min" : "1",
277+ "pct" : "0",
278+ "pct_95" : "2",
279+ "stddev" : "0",
280+ "sum" : "3"
281+ },
282+ "Rows_sent" : {
283+ "avg" : "1",
284+ "max" : "1",
285+ "median" : "1",
286+ "min" : "1",
287+ "pct" : "0",
288+ "pct_95" : "1",
289+ "stddev" : "0",
290+ "sum" : "2"
291+ },
292+ "db" : {
293+ "value" : "test3"
294+ },
295+ "user" : {
296+ "value" : "root"
297+ }
298+ },
299+ "query_count" : null,
300+ "tables" : [
301+ {
302+ "create" : "SHOW CREATE TABLE `test3`.`users`\\G",
303+ "status" : "SHOW TABLE STATUS FROM `test3` LIKE 'users'\\G"
304+ }
305+ ],
306+ "ts_max" : "2007-10-15 21:43:53",
307+ "ts_min" : "2007-10-15 21:43:52"
308+ },
309+ {
310+ "attribute" : "fingerprint",
311+ "checksum" : "1087A32FED3B7EBB",
312+ "distillate" : "INSERT articles",
313+ "example" : {
314+ "query" : "INSERT IGNORE INTO articles (id, body,)VALUES(3558268,'sample text')",
315+ "ts" : "2007-10-15 21:43:52"
316+ },
317+ "fingerprint" : "insert ignore into articles (id, body,)values(?+)",
318+ "histograms" : {
319+ "Query_time" : [
320+ 0,
321+ 0,
322+ 0,
323+ 0,
324+ 0,
325+ 0,
326+ 1,
327+ 0
328+ ]
329+ },
330+ "metrics" : {
331+ "Lock_time" : {
332+ "avg" : "0.000145",
333+ "max" : "0.000145",
334+ "median" : "0.000145",
335+ "min" : "0.000145",
336+ "pct" : "0.333333",
337+ "pct_95" : "0.000145",
338+ "stddev" : "0.000000",
339+ "sum" : "0.000145"
340+ },
341+ "Query_time" : {
342+ "avg" : "1.001943",
343+ "max" : "1.001943",
344+ "median" : "1.001943",
345+ "min" : "1.001943",
346+ "pct" : "0.333333",
347+ "pct_95" : "1.001943",
348+ "stddev" : "0.000000",
349+ "sum" : "1.001943"
350+ },
351+ "Rows_examined" : {
352+ "avg" : "0",
353+ "max" : "0",
354+ "median" : "0",
355+ "min" : "0",
356+ "pct" : "0",
357+ "pct_95" : "0",
358+ "stddev" : "0",
359+ "sum" : "0"
360+ },
361+ "Rows_sent" : {
362+ "avg" : "0",
363+ "max" : "0",
364+ "median" : "0",
365+ "min" : "0",
366+ "pct" : "0",
367+ "pct_95" : "0",
368+ "stddev" : "0",
369+ "sum" : "0"
370+ },
371+ "db" : {
372+ "value" : "test1"
373+ },
374+ "user" : {
375+ "value" : "root"
376+ }
377+ },
378+ "query_count" : null,
379+ "tables" : [
380+ {
381+ "create" : "SHOW CREATE TABLE `test1`.`articles`\\G",
382+ "status" : "SHOW TABLE STATUS FROM `test1` LIKE 'articles'\\G"
383+ }
384+ ],
385+ "ts_max" : "2007-10-15 21:43:52",
386+ "ts_min" : "2007-10-15 21:43:52"
387+ }
388+ ],
389+ "global" : {
390+ "files" : null,
391+ "metrics" : {
392+ "Lock_time" : {
393+ "avg" : "0.000152",
394+ "max" : "0.000201",
395+ "median" : "0.000145",
396+ "min" : "0.000109",
397+ "pct_95" : "0.000194",
398+ "stddev" : "0.000035",
399+ "sum" : "0.000455"
400+ },
401+ "Query_time" : {
402+ "avg" : "3.334426",
403+ "max" : "8.000652",
404+ "median" : "0.992137",
405+ "min" : "1.000682",
406+ "pct_95" : "7.700558",
407+ "stddev" : "3.162380",
408+ "sum" : "10.003277"
409+ },
410+ "Rows_examined" : {
411+ "avg" : "1",
412+ "max" : "2",
413+ "median" : "0",
414+ "min" : "0",
415+ "pct_95" : "1",
416+ "stddev" : "0",
417+ "sum" : "3"
418+ },
419+ "Rows_sent" : {
420+ "avg" : "0",
421+ "max" : "1",
422+ "median" : "0",
423+ "min" : "0",
424+ "pct_95" : "0",
425+ "stddev" : "0",
426+ "sum" : "2"
427+ }
428+ },
429+ "query_count" : 3,
430+ "unique_query_count" : 2
431+ }
432+}
433
434=== modified file 't/pt-query-digest/samples/json/slow002-anon.txt'
435--- t/pt-query-digest/samples/json/slow002-anon.txt 2013-07-01 20:59:12 +0000
436+++ t/pt-query-digest/samples/json/slow002-anon.txt 2013-08-17 01:25:29 +0000
437@@ -6,6 +6,18 @@
438 "checksum" : "66825DDC008FFA89",
439 "distillate" : "UPDATE db?.tuningdetail_?_? db?.gonzo",
440 "fingerprint" : "update d?tuningdetail_?_? n inner join d?gonzo a using(gonzo) set n.column? = a.column?, n.word? = a.word?",
441+ "histograms" : {
442+ "Query_time" : [
443+ 0,
444+ 0,
445+ 0,
446+ 0,
447+ 0,
448+ 1,
449+ 0,
450+ 0
451+ ]
452+ },
453 "metrics" : {
454 "Filesort" : {
455 "yes" : "0"
456
457=== modified file 't/pt-query-digest/samples/json/slow002.txt'
458--- t/pt-query-digest/samples/json/slow002.txt 2013-07-01 20:42:56 +0000
459+++ t/pt-query-digest/samples/json/slow002.txt 2013-08-17 01:25:29 +0000
460@@ -11,6 +11,18 @@
461 "ts" : "2007-12-18 11:48:27"
462 },
463 "fingerprint" : "update d?tuningdetail_?_? n inner join d?gonzo a using(gonzo) set n.column? = a.column?, n.word? = a.word?",
464+ "histograms" : {
465+ "Query_time" : [
466+ 0,
467+ 0,
468+ 0,
469+ 0,
470+ 0,
471+ 1,
472+ 0,
473+ 0
474+ ]
475+ },
476 "metrics" : {
477 "Filesort" : {
478 "yes" : "0"
479
480=== modified file 't/pt-query-digest/samples/json/tcpdump021.txt'
481--- t/pt-query-digest/samples/json/tcpdump021.txt 2013-07-01 20:42:56 +0000
482+++ t/pt-query-digest/samples/json/tcpdump021.txt 2013-08-17 01:25:29 +0000
483@@ -11,6 +11,18 @@
484 "ts" : "2009-12-08 09:23:49.637394"
485 },
486 "fingerprint" : "prepare select i from d.t where i=?",
487+ "histograms" : {
488+ "Query_time" : [
489+ 0,
490+ 0,
491+ 1,
492+ 0,
493+ 0,
494+ 0,
495+ 0,
496+ 0
497+ ]
498+ },
499 "metrics" : {
500 "No_good_index_used" : {
501 "yes" : "0"
502@@ -75,6 +87,18 @@
503 "ts" : "2009-12-08 09:23:49.637892"
504 },
505 "fingerprint" : "execute select i from d.t where i=?",
506+ "histograms" : {
507+ "Query_time" : [
508+ 0,
509+ 0,
510+ 1,
511+ 0,
512+ 0,
513+ 0,
514+ 0,
515+ 0
516+ ]
517+ },
518 "metrics" : {
519 "No_good_index_used" : {
520 "yes" : "0"
521@@ -138,6 +162,18 @@
522 "ts" : "2009-12-08 09:23:49.638381"
523 },
524 "fingerprint" : "administrator command: Quit",
525+ "histograms" : {
526+ "Query_time" : [
527+ 0,
528+ 0,
529+ 0,
530+ 0,
531+ 0,
532+ 0,
533+ 0,
534+ 0
535+ ]
536+ },
537 "metrics" : {
538 "No_good_index_used" : {
539 "yes" : "0"

Subscribers

People subscribed via source and target branches

to all changes: