https://www.percona.com/downloads/percona-toolkit/
代碼: 選擇全部
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