慢查询日志分析工具-mysqlsla
mysqlsla是hackmysql.com推出的一款日志分析工具，整体来说,功能非常强大.数据报表,非常有利于分析慢查询的原因,包括执行频率,数据量,查询消耗等.
安装mysqlsla：
1、获取mysqlsla.zip安装包
2、安装必要的支持包：yuminstall perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker –y
3、安装DBI
wgethttps://cpan.metacpan.org/authors/id/T/TI/TIMB/DBI-1.636.tar.gz
tar xfDBI-1.636.tar.gz
cdDBI-1.636
perlMakefile.PL
make && make install
4、安装mysqlsla
unzip mysqlsla.zip
cd mysqlsla
perl Makefile.PL
make &&make install
使用mysqlsla工具分析慢查询日志
[root@test-db01 DBI-1.636]# /usr/local/bin/mysqlsla/application/mysql/data/mysql-slow.log
Auto-detected logs as slow logs
Report for slow logs:/application/mysql/data/mysql-slow.log
39.83k queries total, 81 unique
Sorted by 't_sum'
Grand Totals: Time 4.43k s, Lock 4 s, Rows sent 32.27M,Rows Examined 95.33M
______________________________________________________________________001 ___
Count:16.58k(41.63%)
Time:4310.320218 s total, 259.955 ms avg, 480 ?s to 1.644234 s max(97.25%)
95% of Time :3798.395814 s total, 241.153 ms avg, 480 ?s to 544.42 ms max
Lock Time (s) : 222.879 ms total, 13 ?s avg, 8 ?s to 142?s max(5.87%)
95% of Lock :199.801 ms total, 13 ?s avg, 8 ?s to 21 ?s max
Rows sent:1.94k avg, 1.01k to 3.10k max(99.62%)
Rows examined : 1.94k avg, 1.01k to 3.10k max(33.72%)
Database:mysqlslap
Users:
root@localhost: 100.00% (16581) of query, 41.65% (16590) ofall users
Query abstract:
SET timestamp=N; SELECT intcol1,charcol1 FROM t1;
Query sample:
SET timestamp=1492597033;
SELECT intcol1,charcol1 FROM t1;
______________________________________________________________________002 ___
Count:10.64k(26.71%)
Time:42.264367 s total, 3.973 ms avg, 2.601 ms to 12.175 ms max(0.95%)
95% of Time :37.86066 s total, 3.746 ms avg, 2.601 ms to 8.007 ms max
Lock Time (s) : 724.429 ms total, 68 ?s avg, 25 ?s to 243?s max(19.09%)
95% of Lock :650.909 ms total, 64 ?s avg, 25 ?s to 122 ?s max
Rows sent: 0avg, 0 to 1 max(0.01%)
Rows examined : 4.20k avg, 4.20k to 4.20k max(46.85%)
Database:union_common
Users:
lwl-com[lwl-com]@192.168.10.33 : 99.98% (10636) of query, 28.13% (11203) of all users
guest@192.168.10.230 : 0.02% (2) of query, 0.02% (9) of all users
Query abstract:
SET timestamp=N; SELECT dictionaryid, parentid, code,name, description, url, sort, addopenid, adddate, updateopenid, updatedate,dictionarytype FROM dictionary WHERE ( code = 'S' );
Query sample:
SET timestamp=1489399770;
select
DictionaryId,ParentId, Code, Name, Description, Url, Sort, AddOpenId, AddDate, UpdateOpenId,
UpdateDate,DictionaryType
from dictionary
WHERE (Code = 'L1_VEHICLEINFO' );
格式说明如下:
总查询次数(queries total),去重后的sql数量(unique)
输出报表的内容排序(sortedby)
最重大的慢sql统计信息,包括平均执行时间,等待锁时间,结果行的总数,扫描的行总数.
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语句
mysqlsla常用参数
常见的用法：
mysqldumpslow -sc -t10/var/run/mysqld/mysqld-slow.log#取出使用最多的10条慢查询
mysqldumpslow -st -t3/var/run/mysqld/mysqld-slow.log#取出查询时间最慢的3条慢查询
mysqldumpslow -st -t10-g “leftjoin”
/database/mysql/slow-log#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -sr -t10-g'left
join'/var/run/mysqld/mysqld-slow.log#按照扫描行数最多的
