Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-964

LP #988928: pt-query-digest doesn't understand Tmp_table_sizes > 2^32

    XMLWordPrintable

    Details

      Description

      **Reported in Launchpad by Alex Sedlov last update 20-06-2013 19:57:48

      if any request has an value slowlog attribute Tmp_table_sizes > 2 ^ 32 (ie 2 ^ 32 +1), then pt-query-digest in the report shows:

      Attribute pct total min max avg 95% stddev median
      Tmp tbl size 0 0 0 0 0 0 0 0

      command line:
      pt-query-digest --explain h=localhost,D=db,F=./db.cfg --nofor-explain --report-format query_report --type slowlog \
      --fingerprints --group-by fingerprint --order-by Tmp_table_sizes:max --limit '100%' slowlog

      Versions:
      percona-toolkit 2.1.1
      mysql 5.1.59-rel13.0-325.lenny
      OS Debian 5.0.10 amd64

      Test slowlog:

      1. User@Host: test[test] @ localhost [127.0.0.1]
      2. Thread_id: 43782952 Schema: db Last_errno: 0 Killed: 0
      3. Query_time: 116.945246 Lock_time: 0.000101 Rows_sent: 50 Rows_examined: 28892915 Rows
      4. Bytes_sent: 21341 Tmp_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 4294967297
      5. InnoDB_trx_id: 1F22C67C9
      6. QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes
      7. Filesort: Yes Filesort_on_disk: Yes Merge_passes: 26
      8. InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000
      9. InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
      10. InnoDB_pages_distinct: 64591
        SET timestamp=1335268230;
        select * FROM test.

      output:

      1. Query 1: 0 QPS, 0x concurrency, ID 0x321B2DD737365711 at byte 0 ________
      2. This item is included in the report because it matches --limit.
      3. Scores: Apdex = 0.00 [1.0]*, V/M = 0.00
      4. EXPLAIN sparkline: a
      5. Query_time sparkline: | ^|
      6. Attribute pct total min max avg 95% stddev median
      7. ============ === ======= ======= ======= ======= ======= ======= =======
      8. Count 100 1
      9. Exec time 100 117s 117s 117s 117s 117s 0 117s
      10. Lock time 100 101us 101us 101us 101us 101us 0 101us
      11. Rows sent 100 50 50 50 50 50 0 50
      12. Rows examine 100 27.55M 27.55M 27.55M 27.55M 27.55M 0 27.55M
      13. Rows affecte 0 0 0 0 0 0 0 0
      14. Rows read 0 0 0 0 0 0 0 0
      15. Bytes sent 100 20.84k 20.84k 20.84k 20.84k 20.84k 0 20.84k
      16. Merge passes 100 26 26 26 26 26 0 26
      17. Tmp tables 100 1 1 1 1 1 0 1
      18. Tmp disk tbl 100 1 1 1 1 1 0 1
      19. Tmp tbl size 0 0 0 0 0 0 0 0
      20. Query size 100 23 23 23 23 23 0 23
      21. InnoDB:
      22. IO r bytes 0 0 0 0 0 0 0 0
      23. IO r ops 0 0 0 0 0 0 0 0
      24. IO r wait 0 0 0 0 0 0 0 0
      25. pages distin 100 63.08k 63.08k 63.08k 63.08k 63.08k 0 63.08k
      26. queue wait 0 0 0 0 0 0 0 0
      27. rec lock wai 0 0 0 0 0 0 0 0
      28. Boolean:
      29. Filesort 100% yes, 0% no
      30. Filesort on 100% yes, 0% no
      31. Full scan 100% yes, 0% no
      32. Tmp table 100% yes, 0% no
      33. Tmp table on 100% yes, 0% no
      34. String:
      35. Databases db
      36. Hosts localhost
      37. InnoDB trxID 1F22C67C9
      38. Last errno 0
      39. Users test
      40. Query_time distribution
      41. 1us
      42. 10us
      43. 100us
      44. 1ms
      45. 10ms
      46. 100ms
      47. 1s
      48. 10s+ ################################################################
      49. Fingerprint
      50. select * from test
      51. EXPLAIN /!50100 PARTITIONS/
        select * FROM test \G
      52. *************************** 1. row ***************************
      53. id: 1
      54. select_type: SIMPLE
      55. table: test
      56. partitions: NULL
      57. type: ALL
      58. possible_keys: NULL
      59. key: NULL
      60. key_len: NULL
      61. ref: NULL
      62. rows: 8531893
      63. Extra:

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              lpjirasync lpjirasync (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: