MySQL的慢查询日志对于网站性能分析十分重要，今天发现了一个分析慢查询日志比较方便的工具mysqlsla，可以对慢查询日志中的SQL语句分类、排序，并计算平均执行时间，结果相对来说比较直观。
现在把安装和使用方式做个笔记，备查~
一、下载和安装
|
1 2 3 4 5 6 |
wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz tar zxvf mysqlsla-2.03.tar.gz cd mysqlsla-2.03 perl Makefile.PL make make install |
二、分析日志
|
1 |
./bin/mysqlsla ../slowlog/10.4.1.107.log.1206 |
三、分析结果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
Auto-detected logs as slow logs Report for slow logs: ../slowlog/10.4.1.107.log.1206 799 queries total, 4 unique Sorted by 't_sum' Grand Totals: Time 1.25k s, Lock 0 s, Rows sent 917, Rows Examined 426.83M ______________________________________________________________________ 001 ___ Count : 372 (46.56%) Time : 581.404365 s total, 1.562915 s avg, 1.001595 s to 4.129653 s max (46.56%) 95% of Time : 516.842801 s total, 1.464144 s avg, 1.001595 s to 2.815608 s max Lock Time (s) : 40.935 ms total, 110 ?s avg, 71 ?s to 211 ?s max (44.74%) 95% of Lock : 38.016 ms total, 108 ?s avg, 71 ?s to 135 ?s max Rows sent : 1 avg, 1 to 1 max (40.57%) Rows examined : 387.71k avg, 225.34k to 725.90k max (33.79%) Database : yy Users : yyr@ 10.4.1.83 : 33.87% (126) of query, 34.79% (278) of all users yyr@ 10.4.1.81 : 33.33% (124) of query, 32.79% (262) of all users yyr@ 10.4.1.82 : 32.80% (122) of query, 32.42% (259) of all users Query abstract: SET timestamp=N; SELECT COUNT(N) AS cnt FROM qz_feed WHERE (qz_id='S') AND (feed_key IN (S1)) AND (created>N) AND feed_id IN(SELECT from_id FROM qz_notice WHERE type_detail='S' AND member_id=N AND isnew=N); Query sample: SET timestamp=1354637384; SELECT COUNT(1) AS cnt FROM qz_feed WHERE (qz_id='377') AND (feed_key in ('speech_add')) AND (created>0) AND feed_id IN(SELECT from_id FROM qz_notice WHERE type_detail='speech_add' AND member_id=38011 AND isnew=1); |
近期评论