Does Percona Toolkit report less information than before?

This is my first post on the forums and I am glad to be here.

I installed Percona Toolkit on our Ubuntu 14.04 server just now, captured some slow queries, ran pt-query-digest against the log and captured the output to a file. To interpret the results, I found this old Percona article useful.

However, I find the output in my file to be different or missing substantial information that’s detailed in the article linked above. For instance, the first query in my log file has the following details:

# Query 1: 0.76 QPS, 0.52x concurrency, ID 0x1AA557597FA1014E at byte 10282856
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.47
# Time range: 2018-05-05 17:05:32 to 17:07:39
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 97
# Exec time 22 66s 54ms 3s 676ms 2s 564ms 526ms
# Lock time 0 30ms 50us 21ms 313us 176us 2ms 76us
# Rows sent 8 561.35k 5.79k 5.79k 5.79k 5.79k 0 5.79k
# Rows examine 23 2.65M 27.93k 27.93k 27.93k 27.93k 0 27.93k
# Query size 0 20.75k 219 219 219 219 0 219
# String:
# Databases username_prodndb
# Hosts localhost
# Users username_dbmin
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms #######
# 100ms ################################################################
# 1s #####################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `username_prodndb` LIKE 'cataloginventory_stock_item'\G
# SHOW CREATE TABLE `username_prodndb`.`cataloginventory_stock_item`\G
# SHOW TABLE STATUS FROM `username_prodndb` LIKE 'catalog_product_entity'\G
# SHOW CREATE TABLE `username_prodndb`.`catalog_product_entity`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `main_table`.*, `cp_table`.`type_id` FROM `cataloginventory_stock_item` AS `main_table`
INNER JOIN `catalog_product_entity` AS `cp_table` ON main_table.product_id = cp_table.entity_id WHERE (`is_in_stock` = '1')\G

In the article, however, the output for the first query has the following useful additional info:

# Merge passes 0 0 0 0 0 0 0 0
# Tmp tables 0 0 0 0 0 0 0 0
# Tmp disk tbl 0 0 0 0 0 0 0 0
# Tmp tbl size 0 0 0 0 0 0 0 0
# Query size 0 21.63k 71 71 71 71 0 71
# InnoDB:
# IO r bytes 0 0 0 0 0 0 0 0
# IO r ops 0 0 0 0 0 0 0 0
# IO r wait 0 0 0 0 0 0 0 0
# pages distin 40 11.77k 34 44 38.62 38.53 1.87 38.53
# queue wait 0 0 0 0 0 0 0 0
# rec lock wai 0 0 0 0 0 0 0 0
# Boolean:
# Full scan 100% yes, 0% no
# String:
# Databases wp_blog_one (264/84%), wp_blog_tw… (36/11%)... 1 more
# Hosts
# InnoDB trxID 86B40B (1/0%), 86B430 (1/0%), 86B44A (1/0%)... 309 more
# Last errno 0
# Users wp_blog_one (264/84%), wp_blog_two (36/11%)... 1 more

Is this useful information available elsewhere in the current versions of Percona Toolkit? I mean the stuff related to tmp_table creation, full scan, filesort etc.

Additionally, the report I generated doesn’t really seem to tell me which are the queries without indexes. I ran

[B]SET GLOBAL log_queries_not_using_indexes = 'ON';[/B]

to capture the log against which I ran pt-query-digest, so does that mean all the queries in the output file are ones without indexes? I was looking for something more specific such a list of tables without indexes or without a good index.

Note: I am a server admin, not a dba. I am just trying to use Percona Toolkit to address high CPU uitlization (frequent spikes of anywhere from 90% to 280%) by MySQL on our server.

Thanks in advance.

Hi fuzzybase

What version of MySQL are you running? The example from the blog post is using Percona Server which includes the extended slow log format, which includes additional data points per query such as InnoDB and tmp table data. pt-query-digest is aware of these fields and will provide summary metrics when they are included, otherwise you just get zeroes.

https://www.percona.com/doc/percona-…_extended.html

Regarding queries without indexes - what this option does is ensure that queries that aren’t using indexes are also logged to the slow log file. If you’ve already set long_query_time=0 then these queries are also captured. Queries not using indexes will also be known as full table scans, so you can use pt-query-digest --filter to select only those queries doing full table scans. The documentation has several examples to begin with:

https://www.percona.com/doc/percona-…-digest-filter

I hope this helps!

PS you didn’t ask about it but it might be worthwhile to explore using PMM (Percona Monitoring and Management) as it provides a GUI pt-query-digest but also many helpful dashboards that are sure to provide insight as you diagnose your performance issues.

[url]https://percona.com/pmm[/url]