Not the answer you need?
Register and ask your own question!

Does Percona Toolkit report less information than before?

fuzzybasefuzzybase EntrantCurrent User Role Beginner
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.

Comments

  • Michael CoburnMichael Coburn Principal Architect, Percona Percona Staff Role
    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.

    https://percona.com/pmm
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.