你的位置:首页 > 软件开发 > 数据库 > index merge的一次优化

index merge的一次优化

发布时间:2016-05-25 18:00:35
手机微博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 (#换成@)。

可能感兴趣文章

我的浏览记录