MySQL 慢查询设置和分析工具

测试使用 MySQL 版本为 5.7.13

MySQL 配置

首先可以通过命令查看是否开启慢查询日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+-----------------------------------+
| Variable_name | Value |
|---------------------+-----------------------------------|
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/slow.log |
+---------------------+-----------------------------------+
2 rows in set
Time: 0.005s

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+---------+
| Variable_name | Value |
|-----------------+---------|
| long_query_time | 1 |
+-----------------+---------+
1 row in set
Time: 0.004s

slow_query_log 表示是否开启慢查询日志,ON 为开启,OFF 为关闭,如果为关闭可以开启。
slow_query_log_file 表示慢查询日志记录文件
long_query_time 表示超过多长时间为慢查询,是一个边界值,单位为秒

设置

临时设置

在 MySQL 执行 SQL 语句设置,但是如果重启 MySQL 的话将失效

1
2
set global slow_query_log = ON;
set long_query_time = 1;

永久设置

修改配置文件,重启 MySQL, 这种永久生效

1
2
3
4
5
6
my.cnf

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

慢日志格式

1
2
3
4
5
6
# Time: 2016-12-22T09:41:17.777439Z
# User@Host: homestead[homestead] @ [192.168.10.1] Id: 1543
# Query_time: 2.001423 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use homestead;
SET timestamp=1482399677;
select sleep(2);

慢查询日志分析工具

MySQL 自带工具 mysqldumpslow

使用很简单,可以跟 -h 来查看具体的用法。

1
mysqldumpslow /var/log/mysql/slow.log

主要功能是, 统计不同慢 sql 下面这些属性:

  • 出现次数(Count),
  • 执行最长时间(Time),
  • 累计总耗费时间(Time),
  • 等待锁的时间(Lock),
  • 发送给客户端的行总数(Rows),
  • 扫描的行总数(Rows),
  • 用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示).

讲一下有用的参数:
-s 排序选项:c 查询次数 r 返回记录行数 t 查询时间
-t num 只显示 top n 条查询
其他参数可以使用 -h 命令进行查看

使用 mysqlsla 工具 项目地址

mysqlsla 工具,功能非常强大。数据报表,非常有利于分析慢查询的原因,包括执行频率,数据量,查询消耗等。
此工具已停止维护,项目 github 介绍页面推荐使用 percona-toolkit,下面有介绍。

1
mysqlsla -lt /var/log/mysql/slow.log

使用 percona-toolkit

percona-toolkit 是一组高级命令行工具的集合,用来执行各种通过手工执行非常复杂和麻烦的 mysql 和系统任务。这些任务包括:

  • 检查master和slave数据的一致性
  • 有效地对记录进行归档
  • 查找重复的索引
  • 对服务器信息进行汇总
  • 分析来自日志和tcpdump的查询
  • 当系统出问题的时候收集重要的系统信息

安装:

1
2
3
4
5
6
7
1. 下载,可以在官网找最新版本
wget https://www.percona.com/downloads/percona-toolkit/2.2.20/deb/percona-toolkit_2.2.20-1.tar.gz
2. 解压
tar zxvf percona-toolkit_2.2.20-1.tar.gz
3. 安装
perl Makefile.PL
make && make install

percona-toolkit 工具集下 pt-query-digest 命令可以对慢日志进行分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
./pt-query-digest  slow.log

# 170ms user time, 20ms system time, 26.18M rss, 76.54M vsz
# Current date: Fri Dec 23 02:20:25 2016
# Hostname: dev
# Files: /var/log/mysql/slow.log
# Overall: 3 total, 1 unique, 0.00 QPS, 0.00x concurrency ________________
# Time range: 2016-12-22T09:40:03 to 2016-12-23T02:19:35
# Attribute total min max avg 96% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 6s 2s 2s 2s 2s 0 2s
# Lock time 0 0 0 0 0 0 0
# Rows sent 3 1 1 1 1 0 1
# Rows examine 0 0 0 0 0 0 0
# Query size 45 15 15 15 15 0 15

这个部分是总体概要统计信息,对当前 MySQL 的查询性能做一个初步的评估,比如各个指标的最大值(max),
平均值(min),95%分布值,中位数(median),标准偏差(stddev)。这些指标有查询的执行时间(Exec time),
锁占用的时间(Lock time),MySQL 执行器需要检查的行数(Rows examine),
最后返回给客户端的行数(Rows sent),查询的大小 (Query size)。

后面就会把慢查询的 SQL 展示出来,这样就可以去对 SQL 进行分析优化

详情信息查看 官网,也可以了解其他 percona-toolkit 下的工具。

可以对日志进行分析,在实际开发中是一个十分有效的功能,可以快速的分析定位到系统 SQL 的瓶颈,便于做出更好的优化。

©版权声明:原创作品,允许转载,转载时请务必以超链接形式标明文章 原始出处 & 作者信息

End

坚持原创技术分享,您的支持将鼓励我继续创作!
Flyertutor WeChat Pay

WeChat Pay

Flyertutor Alipay

Alipay