Databases Reference
In-Depth Information
Another bit of extra detail here is the variance-to-mean ratio, in the V/M column. This
is also known as the
index of dispersion
. Queries with a higher index of dispersion have
a more variable execution-time profile, and highly variable queries are generally good
candidates for optimization. If you specify the
--explain
option to
pt-query-digest
, it will
also add a column with a short representation of the query's
EXPLAIN
plan—sort of a
“geek code” for the query. This, in combination with the V/M column, makes it a snap
to see which queries are bad and potentially easy to optimize.
Finally, there's an additional line at the bottom, showing the presence of 17 other types
of queries that the tool didn't consider important enough to report individually,
and a summary of the statistics for all of them. You can use options such as
--limit
and
--outliers
to make the tool show more details instead of collapsing unimportant queries
into this final line. By default, the tool prints out queries that are either in the top 10
time consumers overall, or whose execution time was over a one-second threshold too
many times. Both of these limits are configurable.
Following the profile, the tool prints out a detailed report on each type of query. You
can match the query reports to the profile by looking for the query ID or the rank.
Here's the report for the #1 ranked query, the “worst” one:
# Query 1: 24.28 QPS, 3.50x concurrency, ID 0xBFCF8E3F293F6466 at byte 5590079
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.21
# Query_time sparkline: | _^_.^_ |
# Time range: 2008-09-13 21:51:55 to 22:45:30
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 63 78069
# Exec time 68 11256s 37us 1s 144ms 501ms 175ms 68ms
# Lock time 85 134s 0 650ms 2ms 176us 20ms 57us
# Rows sent 8 70.18k 0 1 0.92 0.99 0.27 0.99
# Rows examine 8 70.84k 0 3 0.93 0.99 0.28 0.99
# Query size 84 10.43M 135 141 140.13 136.99 0.10 136.99
# String:
# Databases production
# Hosts
# Users fbappuser
# Query_time distribution
# 1us
# 10us #
# 100us ####################################################
# 1ms ###
# 10ms ################
# 100ms ################################################################
# 1s #
# 10s+
# Tables
# SHOW TABLE STATUS FROM `production ` LIKE'InvitesNew82'\G
# SHOW CREATE TABLE `production `.`InvitesNew82'\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT InviteId, InviterIdentifier FROM InvitesNew82 WHERE (InviteSetId = 87041469)
AND (InviteeIdentifier = 1138714082) LIMIT 1\G
