API tools faq
paste
Advertisement
Guest User

new percona query digest

a guest
Jun 21st, 2012
71
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VIM 18.31 KB | None | 0 0
raw download clone embed print report
  1. root@XMPP:/var/log/mysql# pt-query-digest mysql-slow.log
  2. mysql-slow.log:   6% 07:16 remain
  3. mysql-slow.log:  15% 05:30 remain
  4. mysql-slow.log:  24% 04:43 remain
  5. mysql-slow.log:  32% 04:10 remain
  6. mysql-slow.log:  40% 03:43 remain
  7. mysql-slow.log:  47% 03:21 remain
  8. mysql-slow.log:  55% 02:49 remain
  9. mysql-slow.log:  63% 02:16 remain
  10. mysql-slow.log:  70% 01:54 remain
  11. mysql-slow.log:  75% 01:35 remain
  12. mysql-slow.log:  83% 01:06 remain
  13. mysql-slow.log:  92% 00:29 remain
  14. mysql-slow.log:  99% 00:02 remain
  15.  
  16. # 394.2s user time, 3.7s system time, 21.27M rss, 64.35M vsz
  17. # Current date: Fri Jun 22 08:16:24 2012
  18. # Hostname: XMPP.xgate.com.hk
  19. # Files: mysql-slow.log
  20. # Overall: 1.53M total, 21 unique, 294.37 QPS, 1.13x concurrency _________
  21. # Time range: 2012-06-22 06:42:37 to 08:09:01
  22. # Attribute          total     min     max     avg     95%  stddev  median
  23. # ============     ======= ======= ======= ======= ======= ======= =======
  24. # Exec time          5834s     1us      1s     4ms    17ms    15ms   467us
  25. # Lock time           162s       0   376ms   106us   273us   458us    69us
  26. # Rows sent          2.20M       0     129    1.51    0.99    6.87    0.99
  27. # Rows examine       3.71M       0 195.32k    2.55    0.99  390.17    0.99
  28. # Query size         1.71G       8   7.70k   1.17k   7.31k   2.51k   65.89
  29.  
  30. # Profile
  31. # Rank Query ID           Response time   Calls  R/Call Apdx V/M   Item
  32. # ==== ================== =============== ====== ====== ==== ===== =======
  33. #    1 0x9112934D2E0EBE7D 2816.9185 48.3% 235062 0.0120 1.00  0.07 INSERT tig_pairs
  34. #    2 0x148CABBFD4603D80 1871.9574 32.1% 226626 0.0083 1.00  0.05 DELETE tig_pairs
  35. #    3 0x3A2DF5C4FB9A3168  420.2191  7.2%  57665 0.0073 1.00  0.04 CALL TigUserLogout
  36. #    4 0xA73D290AB3EA03C3  144.5109  2.5% 431889 0.0003 1.00  0.01 CALL TigGetUserDBUid
  37. #    5 0x9BC12AC7409886BB  135.8507  2.3% 307336 0.0004 1.00  0.00 SELECT tig_nodes
  38. #    6 0xFC6FB130BCD2AC4A  122.0492  2.1%  16955 0.0072 1.00  0.04 CALL TigAddNode
  39. #    7 0xDCDFAA0337705F47   85.9583  1.5%  30104 0.0029 1.00  0.00 SELECT tig_users
  40. # MISC 0xMISC              236.0850  4.0% 220381 0.0011   NS   0.0 <14 ITEMS>
  41.  
  42. # Query 1: 45.34 QPS, 0.54x concurrency, ID 0x9112934D2E0EBE7D at byte 1897881304
  43. # Scores: Apdex = 1.00 [1.0], V/M = 0.07
  44. # Query_time sparkline: |  _^.__ |
  45. # Time range: 2012-06-22 06:42:37 to 08:09:01
  46. # Attribute    pct   total     min     max     avg     95%  stddev  median
  47. # ============ === ======= ======= ======= ======= ======= ======= =======
  48. # Count         15  235062
  49. # Exec time     48   2817s   335us      1s    12ms    42ms    29ms     3ms
  50. # Lock time     35     57s       0    88ms   241us   366us   230us   214us
  51. # Rows sent      0       0       0       0       0       0       0       0
  52. # Rows examine   0       0       0       0       0       0       0       0
  53. # Query size    91   1.56G      81   7.70k   6.95k   7.31k   1.57k   7.31k
  54. # String:
  55. # Databases    tigasedb
  56. # Hosts        localhost
  57. # Users        tigase_user
  58. # Query_time distribution
  59. #   1us
  60. #  10us
  61. # 100us  #
  62. #   1ms  ################################################################
  63. #  10ms  #####################
  64. # 100ms  #
  65. #    1s  #
  66. #  10s+
  67. # Tables
  68. #    SHOW TABLE STATUS FROM `tigasedb` LIKE 'tig_pairs'\G
  69. #    SHOW CREATE TABLE `tigasedb`.`tig_pairs`\G
  70. insert into tig_pairs (nid, uid, pkey, pval)  values (182070, 182219, 'roster', '<contact weight="1.0" jid="+391167758@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167768@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167745@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167730@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167773@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167735@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167727@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167765@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167756@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167729@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167750@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167743@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167755@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167741@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167767@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167731@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167760@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167737@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167770@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167734@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167769@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167751@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167771@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167742@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167732@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167759@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167763@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167753@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167748@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167774@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167746@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167764@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167740@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167724@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167736@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167733@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167726@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167772@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167762@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167754@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167738@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167766@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167747@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167744@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167725@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167757@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167739@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167761@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167752@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="none" activity="1.0" last-seen="1000"/><contact weight="1.0" jid="+391167728@xmpp.xgate.com.hk" name="" preped="simple" groups="XGate Dynamics" subs="both" activity="1.0" last-seen="1000"/>')\G
  71.  
  72. # Query 2: 43.72 QPS, 0.36x concurrency, ID 0x148CABBFD4603D80 at byte 1259274069
  73. # Scores: Apdex = 1.00 [1.0], V/M = 0.05
  74. # Query_time sparkline: | __^._  |
  75. # Time range: 2012-06-22 06:42:37 to 08:09:01
  76. # Attribute    pct   total     min     max     avg     95%  stddev  median
  77. # ============ === ======= ======= ======= ======= ======= ======= =======
  78. # Count         14  226626
  79. # Exec time     32   1872s    72us   912ms     8ms    31ms    20ms     2ms
  80. # Lock time      7     12s       0    92ms    54us    84us   200us    44us
  81. # Rows sent      0       0       0       0       0       0       0       0
  82. # Rows examine   5 210.79k       0       1    0.95    0.99    0.21    0.99
  83. # Query size     0  13.66M      59      67   63.19   62.76    0.07   62.76
  84. # String:
  85. # Databases    tigasedb
  86. # Hosts        localhost
  87. # Users        tigase_user
  88. # Query_time distribution
  89. #   1us
  90. #  10us  #
  91. # 100us  ############
  92. #   1ms  ################################################################
  93. #  10ms  ##################
  94. # 100ms  #
  95. #    1s
  96. #  10s+
  97. # Tables
  98. #    SHOW TABLE STATUS FROM `tigasedb` LIKE 'tig_pairs'\G
  99. #    SHOW CREATE TABLE `tigasedb`.`tig_pairs`\G
  100. delete from tig_pairs where (nid = 40418) AND (pkey = 'roster')\G
  101. # Converted for EXPLAIN
  102. # EXPLAIN /*!50100 PARTITIONS*/
  103. select * from  tig_pairs where (nid = 40418) AND (pkey = 'roster')\G
  104.  
  105. # Query 3: 11.70 QPS, 0.09x concurrency, ID 0x3A2DF5C4FB9A3168 at byte 1206612317
  106. # Scores: Apdex = 1.00 [1.0], V/M = 0.04
  107. # Query_time sparkline: |  _^__  |
  108. # Time range: 2012-06-22 06:46:52 to 08:09:01
  109. # Attribute    pct   total     min     max     avg     95%  stddev  median
  110. # ============ === ======= ======= ======= ======= ======= ======= =======
  111. # Count          3   57665
  112. # Exec time      7    420s   482us   902ms     7ms    30ms    18ms     2ms
  113. # Lock time      3      5s    40us    16ms    84us   131us    86us    73us
  114. # Rows sent      0       0       0       0       0       0       0       0
  115. # Rows examine   1  56.31k       1       1       1       1       0       1
  116. # Query size     0   2.70M      45      50   49.05   49.17    0.83   46.83
  117. # String:
  118. # Databases    tigasedb
  119. # Hosts        localhost
  120. # Users        tigase_user
  121. # Query_time distribution
  122. #   1us
  123. #  10us
  124. # 100us  #######
  125. #   1ms  ################################################################
  126. #  10ms  ###############
  127. # 100ms  #
  128. #    1s
  129. #  10s+
  130. CALL TigUserLogout('+39127262@xmpp.xgate.com.hk')\G
  131.  
  132. # Query 4: 83.31 QPS, 0.03x concurrency, ID 0xA73D290AB3EA03C3 at byte 807559265
  133. # Scores: Apdex = 1.00 [1.0], V/M = 0.01
  134. # Query_time sparkline: |  ^___  |
  135. # Time range: 2012-06-22 06:42:37 to 08:09:01
  136. # Attribute    pct   total     min     max     avg     95%  stddev  median
  137. # ============ === ======= ======= ======= ======= ======= ======= =======
  138. # Count         28  431889
  139. # Exec time      2    145s   100us   379ms   334us   424us     1ms   247us
  140. # Lock time     23     38s       0    24ms    88us   138us    93us    76us
  141. # Rows sent     18 421.74k       0       1    1.00    0.99    0.01    0.99
  142. # Rows examine  11 421.74k       0       1    1.00    0.99    0.01    0.99
  143. # Query size     1  21.05M      47      52   51.10   51.63    1.04   49.17
  144. # String:
  145. # Databases    tigasedb
  146. # Hosts        localhost
  147. # Users        tigase_user
  148. # Query_time distribution
  149. #   1us
  150. #  10us
  151. # 100us  ################################################################
  152. #   1ms  #
  153. #  10ms  #
  154. # 100ms  #
  155. #    1s
  156. #  10s+
  157. CALL TigGetUserDBUid('+3917211@xmpp.xgate.com.hk')\G
  158.  
  159. # Query 5: 59.29 QPS, 0.03x concurrency, ID 0x9BC12AC7409886BB at byte 536484383
  160. # Scores: Apdex = 1.00 [1.0], V/M = 0.00
  161. # Query_time sparkline: |  ^___  |
  162. # Time range: 2012-06-22 06:42:37 to 08:09:01
  163. # Attribute    pct   total     min     max     avg     95%  stddev  median
  164. # ============ === ======= ======= ======= ======= ======= ======= =======
  165. # Count         20  307336
  166. # Exec time      2    136s   132us   197ms   442us   568us     1ms   348us
  167. # Lock time     11     19s       0   111ms    60us    93us   227us    49us
  168. # Rows sent     13 300.13k       0       1    1.00    0.99    0.00    0.99
  169. # Rows examine   7 300.13k       0       1    1.00    0.99    0.00    0.99
  170. # Query size     1  28.79M      94      99   98.22   97.36    0.12   97.36
  171. # String:
  172. # Databases    tigasedb
  173. # Hosts        localhost
  174. # Users        tigase_user
  175. # Query_time distribution
  176. #   1us
  177. #  10us
  178. # 100us  ################################################################
  179. #   1ms  #
  180. #  10ms  #
  181. # 100ms  #
  182. #    1s
  183. #  10s+
  184. # Tables
  185. #    SHOW TABLE STATUS FROM `tigasedb` LIKE 'tig_nodes'\G
  186. #    SHOW CREATE TABLE `tigasedb`.`tig_nodes`\G
  187. # EXPLAIN /*!50100 PARTITIONS*/
  188. select nid as nid1 from tig_nodes where (uid = 32423) AND (parent_nid is null) AND (node = 'root')\G
  189.  
  190. # Query 6: 17.70 QPS, 0.13x concurrency, ID 0xFC6FB130BCD2AC4A at byte 1496940393
  191. # Scores: Apdex = 1.00 [1.0], V/M = 0.04
  192. # Query_time sparkline: |  _^__  |
  193. # Time range: 2012-06-22 07:07:51 to 07:23:49
  194. # Attribute    pct   total     min     max     avg     95%  stddev  median
  195. # ============ === ======= ======= ======= ======= ======= ======= =======
  196. # Count          1   16955
  197. # Exec time      2    122s   512us   506ms     7ms    28ms    18ms     2ms
  198. # Lock time      0      1s    43us     2ms    85us   131us    38us    76us
  199. # Rows sent      0  16.56k       1       1       1       1       0       1
  200. # Rows examine   0       0       0       0       0       0       0       0
  201. # Query size     0 658.99k      39      42   39.80   40.45    0.58   38.53
  202. # String:
  203. # Databases    tigasedb
  204. # Hosts        localhost
  205. # Users        tigase_user
  206. # Query_time distribution
  207. #   1us
  208. #  10us
  209. # 100us  ########
  210. #   1ms  ################################################################
  211. #  10ms  ##############
  212. # 100ms  #
  213. #    1s
  214. #  10s+
  215. CALL TigAddNode(316751, 85207, 'XGate')\G
  216.  
  217. # Query 7: 11.84 QPS, 0.03x concurrency, ID 0xDCDFAA0337705F47 at byte 790773599
  218. # Scores: Apdex = 1.00 [1.0], V/M = 0.00
  219. # Query_time sparkline: |  _^__  |
  220. # Time range: 2012-06-22 06:42:38 to 07:25:00
  221. # Attribute    pct   total     min     max     avg     95%  stddev  median
  222. # ============ === ======= ======= ======= ======= ======= ======= =======
  223. # Count          1   30104
  224. # Exec time      1     86s   222us   174ms     3ms     4ms     4ms     2ms
  225. # Lock time      2      4s       0     5ms   124us   185us    87us   103us
  226. # Rows sent     65   1.44M       0      50   50.00   49.17    0.28   49.17
  227. # Rows examine  38   1.44M       0      50   50.00   49.17    0.28   49.17
  228. # Query size     2  45.78M   1.51k   1.61k   1.56k   1.53k   23.46   1.53k
  229. # String:
  230. # Databases    tigasedb
  231. # Hosts        localhost
  232. # Users        tigase_user
  233. # Query_time distribution
  234. #   1us
  235. #  10us
  236. # 100us  #
  237. #   1ms  ################################################################
  238. #  10ms  #
  239. # 100ms  #
  240. #    1s
  241. #  10s+
  242. # Tables
  243. #    SHOW TABLE STATUS FROM `tigasedb` LIKE 'tig_users'\G
  244. #    SHOW CREATE TABLE `tigasedb`.`tig_users`\G
  245. # EXPLAIN /*!50100 PARTITIONS*/
  246. SELECT user_id FROM tig_users WHERE user_id IN ('+3918825@xmpp.xgate.com.hk', '+3918826@xmpp.xgate.com.hk', '+3918827@xmpp.xgate.com.hk', '+3918828@xmpp.xgate.com.hk', '+3918829@xmpp.xgate.com.hk', '+3918830@xmpp.xgate.com.hk', '+3918831@xmpp.xgate.com.hk', '+3918832@xmpp.xgate.com.hk', '+3918833@xmpp.xgate.com.hk', '+3918834@xmpp.xgate.com.hk', '+3918835@xmpp.xgate.com.hk', '+3918836@xmpp.xgate.com.hk', '+3918837@xmpp.xgate.com.hk', '+3918838@xmpp.xgate.com.hk', '+3918839@xmpp.xgate.com.hk', '+3918840@xmpp.xgate.com.hk', '+3918841@xmpp.xgate.com.hk', '+3918842@xmpp.xgate.com.hk', '+3918843@xmpp.xgate.com.hk', '+3918844@xmpp.xgate.com.hk'/*... omitted 30 items ...*/)\G
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!