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
  6. ______________________________________________________________________001___
  7. Count:372(46.56%)
  8. Time:581.404365stotal,1.562915savg,1.001595sto4.129653smax(46.56%)
  9. 95%ofTime:516.842801stotal,1.464144savg,1.001595sto2.815608smax
  10. LockTime(s):40.935mstotal,110?savg,71?sto211?smax(44.74%)
  11. 95%ofLock:38.016mstotal,108?savg,71?sto135?smax
  12. Rowssent:1avg,1to1max(40.57%)
  13. Rowsexamined:387.71kavg,225.34kto725.90kmax(33.79%)
  14. Database:yy
  15. Users:
  16. yyr@10.4.1.83:33.87%(126)ofquery,34.79%(278)ofallusers
  17. yyr@10.4.1.81:33.33%(124)ofquery,32.79%(262)ofallusers
  18. yyr@10.4.1.82:32.80%(122)ofquery,32.42%(259)ofallusers
  19. Queryabstract:
  20. 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);
  21. //开源代码phpfensi.com
  22. Querysample:
  23. SETtimestamp=1354637384;
  24. 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
  12. Queryabstract:
  13. INSERTINTOt2SELECT*FROMt2;
  14. Querysample:
  15. insertintot2select*fromt2;
相关广告
  • Mysql慢查询和慢查询日志分析利器mysqlsla安装使用 Mysql慢查询和慢查询日志分析利器mysqlsla安装使用 Mysql慢查询和慢查询日志分析利器mysqlsla安装使用
相关阅读

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

2019/10/10 17:37:41 | 谷歌SEO算法 | 夫唯SEO视频教程