需要记录慢查询,在配置文件中加入慢查询的相关配置。
/etc/my.cnf
general_log=off
log_output=table
slow_query_log=1
long_query_time=0.3
general_log=off 会记录所有的查询,建议关闭(set global general_log = off)
log_output=table 将log记录到表中
slow_query_log=1 开启慢查询记录
long_query_time=0.3 大于0.3秒的查询会被记录
配置好以后,我的做法是使用python每周读取数据库中的记录,邮件发出来以便分析。
sql如下: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
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_text
FROM
slow_log
WHERE
sql_text NOT LIKE '%SQL_NO_CACHE%'
GROUP BY
LEFT (sql_text, 40)
ORDER BY
count(*) DESC
LIMIT 20;
部分python代码如下: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" % (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)
得到的邮件部分:
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
这样就可以看到每周比较慢的查询,然后进行优化分析。