Mysql慢查询和慢查询日志分析利器mysqlsla安装使用

mysqlsl是一个mysql日志分析工具了,下面本文章来给大家介绍mysqlsla工具安装也mysqlsla工具对mysql日志分析详解,有需要了解的朋友可参考一下.

1.安装mysqlsla,代码如下:

1. wgethttp://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
2. tarzvxfmysqlsla-2.03.tar.gz
3. cdmysqlsla-2.03
4. perlMakefile.PL
5. make
6. makeinstall

perl Makefile.PL会报错,根据出错内容百度下都有相关信息,无非yum install一些东西.

2.使用mysqlsla语法,代码如下:

1. Slowlog:mysqlsla-ltslowslow.log
2. Generallog:mysqlsla-ltgeneralgeneral.log
3. Binarylog:mysqlbinlogbin.log|mysqlsla-ltbinary-

3.分析日志,代码如下:

./bin/mysqlsla ../slowlog/10.4.1.107.log.1206

分析结果:

1. Auto-detectedlogsasslowlogs
2. Reportforslowlogs:../slowlog/10.4.1.107.log.1206
3. 799queriestotal,4unique
4. Sortedby't_sum'
5. GrandTotals:Time1.25ks,Lock0s,Rowssent917,RowsExamined426.83M
7. ______________________________________________________________________001___
8. Count:372(46.56%)
9. Time:581.404365stotal,1.562915savg,1.001595sto4.129653smax(46.56%)
10. 95%ofTime:516.842801stotal,1.464144savg,1.001595sto2.815608smax
11. LockTime(s):40.935mstotal,110?savg,71?sto211?smax(44.74%)
12. 95%ofLock:38.016mstotal,108?savg,71?sto135?smax
13. Rowssent:1avg,1to1max(40.57%)
14. Rowsexamined:387.71kavg,225.34kto725.90kmax(33.79%)
15. Database:yy
16. Users:
17. yyr@10.4.1.83:33.87%(126)ofquery,34.79%(278)ofallusers
18. yyr@10.4.1.81:33.33%(124)ofquery,32.79%(262)ofallusers
19. yyr@10.4.1.82:32.80%(122)ofquery,32.42%(259)ofallusers
21. Queryabstract:
22. SETtimestamp=N;SELECTCOUNT(N)AScntFROMqz_feedWHERE(qz_id='S')AND(feed_keyIN(S1))AND(created>N)ANDfeed_idIN(SELECTfrom_idFROMqz_noticeWHEREtype_detail='S'ANDmember_id=NANDisnew=N);
23. //开源代码phpfensi.com
24. Querysample:
25. SETtimestamp=1354637384;
26. SELECTCOUNT(1)AScntFROMqz_feedWHERE(qz_id='377')AND(feed_keyin('speech_add'))AND(created>0)ANDfeed_idIN(SELECTfrom_idFROMqz_noticeWHEREtype_detail='speech_add'ANDmember_id=38011ANDisnew=1);

常用参数说明:

1) -log-type (-lt) type logs:

通过这个参数来制定log的类型，主要有slow, general, binary, msl, udl,分析slow log时通过制定为slow.

2) -sort:

制定使用什么参数来对分析结果进行排序,默认是按照t_sum来进行排序.

t_sum:按总时间排序

c_sum:按总次数排序

c_sum_p: sql语句执行次数占总执行次数的百分比.

3) -top:

显示sql的数量,默认是10,表示按规则取排序的前多少条.

4) –statement-filter (-sf) [+-][TYPE]:

过滤sql语句的类型,比如select、update、drop.

[TYPE]有SELECT, CREATE, DROP, UPDATE, INSERT，例如"+SELECT,INSERT",不出现的默认是-,即不包括.

5) db:要处理哪个库的日志.

例如,只取backup库的select语句、按c_sum_p排序的前2条记录,代码如下:

mysqlsla -lt slow -sort c_sum_p -sf "+select" -db backup -top 2 /tmp/127_slow.log

分析后的效果:

1. ______________________________________________________________________001___
2. Count:18(75.00%)
3. Time:15stotal,833.333msavg,0to8smax(93.75%)
4. 95%ofTime:7stotal,411.765msavg,0to4smax
5. LockTime(s):0total,0avg,0to0max(0.00%)
6. 95%ofLock:0total,0avg,0to0max
7. Rowssent:0avg,0to0max(0.00%)
8. Rowsexamined:116.51kavg,8to1.05Mmax(99.99%)
9. Database:
10. Users:
11. root@localhost:100.00%(18)ofquery,100.00%(24)ofallusers
13. Queryabstract:
14. INSERTINTOt2SELECT*FROMt2;
16. Querysample:
17. insertintot2select*fromt2;