Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

LP #1265546: wrong statistics for query type

Description

**Reported in Launchpad by Aleksandr Kuzminsky last update 17-11-2014 04:17:20

There are two types of queries in a slow log:
1. SELECT /*!40001 SQL_NO_CACHE */ * FROM <table> (2 queries)
2. INSERT INTO <table> (4k queries)

Nevertheless pt-query-digest reports this statistics:

  1. Attribute pct total min max avg 95% stddev median

  2. ============ === ======= ======= ======= ======= ======= ======= =======

  3. Count 0 751

  4. Exec time 20 198s 44us 29s 264ms 501ms 2s 84us

  5. Lock time 0 20ms 16us 140us 26us 42us 8us 22us

  6. Rows sent 48 31.81M 0 10.79M 43.38k 59.57k 467.31k 9.83

  7. Rows examine 13 31.81M 0 10.79M 43.38k 59.57k 467.31k 9.83

  8. Query size 0 43.21k 45 79 58.92 69.19 6.27 56.92

  9. String:

  10. Databases <db1> (173/23%), <db2> (98/13%)... 32 more

  11. Hosts localhost

  12. Users <user>

  13. Query_time distribution

  14. 1us

  15. 10us ################################################################

  16. 100us ####################

  17. 1ms ##########

  18. 10ms ###########

  19. 100ms #########

  20. 1s ###

  21. 10s+ #

Count 751 neither corresponds to SELECT nor to INSERT.

Environment

None

Smart Checklist

Activity

Show:

lpjirasync January 24, 2018 at 8:02 PM

**Comment from Launchpad by: Launchpad Janitor on: 17-11-2014 04:17:17

[Expired for Percona Toolkit because there has been no activity for 60 days.]

lpjirasync January 24, 2018 at 8:02 PM

**Comment from Launchpad by: Muhammad Irfan on: 04-09-2014 08:04:17

I can't reproduce it.

$ pt-query-digest --version
pt-query-digest 2.2.10

$ awk '{for (i=1;i<=NF;i+) if ( $i == "INSERT") c+}END {print c}' /var/lib/mysql/centos1-slow.log
105

$ cat /var/lib/mysql/centos1-slow.log

  1. Profile

  2. Rank Query ID Response time Calls R/Call V/M Item

  3. ==== ================== ============= ===== ====== ===== ==========

  4. 1 0xCAEC22E79B0EFD3B 1.8842 97.5% 105 0.0179 0.00 INSERT t?

  5. MISC 0xMISC 0.0488 2.5% 21 0.0023 0.0 <10 ITEMS>

  1. Query 1: 52.50 QPS, 0.94x concurrency, ID 0xCAEC22E79B0EFD3B at byte 40611

  2. This item is included in the report because it matches --limit.

  3. Scores: V/M = 0.00

  4. Time range: 2014-09-04 02:50:04 to 02:50:06

  5. Attribute pct total min max avg 95% stddev median

  6. ============ === ======= ======= ======= ======= ======= ======= =======

  7. Count 83 105

  8. Exec time 97 2s 13ms 32ms 18ms 28ms 4ms 16ms

  9. Lock time 9 2ms 12us 51us 17us 27us 5us 15us

Count total 105 reflects total 105 inserts correctly. What pt-query-digest version you used ? Can you attach your slow query log file to check with it.

Cannot Reproduce

Details

Assignee

Reporter

Priority

Labels

Smart Checklist

Created January 24, 2018 at 8:02 PM
Updated February 4, 2018 at 12:06 AM
Resolved January 24, 2018 at 8:02 PM