mysql mysqldumpslow_mysql优化-(1)-慢查询日志工具-mysqldumpslow

1. mysqldumpslow简介

mysql安装好后自带的, perl工具.

2. 查看命令用法:mysqldumpslow --help[root@niewj download]# mysqldumpslow --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

[root@niewj download]#

3. mysqldumpslow参数之-(1): -v或--verbose

打印明细信息

4. mysqldumpslow参数之-(2): -sal = 平均锁定时长

ar=平均返送的rows数

at=平均query时长

c=sql查询总数(某一条sql查询了几次)

r=返送的rows总数

t=query的时间总数

-t N = 指定只查前N条, 相当于 limit Nal: average lock time

ar: average rows sent

at: average query time

c: count

l: lock time

r: rows sent

t: 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.log

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log

Count: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10]

select * from goods

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

Count: 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.log

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log

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

Count: 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 goods

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]#可以看到总时长最大的(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.log

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log

Count: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10]

select * from goods

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

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

Count: 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.log

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log

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

Count: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10]

select * from goods

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

Count: 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.log

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log

Count: 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

Count: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10]

select * from goods

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

Count: 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.log

Reading mysql slow query log from /var/lib/mysql/niewj-slow.log

Count: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10]

select * from goods

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]#

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.log

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

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]#(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.log

Count: 2 Time=25.84s (51s) Lock=0.00s (0s) Rows=150000.0 (300000), root[root]@[121.69.51.10]

select * from goods

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]#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.log

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

Count: 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.log

Count: 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: 6

Count: 3

5. mysqldumpslow工具的缺点

1. 它是mysql自带的, perl脚本写的工具;

2. 无法提供cpu/io等扩展信息;