Shulou(Shulou.com)06/01 Report--

Analyze and statistics the MySQL general log to find out the SQL with the most queries

When we need to optimize MySQL queries, the first thing that comes to mind is to turn on slow logs, which can see SQL statements that take more than a certain amount of time to execute and SQL that are not using indexes. But if we want to analyze the distribution of all SQL queries, that is, which type of SQL query has the most times, we can turn on general log to count.

Open general logmysql > show variables like'% general%'

+-+ +

| | Variable_name | Value |

+-+ +

| | general_log | OFF |

| | general_log_file | / usr/local/mysql/data/localhost.log |

+-+ +

Mysql > set global general_log = "ON"; analysis-general-log.py script #! / usr/bin/python # sort and count mysql general log# Author: Jason# Url: www.centos.bz# Email: admin#centos.bz# Created: UTC 2015-02-15 17:51:53 import reimport sysimport os if len (sys.argv) = = 2: logPath = sys.argv [1] if not os.path.exists (logPath): print ("file" + logPath + "does not exists.") Sys.exit (1) else: print ("Usage:" + sys.argv [0] + "logPath") sys.exit (1) logFo = open (logPath) match = 0 for line in logFo: line = re.sub (r "\ n", ", line) if match = 0: # match line begin with numbers lineMatch = re.match (r"\ s + [0-9] +\ sroom.* ", line Flags=re.I) if lineMatch: lineTmp = lineMatch.group (0) match = match + 1 continue elif match = = 1: # match line begin with numbers lineMatch = re.match (r "\ s + [0-9] +\ sroom.*", line,flags=re.I) if lineMatch: # match only query lineMatchQuery = re.match (r ". * Query\ s + (. *)" LineTmp,flags=re.I) if lineMatchQuery: lineTmp = lineMatchQuery.group (1) # remove extra space lineTmp = re.sub (r "\ s +", ", lineTmp) # replace values (value) to values (x) lineTmp = re.sub (r" values\ s *\ (. *?\) "," values (x) ", lineTmp Flags=re.I) # replace filed = 'value' to filed =' x' lineTmp = re.sub (r "(= | > | = |

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.