mysql mysqldumpslow_MySQL慢查询分析工具之mysqldumpslow

MySQL慢查询分析工具之mysqldumpslow(转载)

mysqldumpslow工具

https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

mysqldumpslow -s at -t 5 mysql_slowlog.log

开启慢查询

查看慢查询相关参数

slow_query_log：慢查询是否开启

slow_query_log_file：慢查询文件位置

long_query_time：超过多长时间记录

log_queries_not_using_indexes：没有使用索引的sql

mysql> show variables like 'slow_query%';

+---------------------+----------------------------+

| Variable_name | Value |

+---------------------+----------------------------+

| slow_query_log | ON |

| slow_query_log_file | /var/lib/mysql/db-slow.log |

+---------------------+----------------------------+

2 rows in set (0.01 sec)

mysql> show variables like 'long_query_time';

+-----------------+----------+

| Variable_name | Value |

+-----------------+----------+

| long_query_time | 2.000000 |

+-----------------+----------+

1 row in set (0.00 sec)

mysql> show variables like 'log_queries_not%';

+-------------------------------+-------+

| Variable_name | Value |

+-------------------------------+-------+

| log_queries_not_using_indexes | OFF |

+-------------------------------+-------+

1 row in set (0.00 sec)

设置方法

方法1：全局变量设置

mysql> set global slow_query_log='ON';

mysql> set global slow_query_log_file='/var/lib/mysql/db-slow.log';

mysql> set global long_query_time=2;

mysql> set global log_queries_not_using_indexes=1;

方法2：配置文件设置

修改配置文件my.cnf在[mysqld]下加入如下内容

slow_query_log = ON

slow_query_log_file = /var/lib/mysql/db-slow.log

long_query_time = 2

log_queries_not_using_indexes=1

修改后重启mysql服务

测试

mysql> select sleep(3);

[root@db ~]# tail -f /var/lib/mysql/db-slow.log

# Time: 2019-12-25T20:08:48.199670Z

# User@Host: root[root] @ localhost [] Id: 2443797

# Query_time: 3.000198 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0

SET timestamp=153892974;

select sleep(3);

[root@db ~]# 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

常用参数

-s 排序方式 后面跟排序列

al 平均锁定时间

ar 平均返回记录时间

at 平均查询时间(默认)

c 计数

l 锁定时间

r 返回记录

t 查询时间

-r 反向排序，最大的在最后

-t 后面跟数字，返回行数

-a 不将所有的数字抽象为N，字符串抽象为S

-n 后面跟数字，在名称中至少有n个数字抽象为数字

-g 正则后边可以写一个正则匹配模式，大小写不敏感的

案例：

1.根据计数返回5条记录

mysqldumpslow -s c -t 5 /var/lib/mysql/db-slow.log

2.根据返回记录返回5条记录

mysqldumpslow -s r -t 5 /var/lib/mysql/db-slow.log

3.根据查询时间返回5条记录

mysqldumpslow -s t -t 5 /var/lib/mysql/db-slow.log

4.根据计数统计，不抽象数字及字符串

mysqldumpslow -s c -a -t 5 /var/lib/mysql/db-slow.log

得到返回记录最多的20个sql

mysqldumpslow -s r -t 20 sqlslow.log

得到平均访问次数最多的20条sql

mysqldumpslow -s ar -t 20 sqlslow.log

得到平均访问次数最多,并且里面含有ttt字符的20条sql

mysqldumpslow -s ar -t 20 -g "ttt" sqldlow.log

注：

1、如果出现 -bash: mysqldumpslow: command not found 错误，请执行

ln -s /usr/local/mysql/bin/mysqldumpslow /usr/bin

2、如果出现如下错误，Died at /usr/bin/mysqldumpslow line 161, <> chunk 405659.说明你要分析的sql日志太大了，请拆分后再分析

拆分的命令为：

tail -100000 mysql-slow.log>mysql-slow.20180725.log

-- 取出执行次数最多的前5条SQL

[root@localhost log]# /software/bin/mysqldumpslow -s c -t 5 slow_query.log |more

Reading mysql slow query log from slow_query.log

Count: 2 Time=43.54s (87s) Lock=0.00s (0s) Rows=0.0 (0), system[system]@localhost

lock table t10 write

Count: 1 Time=14.53s (14s) Lock=0.00s (0s) Rows=73088.0 (73088), system[system]@localhost

SELECT /*!N SQL_NO_CACHE */ * FROM `t_idb_big`

Count: 1 Time=12.22s (12s) Lock=0.00s (0s) Rows=0.0 (0), system[system]@localhost

INSERT INTO `t_idb_big` VALUES ('S','S','S','S',N,'S','S','S',NULL,NULL,N,N,NULL,NULL,'S','S','S','S','S',N)

--取出耗时最长的前10条慢SQL

mysqldumpslow -s t -t 10 slow.log

https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

The MySQL slow query log contains information about queries that take a long time to execute (see Section 5.4.5, “The Slow Query Log”). mysqldumpslow parses MySQL slow query log files and summarizes their contents.

Normally, mysqldumpslow groups queries that are similar except for the particular values of number and string data values. It “ abstracts” these values to N and 'S' when displaying summary output. To modify value abstracting behavior, use the -a and -n options.

Invoke mysqldumpslow like this:

shell> mysqldumpslow [options] [log_file ...]

Example of usage:

shell> mysqldumpslowReading mysql slow query log from /usr/local/mysql/data/mysqld57-slow.log

Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost

insert into t2 select * from t1

Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost

insert into t2 select * from t1 limit N

Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost

insert into t1 select * from t1

mysqldumpslow supports the following options.

Table 4.23 mysqldumpslow Options

Option NameDescription

-aDo not abstract all numbers to N and strings to 'S'

-nAbstract numbers with at least the specified digits

--debugWrite debugging information

-gOnly consider statements that match the pattern

--helpDisplay help message and exit

-hHost name of the server in the log file name

-iName of the server instance

-lDo not subtract lock time from total time

-rReverse the sort order

-sHow to sort output

-tDisplay only first num queries

--verboseVerbose mode

--help

Display a help message and exit.

-a

Do not abstract all numbers to N and strings to 'S'.

--debug, -d

Run in debug mode.

This option is available only if MySQL was built using WITH_DEBUG. MySQL release binaries provided by Oracle are not built using this option.

-g pattern

Consider only queries that match the ( grep-style) pattern.

-h host_name

Host name of MySQL server for *-slow.log file name. The value can contain a wildcard. The default is * (match all).

-i name

Name of server instance (if using mysql.server startup script).

-l

Do not subtract lock time from total time.

-n N

Abstract numbers with at least N digits within names.

-r

Reverse the sort order.

-s sort_type

How to sort the output. The value of sort_type should be chosen from the following list:

t, at: Sort by query time or average query time

l, al: Sort by lock time or average lock time

r, ar: Sort by rows sent or average rows sent

c: Sort by count

By default, mysqldumpslow sorts by average query time (equivalent to -s at).

-t N

Display only the first N queries in the output.

--verbose, -v

Verbose mode. Print more information about what the program does.