CodeCharge TAIWAN

代碼: 選擇全部

pt-query-digest mysql-slow.log.1 > mulin

代碼: 選擇全部

# 17.7s user time, 300ms system time, 41.43M rss, 106.14M vsz
# Current date: Sun Jul 12 08:21:48 2015
# Hostname: li497-78
# Files: mysql-slow.log.1
# Overall: 45.17k total, 845 unique, 0.53 QPS, 2.92x concurrency _________
# Time range: 2015-07-11 06:53:47 to 2015-07-12 06:33:02
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time        248692s    69us     73s      6s     19s     10s      2s
# Lock time        122552s    14us     35s      3s     10s      4s   857ms
# Rows sent          2.86M       0   1.60M   66.50    7.70   7.42k       0
# Rows examine       1.74G       0   1.60M  40.34k 174.27k  71.66k   1.78k
# Query size        33.76M      18   4.28k  783.65 1012.63  300.24  793.42

# Profile
# Rank Query ID           Response time    Calls R/Call  V/M   Item
# ==== ================== ================ ===== ======= ===== ===========
#    1 0xB0E2B379CE83BD98 83508.9334 33.6%  4945 16.8875 16.81 SELECT stock_in stock_in_a prod prod_stock
#    2 0x15BB7029DB8B6D1B 67740.9291 27.2%  4702 14.4068 14.99 SELECT stock_in stock_in_a prod prod_stock
#    3 0x508DB1DE67F13FDA 18844.9922  7.6%  4092  4.6053  3.42 SELECT stock_tranin stock_tranin_a prod prod_stock
#    4 0x0B426DF25954209F 17310.9656  7.0%  3700  4.6786  3.61 SELECT stock_out_back prod prod_stock p_return_d
#    5 0xF41A587FB5AFC8CE 16754.1004  6.7%  3862  4.3382  3.36 SELECT prod_prod? store prod prod_stock
#    6 0x6348152796D9C0BB 15518.3178  6.2%  3976  3.9030  3.84 SELECT stock_tranout stock_tranout_a prod prod_stock
#    7 0x6B883A7EA5E6AACE 13203.4431  5.3%  3727  3.5426  4.43 SELECT prod_prod? store prod prod_stock
#    8 0xBF931269BC975083 12469.0602  5.0%  4087  3.0509  5.45 SELECT p_return p_return_d prod prod_stock
#    9 0x98384C092E4C4859  1320.0724  0.5%   120 11.0006  1.79 UPDATE prod_stock
#   11 0x8C4801C0E6CCD6A2   567.8751  0.2%   120  4.7323  0.45 UPDATE prod_stock
#   12 0x80D8B55679945BC5   341.2033  0.1%   117  2.9163  2.50 SELECT sell sell_a prod store prod_stock
#   13 0xE08E853065709AB4   179.4694  0.1%   120  1.4956  4.38 UPDATE prod_stock
#   14 0x20BED5AD6C45BEDD    77.8172  0.0%   120  0.6485  5.60 UPDATE prod_stock
# MISC 0xMISC               854.5120  0.3% 11481  0.0744   0.0 <832 ITEMS>
# Query 1: 1.01 QPS, 17.13x concurrency, ID 0xB0E2B379CE83BD98 at byte 40276044
# This item is included in the report because it matches --limit.
# Scores: V/M = 16.81
# Time range: 2015-07-12 02:00:05 to 03:21:19
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         10    4945
# Exec time     33  83509s   813ms     71s     17s     52s     17s     10s
# Lock time     10  12445s    47us     35s      3s      9s      3s   857ms
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine  47 850.81M 176.18k 176.18k 176.18k 176.18k       0 176.18k
# Query size    10   3.65M     773     773     773     773       0     773
# String:
# Databases    mulin
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ##
#    1s  ##########################################################
#  10s+  ################################################################
# Tables
#    SHOW TABLE STATUS FROM `mulin` LIKE 'stock_in'\G
#    SHOW CREATE TABLE `mulin`.`stock_in`\G
#    SHOW TABLE STATUS FROM `mulin` LIKE 'stock_in_a'\G
#    SHOW CREATE TABLE `mulin`.`stock_in_a`\G
#    SHOW TABLE STATUS FROM `mulin` LIKE 'prod'\G
#    SHOW CREATE TABLE `mulin`.`prod`\G
#    SHOW TABLE STATUS FROM `mulin` LIKE 'prod_stock'\G
#    SHOW CREATE TABLE `mulin`.`prod_stock`\G
# EXPLAIN /*!50100 PARTITIONS*/
select prod_stock.store_id,
                                                                         prod_stock.stock_id,
                                                                         prod.prod_id,
                                                                         prod.prod_sub_b_id,
                                                                         prod.price_in,
                                                                         prod.supply_price,
                                                                         prod_stock.dms,
                                                                         prod_stock.stocks,
                                                                         sum(stock_in_a.free_amount) as amount
                                                                from stock_in
                                                  Inner Join stock_in_a ON stock_in.stock_in_id = stock_in_a.stock_in_id
                                                  Inner Join prod ON stock_in_a.prod_id = prod.prod_id
                                                  Inner Join prod_stock ON stock_in.store_id = prod_stock.store_id
AND stock_in_a.prod_id = prod_stock.prod_id
                                                           where stock_in.date >= '2015-08-15 00:00:00' and stock_in.date <= '2015-08-15 23:59:59'
                                                        group by prod_stock.store_id,
                                                                         prod_stock.stock_id,
                                                                         prod.prod_id
                                                          having amount > 0\G