分析mysql慢查询日志的工具mysqlsla

当用户访问动态网站有时候会很慢，数据库的原因可能是sql语句的查询效率低，数据对前端的响应慢，这就是所谓的慢查询导致的问题，生产环境如何解决这个慢查询问题呢

解决办法：

1、开启慢查询

2、对慢查询日志就行切割，生成shell脚本每天执行一次慢查询日志的切割

3、利用mysqlsla慢查询分析工具分析日志

4、将分析完的慢查询日志邮件发送给相关人员

解决步骤：

1、开启慢查询

　　在mysql配置文件中添加如下参数，并重启mysql服务使配置生效

long_query_time = 1    ---查询超过1秒记录
log-slow-queries = /application/mysql/data/slow.log  ---记录位置
log_queries_not_using_indexes  ------没有使用索引的查询记录到slow.log中

2、对慢查询日志切割

　　按天进行切割，最后生成shell脚本每天执行一次慢查询日志的切割

cd  /application/mysql/data/

/bin/mv  slow.log  slow.log.$(date +%F)

/application/mysql/bin/mysqladmin  -uroot -p123456 flush-log

　　在/server/scripts/下创建一个切割慢查询日志的脚本cut_slow_log.sh

　　vim cut_slow_log.sh

#!/bin/sh
cd  /application/mysql/data/ &&\
/bin/mv  slow.log  slow.log.$(date +%F) &&\
/application/mysql/bin/mysqladmin  -uroot -p123456 flush-log

　　创建定时任务：crontab -e

00 00 * * *  /bin/sh  /server/scripts/cut_slow_log.sh >/dev/null  2>&1

3、　利用mysqlsla慢查询分析工具分析日志

mysql数据库的慢查询日志是非常重要的一项调优辅助日志，但是mysql默认记录的日志格式阅读时不够友好，这是由mysql日志记录规则所决定的，捕获一条就记录一条，虽说记录的信息足够详尽，但如果将浏览慢查询日志做为一项日常工作，直接阅读mysql生成的慢查询日志就有可能比较低效了。
除了操作系统命令直接查看slowlog外，mysql自己也提供了一个阅读slowlog的命令行工具：mysqldumpslow，该命令行提供了一定的分析汇总功能，可以将多个类似的SQL语句抽象显示成一个，不过功能还是有些简陋，除此之外，还有不少的第三方工具，可用于分析mysql慢查询日志，比如mysqlsla分析工具就不错，简单易用。
mysqlsla不仅仅可用来处理慢查询日志，也可以用来分析其它日志比如二进制日志，普通查询日志等等，其对sql语句的抽象功能非常实用，参数设定简练易用，很好上手。

mysqlsla下载地址为：https://github.com/daniel-nichter/hackmysql.com

mysqlsla是perl编写的脚本，运行mysqlsla需要perl-DBI和per-DBD-Mysql两模块的支持，因此在运行mysqlsla前需要首先安装DBI模块和相应的数据库DBD驱动，而默认情况下linux不安装这两个模块，需要自行下载

perl-DBI下载地址：http://www.cpan.org/modules/by-module/DBI/DBI-1.634.tar.gz

per-DBD-Mysql下载地址：http://www.cpan.org/modules/by-module/DBD/DBD-mysql-4.031.tar.gz

安装per-DBD-Mysql

[root@mysql-server tools]# cd DBD-mysql-4.031

[root@mysql-server DBD-mysql-4.031]# perl  Makefile.PL

[root@mysql-server DBD-mysql-4.031]# make && make install

但是在安装per-DBD-Mysql的时候报错如下：

[root@mysql-server DBD-mysql-4.031]# perl  Makefile.PL
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 9.
BEGIN failed--compilation aborted at Makefile.PL line 9.

解决这个报错办法：yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker -y

再进行安装per-DBD-Mysql，这时候又报错如下：

[root@mysql-server DBD-mysql-4.031]# perl  Makefile.PL
Can't locate DBI/DBD.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 15.

解决这个报错办法：[root@mysql-server DBD-mysql-4.031]# yum install perl-DBD-MySQL -y

这样再安装per-DBD-Mysql perl-DBI mysqlsla就没问题了

安装perl-DBI

[root@mysql-server tools]# cd DBI-1.634

[root@mysql-server DBI-1.634]# perl  Makefile.PL 

[root@mysql-server DBI-1.634]# make  && make install

安装mysqlsla

[root@mysql-server tools]# cd  hackmysql.com-master
[root@mysql-server hackmysql.com-master]# cd  mysqlsla/
[root@mysql-server mysqlsla]# perl  Makefile.PL 
[root@mysql-server mysqlsla]# make  && make install

最后用mysqlsla测试下检查命令是否生效

mysqlsla -lt slow /tmp/test.log >/opt/ceshi.log 这时候会报错如下：

[root@mysql-server ~]# mysqlsla  -lt slow /tmp/test.log >/opt/ceshi.log
Can't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/bin/mysqlsla line 2095.
BEGIN failed--compilation aborted at /usr/local/bin/mysqlsla line 2095.

检测是出现缺少perl Time HiRes组件造成的

解决办法为: yum -y install perl-Time-HiRes

到此mysqlsla慢查询日志分析工具就安装完毕了

4、利用mysqlsla分析日志，并定时将分析好的日志邮件发送给相关人员

　　让服务器支持发送邮件功能,在/etc/mail.rc中配置163邮件服务器为媒介来发送邮件

set from=ceshi@163.com smtp=smtp.163.com
set smtp-auth-user=ceshi smtp-auth-password=abcdefg123 smtp-auth=login

　　发邮件　　mail -s oldboy 3900000@qq.com < /etc/hosts

　　修改上面的cut_slow_log.sh,将mysqlsla分析日志命令加进去，并添加邮件发送功能

#!/bin/sh
cd  /application/mysql/data/ &&\
mysqlsla  -lt slow slow.log >/opt/myslq_slow.log &&\
/bin/mv  slow.log  slow.log.$(date +%F) &&\
/application/mysql/bin/mysqladmin  -uroot -p123456 flush-log 
mail -s oldboy 3900000@qq.com < /opt/mysql_slow.log

5、慢查询日志分析

mysqlsla命令默认会保存在/usr/bin路径下，通常可在任意路径下直接执行。对慢查询日志文件的分析，最简化的调用方式如下：

# mysqlsla -lt slow [SlowLogFilePath] > [ResultFilePath]

比如说，原始慢日志中有一堆的下列语句：

# Time: 110417  0:00:09
# User@Host: junsansi[junsansi] @  [192.168.1.27]
# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 17600
select min(DOC_HIS_ID) AS DOC_HIS_ID from t_******** where DOC_HIS_ISTEAMMATE=1 and DOC_HIS_EDITOR_USER_ID_ENCRYPT='nfEACAwQEW1MICAN2';
# User@Host: junsansi[junsansi] @  [192.168.1.27]
# Query_time: 4  Lock_time: 0  Rows_sent: 1  Rows_examined: 17600
select min(DOC_HIS_ID) AS DOC_HIS_ID from t_******** where DOC_HIS_ISTEAMMATE=1 and DOC_HIS_EDITOR_USER_ID_ENCRYPT='nfEACAwQEW2MICAN2';
# User@Host: jss[junsansi] @  [192.168.1.26]
# Query_time: 4  Lock_time: 0  Rows_sent: 1  Rows_examined: 17600
select min(DOC_HIS_ID) AS DOC_HIS_ID from t_******** where DOC_HIS_ISTEAMMATE=1 and DOC_HIS_EDITOR_USER_ID_ENCRYPT='nfEACAwQEW3MICAN2';
# User@Host: junsansi[junsansi] @  [192.168.1.27]
# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 17600
select min(DOC_HIS_ID) AS DOC_HIS_ID from t_******** where DOC_HIS_ISTEAMMATE=1 and DOC_HIS_EDITOR_USER_ID_ENCRYPT='nfEACAwQEW4MICAN2';
# User@Host: jss[junsansi] @  [192.168.1.26]
# Query_time: 5  Lock_time: 0  Rows_sent: 1  Rows_examined: 17600
select min(DOC_HIS_ID) AS DOC_HIS_ID from t_******** where DOC_HIS_ISTEAMMATE=1 and DOC_HIS_EDITOR_USER_ID_ENCRYPT='nfEACAwQEW5MICAN2';

　　直接阅读的操作体验很不好，使用mysqlsla处理后，结果呈现如下：

Count         : 23  (8.52%)
Time          : 102 s total, 4.434783 s avg, 3 s to 7 s max  (6.79%)
  95% of Time : 88 s total, 4.190476 s avg, 3 s to 6 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
  95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent     : 1 avg, 1 to 1 max  (0.02%)
Rows examined : 11.53k avg, 5.70k to 17.60k max  (1.07%)
Database      : jssdb
Users         : 
        junsansi@ 192.168.1.27 : 86.96% (20) of query, 11.11% (30) of all users
        jss@ 192.168.1.26 : 13.04% (3) of query, 2.96% (8) of all users
Query abstract:
SELECT MIN(doc_his_id) AS doc_his_id FROM t_******** WHERE doc_his_isteammate=N AND doc_his_editor_user_id_encrypt='S';
Query sample:
select min(DOC_HIS_ID) AS DOC_HIS_ID from t_******** where DOC_HIS_ISTEAMMATE=1 and DOC_HIS_EDITOR_USER_ID_ENCRYPT='nfEACAwQEW2MICAN2';

　　上面的mysql工具分析后结果说明：

　　参数说明：

lt：表示日志类型，有slow, general, binary, msl, udl。
sf：[+-][TYPE]，[TYPE]有SELECT, CREATE, DROP, UPDATE, INSERT，例如”+SELECT,INSERT”，不出现的默认是-，即不包括。
db：要处理哪个库的日志。
top：表示取按规则排序的前多少条。
sort：按某种规则排序，t_sum按总时间排序， c_sum按总次数排序。

　　分析后结果解释：

Count： sql的执行次数及占总的slow log数量的百分比.
Time： 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比，95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.
Lock Time： 等待锁的时间，95% of Lock , 95%的慢sql等待锁时间.
Rows sent： 结果行统计数量, 包括平均, 最小, 最大数量.
Rows examined：扫描的行数量.
Database： 属于哪个数据库
Users： 哪个用户,IP, 占到所有用户执行的sql百分比
Query abstract： 抽象后的sql语句
Query sample： sql语句

当然也可以通过full processlist手动来查看慢查询语句，在mysql命令行外直接执行。如：mysql -uroot -p123456 -e "show full processlist;" | grep -v Sleep 多执行几次，如果看到某些查询语句一直存在，那么就可以定位这些查询语句是慢查询了，接下来要进行对某个字段做索引，还是对几个字段做联合索引，根据实际情况来定。。。

posted @ 2017-09-25 23:24 goser 阅读(333) 评论(0) 收藏举报

刷新页面返回顶部

分析mysql慢查询日志的工具mysqlsla

解决办法：

解决步骤：

公告