mysql mysqldumpslow windows_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

Count: 4(运行了多少次)  Time=375.01s(每次运行的时间) (1500s)(一共运行了多少时间)  Lock=0.00s (0s)(等待锁的时间)

Rows=10200.3(每次返回的记录数) (40801)(总共返回的记录数), username[password]@[10.194.172.41]