0

I have a collection of slow query logs from RDS and I put them together into a single file. Trying to run it through pt-query-digest, following instructions here, but it reads the whole file as a single query.

Command: 

pt-query-digest --group-by fingerprint --order-by Query_time:sum collider-slow-query.log > slow-query-analyze.txt

Output, showing that it only analyzed one query:

# Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________

Here's just a short snippet containing 2 queries from the file being analyzed to demonstrate there are many queries:

2019-05-03T20:44:21.828Z # Time: 2019-05-03T20:44:21.828954Z
# User@Host: username[username] @  [ipaddress]  Id:    19
# Query_time: 17.443164  Lock_time: 0.000145 Rows_sent: 5  Rows_examined: 121380
SET timestamp=1556916261;
SELECT   wp_posts.ID FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1  AND wp_posts.ID NOT IN (752921) AND ( 
  wp_term_relationships.term_taxonomy_id IN (40)
) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 5;
2019-05-03T20:44:53.597Z # Time: 2019-05-03T20:44:53.597137Z
# User@Host: username[username] @  [ipaddress]  Id:    77
# Query_time: 35.757909  Lock_time: 0.000054 Rows_sent: 2  Rows_examined: 199008
SET timestamp=1556916293;
SELECT post_id, meta_value FROM wp_postmeta
                WHERE meta_key = '_wp_attached_file'
                AND meta_value IN ( 'family-guy-vestigial-peter-slice.jpg','2015/08/bobs-burgers-image.jpg','2015/08/bobs-burgers-image.jpg' );

Why isn't it reading all the queries? Is there a problem with my concatenation?

Ethan C
  • 1,408
  • 1
  • 14
  • 26
  • Hello Ethan, could you try that with PTDEBUG=1 before the command to get the debug log. ALSO please update with the version of Percona Toolkit so we can eliminate a known issue in an earlier version where there was an error parsing timestamps ending in z... you may have hit that one and need a new version of PT – greenweeds May 08 '19 at 11:56
  • @percona-lorraine I may be missing something here. I did PTDEBUG=1 && pt-query-digest --group-by fingerprint --order-by Query_time:sum collider-slow-query.log > slow-query-analyze.txt but it seems to be the same. Version - 3.0.6. Also tried with 3.0.13 – Ethan C May 14 '19 at 22:05

1 Answers1

0

I had the same problem as well. It turns out that some additional timestamp is stored in the SQL (through this variable: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_timestamps).

This makes pt-query-digest not see individual queries. It should be easily fixed by either turning off the timestamp variable or remove the timestamp:

2019-10-28T11:17:18.412 # Time: 2019-10-28T11:17:18.412214
# User@Host: foo[foo] @  [192.168.8.175]  Id: 467836
# Query_time: 5.839596  Lock_time: 0.000029 Rows_sent: 1  Rows_examined: 0
use foo;
SET timestamp=1572261432;
SELECT COUNT(*) AS `count` FROM `foo`.`invoices` AS `Invoice`   WHERE 1 = 1;

By removing the first timestamp (the 2019-10-28T11:17:18.412 part), it works again.

JayTaph
  • 2,846
  • 1
  • 22
  • 29