程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> windows下mysql5.6.20使用mysqldumpslow.pl分析慢日志

windows下mysql5.6.20使用mysqldumpslow.pl分析慢日志

編輯:MySQL綜合教程

windows下mysql5.6.20使用mysqldumpslow.pl分析慢日志


要想運行mysqldumpslow.pl(這是perl程序),下載perl編譯器。下載地址:http://pan.baidu.com/s/1i3GLKAp

就是ActivePerl_5.16.2.3010812913.msi,一步一步安裝後,將bin加入環境變量path。

現在假設一個場景:現場的slow.log拿回來了,要在本地的windows環境上的mysql上分析,如何處理?

C:\Program Files\MySQL\MySQL Server 5.6\bin>perl mysqldumpslow.pl --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output


-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time

1. -s,排序,c,t,l,r以及ac,at,al,ar分別是按照query次數,時間,lock時間,返回記錄排序。加a就是倒序。
2. -t,top n,跟上數字就是算出top多少條
3. -g,跟正則表達式。

C:\Program Files\MySQL\MySQL Server 5.6\bin> mysqldumpslow.pl -r -s c -a -t > E:\slow.txt

如果執行這個會報錯,就按照報錯信息來,在my.ini中添加一些參數即可。分析的結果在slow.txt中,如下:

Count: 23 Time=505.55s (11627s) Lock=0.00s (0s) Rows=30740.8 (707039), username[password]@[10.194.172.41]
SELECT DISTINCT u.name,o.full_name FROM pub_user u,pub_user_org uo,pub_org o WHERE u.user_id=uo.user_id
AND uo.org_id=o.org_id and u.del_flag=N and uo.del_flag=N
and u.account not like 'S' and u.account not like 'S' group by u.user_id

Count: 4 Time=375.01s (1500s) Lock=0.00s (0s) Rows=10200.3 (40801), username[password]@[10.194.172.41]
select distinct username,pu.name, po.full_name from pub_login_history as plh
inner join pub_user as pu on plh.username=pu.user_id
inner join pub_user_org as puo on pu.user_id=puo.user_id
inner join pub_org as po on puo.org_id=po.org_id
where TIMESTAMPDIFF(MINUTE,login_time,logout_time)>=N
and login_time>'S' and login_time<'S'

Count: 4(執行了多少次) Time=375.01s(每次執行的時間) (1500s)(一共執行了多少時間) Lock=0.00s (0s)(等待鎖的時間) Rows=10200.3(每次返回的記錄數) (40801)(總共返回的記錄數), username[password]@[10.194.172.41]

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved