乐趣区

关于mysql优化:mysql优化1慢查询日志工具mysqldumpslow

jiezi

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): -s

  1. al = 均匀锁定时长
  2. ar= 均匀返送的 rows 数
  3. at= 均匀 query 时长
  4. c=sql 查问总数(某一条 sql 查问了几次)
  5. r= 返送的 rows 总数
  6. t=query 的工夫总数
  7. -t N = 指定只查前 N 条, 相当于 limit N
al: 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 等扩大信息;

退出移动版