MySQL慢查询分析工具之mysqldumpslow

  • lhrbest

    2020-04-29 12:58:50
  • MySQL
  • 原创


mysqldumpslow工具


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

mysqldumpslow -s at -t 5 mysql_slowlog.log
perl "D:\Program Files\MySQL\mysql-8.0.15-winx64\bin\mysqldumpslow.pl" -s at -t 5 C:\Users\chinasoft_lhrxxt\Desktop\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: 2018-12-25T08:00: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=1545724848;

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 Name Description
-a Do not abstract all numbers to N and strings to 'S'
-n Abstract numbers with at least the specified digits
--debug Write debugging information
-g Only consider statements that match the pattern
--help Display help message and exit
-h Host name of the server in the log file name
-i Name of the server instance
-l Do not subtract lock time from total time
-r Reverse the sort order
-s How to sort output
-t Display only first num queries
--verbose Verbose 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.





About Me

........................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub、博客园、CSDN和个人微 信公众号（ DB宝）上有同步更新

● 本文itpub地址： http://blog.itpub.net/26736162

● 本文博客园地址： http://www.cnblogs.com/lhrbest

● 本文CSDN地址： https://blog.csdn.net/lihuarongaini

● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● QQ群号： 230161599 、618766405

● 微 信群：可加我微 信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友 （ 646634621 ），注明添加缘由

● 于 2020-04-01 06:00 ~ 2020-04-30 24:00 在西安完成

● 最新修改时间：2020-04-01 06:00 ~ 2020-04-30 24:00

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

........................................................................................................................

● 小麦苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书： http://blog.itpub.net/26736162/viewspace-2142121/

● 小麦苗OCP、OCM、高可用网络班： http://blog.itpub.net/26736162/viewspace-2148098/

● 小麦苗腾讯课堂主页： https://lhr.ke.qq.com/

........................................................................................................................

使用 微 信客户端扫描下面的二维码来关注小麦苗的微 信公众号（ DB宝）及QQ群（DBA宝典）、添加小麦苗微 信， 学习最实用的数据库技术。

........................................................................................................................

欢迎与我联系

 

 



请使用浏览器的分享功能分享到微信等