Vc-mysql-sniffer statistics MySQL SQL distribution

Sometimes we need to count the SQL execution on the line, like to know which SQL performs the most frequently, we can turn on General_log, and then do statistics, but general_log turn on very lossy performance, then we can use Vc-mysql-sniffer instead , the tool is compiled binary, download can be used. ：

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

Here I have modified a script on the web to analyze the results of the Vc-mysql-sniffer crawl to statistics frequently executed SQL.

#!/usr/bin/python#Coding:utf8#python analysis-vc-log.py 3315 | sort | uniq-c | Sort-nr |head-nImportReImportSYSImportOSImportCommandsvc_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/, "");p rint} ' |awk begin{rs=eof} ' {gsub (/myxxxxx/, "\\n");p rint} ' >/tmp/vc_mysql_%s.txt"""%(port,port) Outtext=commands.getoutput (cmd) file="/tmp/vc_mysql_%s.txt"%(port) Logfo=open (file) forLineinchLogfo:line= Re.sub (r"\ n","", line) Linematch= Re.match (r".*", line)iflinematch:linetmp=linematch.group (0)#Remove Extra SpaceLinetmp = 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 = xLinetmp = 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 limitLinetmp = Re.sub (r"limit.*","Limit", linetmp)Printlinetmplogfo.close ()

3310 Sort Uniq Sort -nr | Head Ten

Crawl the MySQL 3310 port and statistically perform the most frequent 10 sql. I'm not going to put it up here. Interested classmates to test themselves.

Resources:

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

Vc-mysql-sniffer statistics MySQL SQL distribution