Analyze MySQL slow log script and quickly locate slow SQL

Analysis of MySQL Slow log is the operation of the work, not less. To quickly locate slow SQL, and to optimize SQL after discovery and to modify the business, ensure that the database works stably and efficiently. Here is my work to solve the idea ...

1. View local database slow log files first

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4B/F1/wKioL1Q15smTPF1_AAUQZO2uypA757.jpg "title=" 01.png "alt=" Wkiol1q15smtpf1_aauqzo2uypa757.jpg "/>

2. Writing analysis slow log scripts

#!/usr/bin/python#coding=utf-8   #字符编码import  re   #导入正则匹配模块import  time # Import Time Module import sys   #导入sys模块import  MySQLdb  #导入连接mysql模块canshu =len (SYS.ARGV)  # Number of parameters Def help ():     #帮助函数     print  "Analysis of the day slow Log execution command python %s  today today " %sys.argv[0]    print " Analysis of previous slow log execution commands python %s  before  Log Name " %sys.argv[0]def create_table ():    #  Open database connection      db=mysqldb.connect ("192.168.1.1", "Test", "Test", "Log_fenxi")     #  use cursor () method gets the operation cursor     cursor=db.cursor ()     #  If the data table already exists using  execute ()   method to delete a table.     cursor.execute ("drop table if exists  ' Mysql_slow ';")     #  Create a data table SQL statement     sql= "" "create table  ' Mysql_slow '   (       ' id '  int (one)   unsigned NOT NULL AUTO_INCREMENT,        ' Query_time '  float (11,6)  NOT NULL,       ' Lock_ Time '  char (one)  NOT NULL,       ' rows_sent '  int (one)  not  NULL,       ' rows_examined '  int (one)  NOT NULL,        ' time '  datetime NOT NULL,       ' Slow_sql '  text NOT NULL,       PRIMARY KEY  (' id ')      )  ENGINE=InnoDB DEFAULT CHARSET=utf8; "" "     cursor.execute (SQL)     #  Close database connections      Db.close () def insert_table ():     log_file=open (log_name)   #读取慢日志      #  Open Database Connection     db=mysqldb.connect("192.168.1.1", "Test", "Test", "Log_fenxi")     #  get an operation cursor using the cursor () method      cursor = db.cursor ()     for line in log_file:         line=line.strip ()         query_ Time=re.search (' Query_time: (\s\d+\.\d+) ', line)   #正则匹配慢日志时间          Lock_time=re.search (' Lock_time: (\s\d+\.\d+) ', line)     #正则匹配锁定时间          rows_sent=re.search (' Rows_sent: (\s\d+) ', line)      #正则匹配返回结果好多行数据          rows_examined=re.search (' rows_examined: (\s\d+) ', line)      #正则匹配扫描好多行数据         timestamp=re.search (' timestamp= (\d+) ', line)     #正则匹配时间戳         slow_sql=re.match (' (select.*?); ', Line)     #正则匹配慢sql        if query_time:            query_time_new=query_time.group (1). Strip ()    #匹配正则结果赋值          if Lock_time:            lock_time_new=lock_time.group (1). Strip ()    #匹配正则结果赋值          if rows_sent:           rows_sent_new= Rows_sent.group (1). Strip ()    #匹配正则结果赋值         if rows_ Examined:           rows_examined_new=rows_ Examined.group (1). Strip ()    #匹配正则结果赋值         if  Timestamp:           timestamp=int (Timestamp.group (1))        &nbsP;   timearray=time.localtime (timestamp)             sql_time=time.strftime ("%y-%m-%d %h:%m:%s",  timearray)    #匹配正则结果赋值          if slow_sql:             slow_sql_new=slow_sql.group ()    #匹配正则结果赋值              # SQL  INSERT Statement              sql =  "" "Insert into mysql_slow (query_time,lock_time,rows_sent,rows_examined, Time,slow_sql)                  values  ("" "+query_time_new+" "" "" "" "+lock_time_new+" "" "" "" +rows_sent_new+ "" "" "" +rows_examined_new+ "" "" "" " +sql_time+ "" "" "" "" "" +slow_sql_new+ "" "" "" ";             try:               #  Execute SQL statement                 cursor.execute (SQL)                 #  submit to database execution                 db.commit ()              except:                # Rollback in case there is any  Error               db.rollback ()     log_file.close ()     #  Close database connection     db.close () Def main ():    global log_name    if canshu!=3:        print  "Wrong number of parameters, please check!"        help ()     else:        create_table ()        xuanze=sys.argv[1]    #第一个参数 (Slow log time)        log_before=sys.argv[2]  #慢日志具体时间          if xuanze== ' Today ':             log_name= '/data/mysqlp/mysql-slow.log '              insert_table ()        elif xuanze== ' before ':             log_name= '/data/logs/mysql_slow/%s '  %log_ Before            insert_table ()         else:            print  ' parameter type selection error, type only contains Today|before '              help () Main ()

3. The results of the script processing exist in the database and view the database records.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4B/EF/wKiom1Q158KhlUK3AARIpwQ-Zk4155.jpg "title=" 02.jpg "alt=" Wkiom1q158khluk3aaripwq-zk4155.jpg "/>

Note:

If you want to quickly locate those SQL problems, simply order the Query_time and rows_examined fields in descending order to locate them. You can manually query the sort in front of SQL, and if you find it slower you need to optimize SQL and modify your business.

If you want to use this script directly, you need to modify the database connection IP and user password, but also need to install the MYSQLDB module on the log library, and need to modify the current slow log path and name/data/mysqlp/mysql-slow.log, and keep the path of slow log/data/ Logs/mysql_slow, these two paths should be modified according to their storage path in order to use this script properly!

This article is from the "Chengdu @ Ah-like" blog, please be sure to keep this source http://azhuang.blog.51cto.com/9176790/1561517

Analyze MySQL slow log script and quickly locate slow SQL