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.