监控系统监控到我们的程序变慢了,怀疑是sql的原因,要怎么去分析排查呢?一般按照如下几个步骤进行: - 开启慢查询日志,设置阀值,将慢sql从日志中抓取出来;
- show profile,查询sql的执行细节和生命周期情况;
本文先来讲讲慢查日志。 1. 是什么? MySQL慢查日志是一种记录执行很慢的sql的日志。MySQL中可以通过long_query_time 来设置一个阀值,如果执行时间超过这个阀值,就会被认为是慢sql,就会记录到慢查日志中。该阀值默认是10s,即执行超过10s的sql就会被记录。 2. 怎么用? MySQL默认是没有开启慢查日志的,如果不是调优需要的话,不建议开启,因为这个也会带来性能的影响。执行以下sql可以查询是否开启慢查日志以及慢查日志的路径: show variables like '%slow_query_log%';
执行结果可以看到默认是off的。执行以下sql即可开启: set global slow_query_log = 1;
执行结果可以看到,已经变成on了,也就是开启了。不过这样设置并不是永久生效,重启后就失效了,如果要永久生效,就得修改MySQL的配置文件my.cnf ,在[mysqld] 下增加如下配置: slow_query_log=1 slow_query_log_file=/var/lib/mysql/sql-slow.log long_query_time=3 log_output=FILE
增加了这两行配置后,重启MySQL就可以了。 执行如下sql可以查看当前设置的阀值: show variables like 'long_query_time';
执行结果可以看到,当前设置的是10秒。要大于10s才会触发,等于是不会触发的。但是10s这个阀值太大了,我们一般会改小一点,通过以下sql可以修改该值: set long_query_time = 3;
执行结果那么我们要怎么验证修改是否生效呢?也就是超过3s的sql到底有没有被记录到慢查日志中呢?执行如下sql: select sleep(4);
这条sql就要睡4秒之后才会执行,所以最少都要执行4秒多,我们设置的阀值是3秒,所以肯定会被记录。 刚才查看了知道慢查日志名字叫localhost-slow.log ,那么到底在哪个目录下?可以通过执行如下命令来查找: find / -name "localhost-slow.log"
执行结果我们cat一下这个文件,看看刚刚执行的select sleep(4) 到底有没有被记录,结果如下: 执行结果可以看到,确实生效了,sql被记录到了慢查日志中。 我们还可以通过执行如下语句,来查询当前有多少条慢sql: show global status like "%Slow_queries%";
执行结果生产环境可能会出现很多的慢查日志,如果都要手工分析,也是比较费时的,MySQL提供了慢查日志分析工具,mysqldumpslow。 执行如下命令可以查看帮助文档: mysqldumpslow --help
帮助文档常见命令如下: # 得到返回记录集最多的10个sql mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
# 得到访问次数最多的10个sql mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log
# 一般执行上面的语句都要和 | more 一起使用,否则爆屏 mysqldumpslow -s r -t 10 /var/lib/mysql/local
|