1. mysqldumpslow简介
mysql装置好后自带的, perl工具.
2. 查看命令用法:mysqldumpslow --help
[root@niewj download]# mysqldumpslow --helpUsage: 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[root@niewj download]# 3. mysqldumpslow参数之-(1): -v或--verbose
打印明细信息
4. mysqldumpslow参数之-(2): -s
- al = 均匀锁定时长
- ar=均匀返送的rows数
- at=均匀query时长
- c=sql查问总数(某一条sql查问了几次)
- r=返送的rows总数
- t=query的工夫总数
- -t N = 指定只查前N条, 相当于 limit N
al: average lock timear: average rows sentat: average query timec: countl: lock timer: rows sentt: query time 排序参数:
4.1 -s at (按均匀的query time)
查问均匀耗时最长的慢sql:
mysqldumpslow -v -s at /var/lib/mysql/niewj-slow.log
[root@niewj download]# mysqldumpslow -v -s at /var/lib/mysql/niewj-slow.logReading mysql slow query log from /var/lib/mysql/niewj-slow.logCount: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10] select * from goodsCount: 3 Time=23.26s (69s) Lock=0.00s (0s) Rows=136666.7 (410000), root[root]@[121.69.51.10] select * from goods where id>NCount: 6 Time=11.12s (66s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[121.69.51.10] call Proc()Count: 1 Time=11.00s (11s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@[121.69.51.10] select sleep(N)Count: 1 Time=4.68s (4s) Lock=0.00s (0s) Rows=32262.0 (32262), root[root]@[121.69.51.10] select * from goods where id>N[root@niewj download]#能够看到 Time=25.84s 均匀时长最长, 排在最前;4.2 -s t (依照总的query time)
查问哪个sql查问最耗时(算总时长,疏忽次数):
mysqldumpslow -v -s t /var/lib/mysql/niewj-slow.log
[root@niewj download]# mysqldumpslow -v -s t /var/lib/mysql/niewj-slow.logReading mysql slow query log from /var/lib/mysql/niewj-slow.logCount: 3 Time=23.26s (69s) Lock=0.00s (0s) Rows=136666.7 (410000), root[root]@[121.69.51.10] select * from goods where id>NCount: 6 Time=11.12s (66s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[121.69.51.10] call Proc()Count: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10] select * from goodsCount: 1 Time=11.00s (11s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@[121.69.51.10] select sleep(N)Count: 1 Time=4.68s (4s) Lock=0.00s (0s) Rows=32262.0 (32262), root[root]@[121.69.51.10] select * from goods where id>N[root@niewj download]#能够看到总时长最大的(69s)的排到最后面了;
4.3 -s ar (依照均匀返回的rows量)
查问均匀每次查问返回条数最多的慢sql:
mysqldumpslow -v -s ar /var/lib/mysql/niewj-slow.log
[root@niewj download]# mysqldumpslow -v -s ar /var/lib/mysql/niewj-slow.logReading mysql slow query log from /var/lib/mysql/niewj-slow.logCount: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10] select * from goodsCount: 3 Time=23.26s (69s) Lock=0.00s (0s) Rows=136666.7 (410000), root[root]@[121.69.51.10] select * from goods where id>NCount: 1 Time=4.68s (4s) Lock=0.00s (0s) Rows=32262.0 (32262), root[root]@[121.69.51.10] select * from goods where id>NCount: 1 Time=11.00s (11s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@[121.69.51.10] select sleep(N)Count: 6 Time=11.12s (66s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[121.69.51.10] call Proc()[root@niewj download]#Rows=150000.0 (300000) 排在最前了: 括号里是总量; 平均值最大, 排最前;
4.4 -s r (依照总的rows返回量)
查问返回行数最多的慢sql:
mysqldumpslow -v -s r /var/lib/mysql/niewj-slow.log
[root@niewj download]# mysqldumpslow -v -s r /var/lib/mysql/niewj-slow.logReading mysql slow query log from /var/lib/mysql/niewj-slow.logCount: 3 Time=23.26s (69s) Lock=0.00s (0s) Rows=136666.7 (410000), root[root]@[121.69.51.10] select * from goods where id>NCount: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10] select * from goodsCount: 1 Time=4.68s (4s) Lock=0.00s (0s) Rows=32262.0 (32262), root[root]@[121.69.51.10] select * from goods where id>NCount: 1 Time=11.00s (11s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@[121.69.51.10] select sleep(N)Count: 6 Time=11.12s (66s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[121.69.51.10] call Proc()[root@niewj download]# 看到 Rows=136666.7 (410000) 排到最前了, 括号里是总量, 不管查了几次, 总量最大, 排最前了;
4.5 -s c (依照查问次数排序)
查找调用频次最高的慢查问sql:
mysqldumpslow -v -s c /var/lib/mysql/niewj-slow.log
[root@niewj download]# mysqldumpslow -v -s c /var/lib/mysql/niewj-slow.logReading mysql slow query log from /var/lib/mysql/niewj-slow.logCount: 6 Time=11.12s (66s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[121.69.51.10] call Proc()Count: 3 Time=23.26s (69s) Lock=0.00s (0s) Rows=136666.7 (410000), root[root]@[121.69.51.10] select * from goods where id>NCount: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10] select * from goodsCount: 1 Time=4.68s (4s) Lock=0.00s (0s) Rows=32262.0 (32262), root[root]@[121.69.51.10] select * from goods where id>NCount: 1 Time=11.00s (11s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@[121.69.51.10] select sleep(N)[root@niewj download]#4.6 -t N(限定返回topN的慢sql)
下面每种情景限定返回前两条:
4.6.1 -s at -t 2 查问均匀耗时最长前2条的慢sql-限前2个:
[root@niewj download]# mysqldumpslow -v -s at -t 2 /var/lib/mysql/niewj-slow.logReading mysql slow query log from /var/lib/mysql/niewj-slow.logCount: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10] select * from goodsCount: 3 Time=23.26s (69s) Lock=0.00s (0s) Rows=136666.7 (410000), root[root]@[121.69.51.10] select * from goods where id>N[root@niewj download]#4.6.2 -s t -t 2 查问哪2个sql查问最耗时(算总时长,疏忽次数)的慢sql-限前2个:
[root@niewj download]# mysqldumpslow -v -s t -t 2 /var/lib/mysql/niewj-slow.log Reading mysql slow query log from /var/lib/mysql/niewj-slow.logCount: 3 Time=23.26s (69s) Lock=0.00s (0s) Rows=136666.7 (410000), root[root]@[121.69.51.10] select * from goods where id>NCount: 6 Time=11.12s (66s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[121.69.51.10] call Proc()[root@niewj download]#(69s)-(66s)
4.6.3 -s ar -t 2 查问前2个均匀每次查问返回条数最多的慢sql-限前2个:
[root@niewj download]# mysqldumpslow -v -s ar -t 2 /var/lib/mysql/niewj-slow.log Reading mysql slow query log from /var/lib/mysql/niewj-slow.logCount: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10] select * from goodsCount: 3 Time=23.26s (69s) Lock=0.00s (0s) Rows=136666.7 (410000), root[root]@[121.69.51.10] select * from goods where id>N[root@niewj download]#Rows=150000.0 (300000)Rows=136666.7 (410000)
4.6.4 -s r -t 2 查问返回行数最多的慢sql-限前2个:
[root@niewj download]# mysqldumpslow -v -s r -t 2 /var/lib/mysql/niewj-slow.log Reading mysql slow query log from /var/lib/mysql/niewj-slow.logCount: 3 Time=23.26s (69s) Lock=0.00s (0s) Rows=136666.7 (410000), root[root]@[121.69.51.10] select * from goods where id>NCount: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10] select * from goods[root@niewj download]#Rows=136666.7 (410000)Rows=150000.0 (300000)
4.6.5 -s c -t 2 查找调用频次最高的慢查问sql:-限前2个:
[root@niewj download]# mysqldumpslow -v -s c -t 2 /var/lib/mysql/niewj-slow.log Reading mysql slow query log from /var/lib/mysql/niewj-slow.logCount: 6 Time=11.12s (66s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[121.69.51.10] call Proc()Count: 3 Time=23.26s (69s) Lock=0.00s (0s) Rows=136666.7 (410000), root[root]@[121.69.51.10] select * from goods where id>N[root@niewj download]#Count: 6Count: 3