26 Aug 2009

过滤大于200秒的慢查询

日志格式

# User@Host: root[root] @ svr56.okooo [192.168.8.56]

# Query_time: 44 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

/*!40000 ALTER TABLE `TogetherBuyTop` ENABLE KEYS */;

# Time: 090825 1:52:41

# User@Host: root[root] @ svr56.okooo [192.168.8.56]

# Query_time: 255 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

—

SELECT SponsorName,LotteryType,LotteryNo,SingleMulti,ConsignType,ProjectID,SponsorID,TotalMoney,RawPrize,Status FROM NewProject WHERE LotteryType = ‘WDL’ AND SponsorID = ‘174760’ AND ConsignType = ‘Together’ Order By ProjectID DESC LIMIT 0,50;

# User@Host: root[root] @ svr34.okooo [192.168.8.34]

# Query_time: 3 Lock_time: 0 Rows_sent: 309 Rows_examined: 101341

SELECT DISTINCT TableItem FROM ArenaTable WHERE TableType = ‘LotteryNo’ ORDER BY TableItem DESC;

# User@Host: root[root] @ svr36.okooo [192.168.8.36]

# Query_time: 255 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

# Time: 090825 11:37:53

# User@Host: root[root] @ svr30.okooo [192.168.8.30]

# Query_time: 5 Lock_time: 0 Rows_sent: 50 Rows_examined: 601

SELECT SponsorName,LotteryType,LotteryNo,SingleMulti,ConsignType,ProjectID,SponsorID,TotalMoney,RawPrize,Status FROM NewProject WHERE LotteryType = ‘ToTo’ AND SponsorID = ‘206226’ AND ConsignType = ‘Together’ Order By ProjectID DESC LIMIT 0,50;

# User@Host: root[root] @ svr34.okooo [192.168.8.34]

# Query_time: 237 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

—

SELECT T1.MatchID,T1.MakerID,T3.MakerCnName,T1.HandicapNumber,T2.HandicapName,OverLine,UnderLine,HandicapType,T1.LastTime

FROM MatchHandicap AS T1 LEFT JOIN HandicapType AS T2 ON T1.HandicapNumber = T2.HandicapNumber

LEFT JOIN BookMaker AS T3 ON T1.MakerID=T3.MakerID

几个注意事项

为了不漏掉可能的查询，我显示了匹配行的下5行。

grep 无法直接 awk ‘$2 ~ /Query_time/{if ($3>=200){print $2,$3}}’ /logs/mysql/slow.log ，因为换行符？

#!/bin/bash
for i in `awk '$2 ~ /Query_time/{if ($3>=200){print $3}}' /logs/mysql/slow.log`
do
        grep -B 5 "Query_time: $i" /logs/mysql/slow.log
done

grep 打印上下文5行

grep -A 5 -B 5