过滤大于200秒的慢查询

By admin|工作笔记|Be the first to comment!

日志格式

# 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