mysqlsla是hackmysql.com推出的一款日志分析工具，整體來(lái)說(shuō),功能非常強(qiáng)大.數(shù)據(jù)報(bào)表,非常有利于分析慢查詢(xún)的原因,包括執(zhí)行頻率,數(shù)據(jù)量,查詢(xún)消耗等.
安裝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工具分析慢查詢(xún)?nèi)罩?/p>
[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' );
格式說(shuō)明如下:
總查詢(xún)次數(shù)(queries total),去重后的sql數(shù)量(unique)
輸出報(bào)表的內(nèi)容排序(sortedby)
最重大的慢sql統(tǒng)計(jì)信息,包括平均執(zhí)行時(shí)間,等待鎖時(shí)間,結(jié)果行的總數(shù),掃描的行總數(shù).
Count, sql的執(zhí)行次數(shù)及占總的slow log數(shù)量的百分比.
Time,執(zhí)行時(shí)間,包括總時(shí)間,平均時(shí)間,最小,最大時(shí)間,時(shí)間占到總慢sql時(shí)間的百分比.
95% of Time,去除最快和最慢的sql,覆蓋率占95%的sql的執(zhí)行時(shí)間.
Lock Time,等待鎖的時(shí)間.
95% of Lock , 95%的慢sql等待鎖時(shí)間.
Rows sent,結(jié)果行統(tǒng)計(jì)數(shù)量,包括平均,最小,最大數(shù)量.
Rows examined,掃描的行數(shù)量.
Database,屬于哪個(gè)數(shù)據(jù)庫(kù)
Users,哪個(gè)用戶,IP,占到所有用戶執(zhí)行的sql百分比
Query abstract,抽象后的sql語(yǔ)句
Query sample, sql語(yǔ)句
mysqlsla常用參數(shù)
常見(jiàn)的用法：
mysqldumpslow -sc -t10/var/run/mysqld/mysqld-slow.log#取出使用最多的10條慢查詢(xún)
mysqldumpslow -st -t3/var/run/mysqld/mysqld-slow.log#取出查詢(xún)時(shí)間最慢的3條慢查詢(xún)
mysqldumpslow -st -t10-g “l(fā)eftjoin”
/database/mysql/slow-log#得到按照時(shí)間排序的前10條里面含有左連接的查詢(xún)語(yǔ)句
mysqldumpslow -sr -t10-g'left
join'/var/run/mysqld/mysqld-slow.log#按照掃描行數(shù)最多的
