手机微博4040端口SQL优化现象某端口常态化延迟,通过使用pt-query-digest发现主要由于一条count(*)语句引发,具体如下:# 13.5s user time, 40ms system time, 21.58M rss, 156.84M vsz# Current ...
手机微博4040端口SQL优化
现象
某端口常态化延迟,通过使用pt-query-digest发现主要由于一条count(*)语句引发,具体如下:
# 13.5s user time, 40ms system time, 21.58M rss, 156.84M vsz# Current date: Fri Apr 1 17:43:05 2016# Hostname: naga64# Files: /data1/mysql4040/slow.log# Overall: 45.87k total, 53 unique, 1.01 QPS, 9.05x concurrency __________# Time range: 2016-04-01 05:05:02 to 17:43:05# Attribute total min max avg 95% stddev median# ============ ======= ======= ======= ======= ======= ======= =======# Exec time 411622s 1s 238s 9s 29s 13s 6s# Lock time 70s 0 4s 2ms 138us 57ms 76us# Rows sent 12.66M 0 1.31M 289.43 19.46 13.90k 0.99# Rows examine 310.43M 0 5.40M 6.93k 31.59k 65.56k 0.99# Query size 5.89M 17 4.14k 134.67 563.87 150.53 76.28 # Profile# Rank Query ID Response time Calls R/Call Apdx V/M Item# ==== ================== ================= ===== ======= ==== ===== =====# 1 0xE74340EE1DEFEC99 317229.0380 77.1% 34627 9.1613 0.11 12.60 SELECT user_rec_?# 2 0xB9959C570826EFA4 72164.9508 17.5% 3746 19.2645 0.15 36.13 SELECT app# 3 0xECEF2B7CA2BE445C 7136.5824 1.7% 3581 1.9929 0.53 2.75 SELECT user_rec_?# 4 0x7B9529D6435F23B3 3465.0381 0.8% 137 25.2922 0.16 33.53 SELECT app# 5 0x270C8D7D3EC37561 2209.2050 0.5% 1087 2.0324 0.51 2.34 SELECT apk# 6 0x6AF45A776EDFF7A9 1921.4956 0.5% 905 2.1232 0.50 2.63 SELECT apk# 7 0x67DC38C9C5F7EEBB 1816.0314 0.4% 108 16.8151 0.08 7.32 SELECT ios_apk# 8 0x5F7E7D2BFA8FB79B 1388.2303 0.3% 518 2.6800 0.49 10.45 SELECT apk cooper# 9 0x79F2C2072394C9BB 1005.4780 0.2% 656 1.5327 0.59 1.64 SELECT user_rec_?b# 10 0x3229403E99601A69 632.3939 0.2% 81 7.8073 0.07 1.07 SELECT ios_app# 11 0x83D4C6B0BB535E12 506.5923 0.1% 15 33.7728 0.10 11.12 SELECT apk# 13 0x2F002402DBB98EE9 226.3586 0.1% 73 3.1008 0.42 4.04 SELECT app# 14 0x992F97D6C4D52DF6 219.2329 0.1% 44 4.9826 0.19 2.00 SHOW STATUS# 16 0x791C5370A1021F19 140.2855 0.0% 30 4.6762 0.25 1.87 SHOW SLAVE STATUS# 18 0x2F27EBCFABB23992 110.6802 0.0% 36 3.0744 0.40 2.47 SELECT app_recommend app# 19 0x980736573219087A 108.8593 0.0% 15 7.2573 0.00 0.45 SELECT ios_app_free ios_app# 20 0x58492BB2C89253D8 71.5322 0.0% 10 7.1532 0.05 0.57 SELECT ios_app_free ios_app# 21 0x0EB86D9E4630253A 61.5251 0.0% 27 2.2787 0.52 0.33 SELECT ios_app_recommend ios_app# 22 0x398799E91C3C2AAD 59.5222 0.0% 12 4.9602 0.33 3.46 SELECT apk cooper# 24 0x53148D850C2E022E 45.0953 0.0% 11 4.0996 0.23 1.04 SELECT ios_app# 25 0x07387FA6467B3DB9 34.6657 0.0% 17 2.0392 0.50 0.39 SELECT app_recommend app# 26 0xBD799CC975081065 31.1719 0.0% 16 1.9482 0.47 0.51 SELECT app# 27 0xB7F06103A7ADA5C0 30.4686 0.0% 13 2.3437 0.42 0.52 SELECT user_rec_?d# 30 0x188747BC3CB9728B 19.8929 0.0% 12 1.6577 0.58 0.22 SELECT app_recommend app# MISC 0xMISC 987.4775 0.2% 92 10.7335 NS 0.0 <29 ITEMS> # Query 1: 0.76 QPS, 6.97x concurrency, ID 0xE74340EE1DEFEC99 at byte 2753434# This item is included in the report because it matches --limit.# Scores: Apdex = 0.11 [1.0], V/M = 12.60# Query_time sparkline: | ^_|# Time range: 2016-04-01 05:05:02 to 17:43:04# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ======= ======= =======# Count 75 34627# Exec time 77 317229s 1s 174s 9s 23s 11s 7s# Lock time 55 39s 46us 3s 1ms 119us 46ms 73us# Rows sent 0 31.80k 0 1 0.94 0.99 0.23 0.99# Rows examine 0 22.97k 0 5 0.68 0.99 0.55 0.99# Query size 44 2.61M 76 79 79.00 76.28 0.02 76.28# String:# Databases apps# Hosts# Users apps_r# Query_time distribution# 1us# 10us# 100us# 1ms# 10ms# 100ms# 1s ################################################################# 10s+ ######################## Tables# SHOW TABLE STATUS FROM `apps` LIKE 'user_rec_07'\G# SHOW CREATE TABLE `apps`.`user_rec_07`\G# EXPLAIN /*!50100 PARTITIONS*/select count(*) as total from user_rec_07 where type=5 and weiboId='1934676487'\G
原标题:index merge的一次优化
关键词:
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。