vc-mysql-sniffer統計MySQL的SQL分布

標籤：style   grep   close   連接埠   mysq   pre   body   sql   war   

有時候我們需要統計線上的SQL執行情況，比如想知道哪條SQL執行最頻繁，我們可以開啟general_log，然後進行統計，但是general_log開啟非常損耗效能，那麼我們可以使用vc-mysql-sniffer來代替，改工具是編譯好的二進位，下載即可使用。：

https://www.vividcortex.com/resources/network-analyzer-for-mysql

我這裡修改了網上的一個指令碼，分析vc-mysql-sniffer抓取到的結果統計頻繁執行的SQL。

#!/usr/bin/python#coding:utf8# python analysis-vc-log.py 3315 | sort | uniq -c | sort -nr |head -n 10import reimport sysimport osimport commandsvc_sniffer_time=5port=sys.argv[1]vc_cmd=""" /usr/bin/timeout %s  /data/software/vc-mysql-sniffer -binding="[::]:%s"  > /tmp/tmp_vc_mysql_%s.txt """ % (vc_sniffer_time,port,port)outtext = commands.getoutput(vc_cmd)cmd=""" grep -Ev ‘# Time:|# [email protected]‘ /tmp/tmp_vc_mysql_%s.txt |sed ‘s/# Query_time.*/myxxxxx/g‘ |awk BEGIN{RS=EOF}‘{gsub(/\\n/," ");print}‘|awk BEGIN{RS=EOF}‘{gsub(/myxxxxx/,"\\n");print}‘ >/tmp/vc_mysql_%s.txt""" % (port,port)outtext = commands.getoutput(cmd)file="/tmp/vc_mysql_%s.txt" % (port)logFo = open(file)for line in logFo:    line = re.sub(r"\n","",line)    lineMatch = re.match(r".*",line)    if lineMatch:        lineTmp = lineMatch.group(0)        # remove extra space        lineTmp = re.sub(r"\s+", " ",lineTmp)        # replace values (value) to values (x)        lineTmp = re.sub(r"values\s*\(.*?\)", "values (x)",lineTmp)        # replace filed = ‘value‘ to filed = ‘x‘        lineTmp = re.sub(r"(=|>|<|>=|<=)\s*(‘|\").*?\2","\\1 ‘x‘",lineTmp)        # replace filed = value to filed = x        lineTmp = re.sub(r"(=|>|<|>=|<=)\s*[0-9]+","\\1 x",lineTmp)        # replace like ‘value‘ to like ‘x‘        lineTmp = re.sub(r"like\s+(‘|\").*?\1","like ‘x‘",lineTmp)        # replace in (value) to in (x)        lineTmp = re.sub(r"in\s+\(.*?\)","in (x)",lineTmp)        # replace limit x,y to limit        lineTmp = re.sub(r"limit.*","limit",lineTmp)        print lineTmplogFo.close()

python analysis-vc-log.py 3310 | sort | uniq -c | sort -nr |head -n 10

抓取mysql 3310連接埠，統計執行最頻繁的10條SQL。結果我這裡就不貼上來了。有興趣的同學自己測試。

 

參考資料：

https://www.centos.bz/2015/02/analysis-mysql-general-log/

vc-mysql-sniffer統計MySQL的SQL分布