MySQL Slow_log record and get

A slow query needs to be logged and a configuration file with a slow query is added.

/etc/my.cnf

General_log=off

Log_output=table

Slow_query_log=1

long_query_time=0.3

• General_log=off will log all queries and suggest close (set global general_log = off)

• Log_output=table log logs to the table

• Slow_query_log=1 turn on slow query logging

• long_query_time=0.3 queries that are greater than 0.3 seconds are logged

Once configured, my approach is to use Python to read the records in the database every week, and the messages to be sent out for analysis.

SQL is as follows:

Select    max (User_host),     min (start_time),     Max (start_time),     count (*),     sum (         case        when time_to_sec (query_time)  =  0 THEN            0.3         else            time _to_sec (query_time)         END    ),     max (        CASE         when time_to_sec (query_time)  = 0 THEN             0.3        ELSE        &nbsP;    time_to_sec (query_time)         END     ),     min (        CASE         when time_to_sec (query_time)  = 0 THEN             0.3         else            time_to_sec (query_time )         END    ),     round (         avg (             case            when time_ To_sec (query_time)  = 0 THEN                 0.3            else                 time_to_sec (query_time)              END        ),         2    ),     sql_textfrom     slow_logWHERE    sql_text NOT LIKE  '%sql_no_cache% ' group by    left  (SQL_TEXT, 40) order by    count (* )  DESCLIMIT 20;

Some Python codes are as follows:

For i in range (LEN (conn)):             log=p.get_table (Conn[i],sql)              query_log+= ' _ ' *120+ ' \ r \ n '             query_log+= ' slow_log_%s:%s____%s\r\n\r\n " %  (conn[i][" host "],conn[i][" Port "],days)              for n in range (len (log)):                  query_log+= "%s  %s   %s  %s  %s  " %  (str (log[n][3)), str (log[n][4]), str (log[n][5]), STR (log[n][6]), str (log[n][7))                  query_log+= "user_host :%s    start_time :%s    end_time :%s\r\n " %&NBSP (STR (log[n][0]), str (log[n][1]), str (log[n][2]))                  query_log+= '   ' *40+squeeze (str (log[n][8)) + ' \r\n\r\n '                           mail.send_mail (Mail_list,mail_title,query_log)

Get the message part:

Slow_log_x.x.x.x:3306____2016-05-26

1889 704.4 33.0 0.3 0.37 user_host:x[x] @ [x] start_time:2016-05-25 09:30:27 end_time:2016-05-26 09:16:05

Select Max (Online_user), round (sum (online_user)/count (ID)) from online where datetime >= ' 2016-05-25 ' and server_id = 27

This allows you to see a slower query per week and then perform an optimization analysis.

This article is from the "Hanchengway" blog, make sure to keep this source http://hanchengway.blog.51cto.com/10974268/1783520

MySQL Slow_log record and get