【MySql】 慢日誌查詢工具之mysqlsla

[root@rac3 software]# wget
[root@rac3 software]# tar zvxf mysqlsla-2.03.tar.gz
[root@rac3 software]# mv mysqlsla-2.03 mysqlsla
[root@rac3 software]# cd mysqlsla
[root@rac3 mysqlsla]# ls
bin  Changes  INSTALL  lib  Makefile.PL  MANIFEST  META.yml  README
Makefile.PL  MANIFEST     META.yml    
[root@rac3 mysqlsla]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for mysqlsla
[root@rac3 mysqlsla]#
[root@rac3 mysqlsla]# make && make install
cp lib/mysqlsla.pm blib/lib/mysqlsla.pm
cp bin/mysqlsla blib/script/mysqlsla
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysqlsla
Manifying blib/man3/mysqlsla.3pm
Installing /usr/lib/perl5/site_perl/5.8.8/mysqlsla.pm
Installing /usr/share/man/man3/mysqlsla.3pm
Installing /usr/bin/mysqlsla
Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/mysqlsla/.packlist
Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod

在mysql中執行查詢語句，之後使用mysqlsla 工具查詢

[root@rac3 bin]# mysqlsla /opt/mysql/data/slowquery.log 
Auto-detected logs as slow logs
Report for slow logs: /opt/mysql/data/slowquery.log
3 queries total, 3 unique
Sorted by 't_sum'
Grand Totals: Time 1 s, Lock 0 s, Rows sent 2, Rows Examined 1.00M
______________________________________________________________________ 001 ___
Count         : 1  (50.00%)
Time          : 634.32 ms total, 634.32 ms avg, 634.32 ms to 634.32 ms max  (98.09%)
Lock Time (s) : 107.011 ms total, 107.011 ms avg, 107.011 ms to 107.011 ms max  (98.71%)
Rows sent     : 1 avg, 1 to 1 max  (50.00%)
Rows examined : 1.00M avg, 1.00M to 1.00M max  (99.80%)
Database      : test
Users         : 
        root@localhost  : 100.00% (1) of query, 100.00% (2) of all users
Query abstract:
SET timestamp=N; SELECT COUNT(N) FROM sbtest;
Query sample:
SET timestamp=1329572756;
select count(1) from sbtest;
______________________________________________________________________ 002 ___
Count         : 1  (50.00%)
Time          : 12.356 ms total, 12.356 ms avg, 12.356 ms to 12.356 ms max  (1.91%)
Lock Time (s) : 1.403 ms total, 1.403 ms avg, 1.403 ms to 1.403 ms max  (1.29%)
Rows sent     : 1 avg, 1 to 1 max  (50.00%)
Rows examined : 2.00k avg, 2.00k to 2.00k max  (0.20%)
Database      : 
Users         : 
        root@localhost  : 100.00% (1) of query, 100.00% (2) of all users
Query abstract:
SET timestamp=N; SELECT COUNT(N) FROM sbtest ,t1 WHERE t1.id=sbtest.id;
Query sample:
SET timestamp=1329572815;
select count(1) from sbtest ,t1 where t1.id=sbtest.id;

______________________________________________________________________ 003 ___

Count         : 1  (33.33%)
Time          : 12.356 ms total, 12.356 ms avg, 12.356 ms to 12.356 ms max  (0.00%)
Lock Time (s) : 1.403 ms total, 1.403 ms avg, 1.403 ms to 1.403 ms max  (1.29%)
Rows sent     : 1 avg, 1 to 1 max  (33.33%)
Rows examined : 2.00k avg, 2.00k to 2.00k max  (0.20%)
Database      : 
Users         : 
        root@localhost  : 100.00% (1) of query, 100.00% (3) of all users
Query abstract:
SET timestamp=N; SELECT COUNT(N) FROM sbtest ,t1 WHERE t1.id=sbtest.id;
Query sample:
SET timestamp=1329572815;
select count(1) from sbtest ,t1 where t1.id=sbtest.id;

queries total  總查詢次數
unique           去除重複後的 sql 數量.
Sorted by       輸出報表的內容排序.
Grand Totals slow sql 統計資訊包括: 總執行時間, 等待鎖時間, 結果行總數, 掃描行總數.
Count slow sql 的執行次數及佔總的 slow log 數量的百分比.
Time 執行時間, 包括總時間, 平均時間, 最小, 最大時間, 時間佔到總 slow sql 時間的百分比.
Lock Time 等待鎖的時間.
Rows sent 結果行統計數量, 包括平均, 最小, 最大數量.
Rows examined 掃描的行數量.
Database 屬於哪個資料庫
Users username@hostname  執行sql的使用者
Query abstract 精簡後的sql語句

幾個常用OPTION, 其他可檢視官網文件

--log-type=type logs or -lt type logs

其中type logs 可為 slow, general, binary, msl or udl. 官方文件說是沒有指定時, 會根據給定的日誌檔案自動檢測, 但測試時失敗, so, 最好指定該引數.

msl 是指 microslow patched 的慢日誌.

udl 使用者自定義的日誌.

binary 因為 mysqlsla 不能直接解析 MySQL 的 binary log, 所以需先用 mysqlbinlog命令將其解析為文字. mysqlbinlog帶有--short-form引數時, 則 LOG TYPE 需指定為 udl. 命令可類似於如下:

mysqlbinlog /opt/mysql/data/mysql-bin.000001 | mysqlsla -lt binary -

mysqlbinlog --short-form. /opt/mysql/data/mysql-bin.000001 | mysqlsla -lt udl -

--explain or -ex explain 每句 SQL, 預設沒有啟用

--databases=dbname1,dbnam2[,...] or -db dbname1,dbnam2[,...] or -D dbname1,dbnam2[,...]用於 --explain

--flush-qc 強制重新整理查詢快取, 預設沒有啟用

--grep="PATTERN" 只解析滿足條件的 SQL

--meta-filter="CONDTIONS" or -mf "CONDTIONS" CONDITIONS 格式為[meta][op][value],多個條件時, 中間以,分割.

[meta] 檢視這裡

[op] >, < r =. 當 [meta] 是基於字串時, [op] 只能是 =.

[value] 數字 or 字串.

--reports=REPORTS or -R REPORTS, 預設是 standard, REPORTS 可以是以,為分割的列表. 可選選項：standard, time-all, print-unique, print-all, dump