MySQL性能分析与优化 - 慢查询日志

最新推荐文章于 2022-09-20 08:36:42 发布

薛定谔的码

最新推荐文章于 2022-09-20 08:36:42 发布

阅读量236

点赞数

分类专栏：数据库 # MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_39997438/article/details/108026899

版权

MySQL 同时被 2 个专栏收录

22 篇文章 3 订阅

订阅专栏

数据库

20 篇文章 1 订阅

订阅专栏

1、设置慢查询日志

1.1、配置参数说明

在MySQL配置文件（my.cnf）中，慢查询日志包含四个重要参数：

slow_query_log：是否开启慢查询日志，默认OFF，开启则设置为 ON。
slow_query_log_file：慢查询日志文件存储位置。
log_queries_not_using_indexes：是否把没有使用到索引的SQL记录到日志中，默认OFF，开启则设置为ON。
long_query_time：超过多少秒的查询才会记录到日志中。

1.2、配置参数设置

全局设置方式
进入MySQL服务，在MySQL服务内执行以下语句：

mysql> SET GLOBAL slow_query_log = 'ON';
mysql> SET GLOBAL slow_query_log_file = '文件绝对路径';
mysql> SET GLOBAL log_queries_not_using_indexes = 'ON';
# 这里需要注意下，long_query_time参数设置后需要下次会话后才生效，当前会话查询还是原来的数值
mysql> SET GLOBAL long_query_time = 1;

通过全局设置的方式，不需要重启MySQL，设置完后直接生效。缺点就是当MySQL重启时，全局设置方式设置的参数就会失效。

配置文件方式
在MySQL的配置文件 my.cnf 中添加以下四个属性：
```
slow_query_log=ON
slow_query_log_file=文件绝对路径
log_queries_not_using_indexes=ON
long_query_time=1
```
通过配置文件方式，需要重启MySQL之后才能生效，但好处是设置重启后永久生效。

1.3、检测设置结果

设置结束后，我们可以查看属性经设置之后的结果，如slow_query_log和slow_query_log_file：
在这里插入图片描述
如图所示，属性设置成功！我们在查看以下日志是否真正输出：

mysql> SELECT SLEEP(3);

查看慢查询日志：

tail -100f /var/lib/mysql/kkb-slow.log

在这里插入图片描述
此时说明参数设置成功！

2、分析慢查询日志的工具

2.1、mysqldumpslow

mysqldumpslow 是 MySQL 自带的慢查询日志工具。可以使用 mysqldumpslow 工具搜索慢查询日志中的SQL语句。以查看按照查询耗时排序的前4条里面含有 sleep 的查询语句为例：

mysqldumpslow -s t -t 4 -g "sleep" /var/lib/mysql/kkb-slow.log

参数	说明	备注
-s	表示按照何种方式排序	c：访问计数 l：锁定时间（L的小写） r：返回记录 t：查询时间 al：平均锁定时间 ar：平均返回记录数 at：平均查询时间
-t	即 top n，根据排序返回前多少条数据
-g	sql包含内容，支持正则匹配模式，大小写不敏感

查询结果如下：

在这里插入图片描述

2.2、percona-toolkit

percona-toolkit是一组高级命令行工具的集合，可以查看当前服务的摘要信息、磁盘检测、分析慢查询日志、查找重复索引、实现表同步等等。

2.2.1、下载

下载地址：https://www.percona.com/downloads/percona-toolkit/3.0.11/binary/tarball/percona-toolkit-3.0.11_x86_64.tar.gz

wget https://www.percona.com/downloads/percona-toolkit/3.0.11/binary/tarball/percona-toolkit-3.0.11_x86_64.tar.gz

2.2.2、安装

tar -xf percona-toolkit-3.0.11_x86_64.tar.gz
cd percona-toolkit-3.0.11
perl Makefile.PL
make
make install

若执行安装过程中某一操作出现以下错误，则执行对应操作并对该操作重新执行：

Can’t locate ExtUtils/MakeMaker.pm in @INC

yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

Can’t locate Time/HiRes.pm in @INC
```
yum install -y perl-Time-HiRes
```
Can’t locate Digest/MD5.pm in @INC
```
yum install perl-Digest-MD5.x86_64
```

2.2.3、查看

# 语法
pt-query-digest [OPTIONS] [FILES] [DSN]
# 案例
pt-query-digest /var/lib/mysql/kkb-slow.log

2.2.3.1、参数说明：

参数	说明
--create-review-table	当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table	当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter	对输入的慢查询按指定的字符串进行匹配过滤后再进行分析。
--limit	限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host	mysql服务器地址
--user	mysql用户名
--password	mysql用户密码
--history	将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同--CHECKSUM 来比较某类型查询的历史变化。
--review	将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output	分析结果输出类型，值可以是report（标准分析报告）、slowlog（Mysql slow log）、 json、json-anon，一般使用report，以便于阅读。
--since	从什么时间开始分析，值为字符串，可以是指定的某个“yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until	截止时间，配合--since可以分析一段时间内的慢查询，若没有设置该参数，则代表到当前为止。

2.2.3.2、用法说明

直接分析慢查询文件

pt-query-digest slow_report1.log > slow_report2.log

分析最近12小时内的查询

pt-query-digest --since=12h slow_report1.log > slow_report2.log

分析指定时间范围内的查询

pt-query-digest slow.log --since '2020-08-16 09:30:00' --until '2020-08-16 10:00:00'> > slow_report3.log

分析指含有select语句的慢查询

pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log

针对某个用户的慢查询

pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log

查询所有所有的全表扫描或full join的慢查询

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event-> {Full_join} || "") eq "yes")' slow.log> slow_report6.log

把查询保存到query_review表

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review--create-review-table slow.log

把查询保存到query_history表

pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history--create-review-table slow.log_0001
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_history--create-review-table slow.log_0002

通过tcpdump抓取mysql的tcp协议数据，然后再分析

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

分析binlog

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log

分析general log

pt-query-digest --type=genlog localhost.log > slow_report11.log

2.2.4、分析输出结果

第一部分：总体统计结果

# 该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
120ms user time, 20ms system time, 22.23M rss, 184.71M vsz
# 工具执行时间
Current date: Sun Aug 16 17:37:31 2020
# 运行分析工具的主机名
Hostname: centos-7.shared
# 被分析的文件名
Files: /var/lib/mysql/kkb-slow.log
# 语句总数量，唯一的语句数量，QPS，并发数
Overall: 4 total, 1 unique, 0.01 QPS, 0.03x concurrency ________________
# 日志记录的时间范围
Time range: 2020-08-15 23:46:04 to 23:54:58
# 属性			   总计		 最小	 最大	 平均	 95%  标准	  中间数
Attribute          total     min     max     avg     95%  stddev  median
============     ======= ======= ======= ======= ======= ======= =======
# 语句执行时间
Exec time            17s      1s     11s      4s     11s      4s      7s
# 锁占用时间
Lock time              0       0       0       0       0       0       0
# 发送到客户端的行数
Rows sent              4       1       1       1       1       0       1
# select语句扫描行数
Rows examine           0       0       0       0       0       0       0
# 查询的字符数
Query size            61      15      16   15.25   15.25    0.31   14.52

部分参数	参数说明
Overall	总共有多少条查询
Time range	查询执行的时间范围
unique	唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询
total	总计，min = 最小、max = 最大、avg = 平均
95%	把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值
median	中位数，把所有值从小到大排列，位置位于中间那个数

第二部分：查询分组统计结果

Profile
Rank Query ID                           Response time  Calls R/Call V/M  Item
==== ================================== ============== ===== ====== ==== ======
   1 0x59A74D08D407B5EDF9A57DD5A41825CA 17.0036 100.0%     4 4.2509 3.59 SELECT

部分参数	参数说明
Rank	所有语句的排名，默认按查询时间降序排列，通过 --order-by 指定
Query ID	语句的ID，是将Query语句去掉多余空格与文本字符后，通过hash函数转化成的hash值
Response	总的响应时间
time	该查询在本次分析中总的时间占比
calls	执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call	平均每次执行的响应时间
V/M	响应时间Variance-to-mean的比率
Item	查询对象

第三部分：每一种查询的详细统计结果

Query 1: 0.01 QPS, 0.03x concurrency, ID 0x59A74D08D407B5EDF9A57DD5A41825CA at byte 0
This item is included in the report because it matches --limit.
Scores: V/M = 3.59
Time range: 2020-08-15 23:46:04 to 23:54:58
Attribute    pct   total     min     max     avg     95%  stddev  median
============ === ======= ======= ======= ======= ======= ======= =======
Count        100       4
Exec time    100     17s      1s     11s      4s     11s      4s      7s
Lock time      0       0       0       0       0       0       0       0
Rows sent    100       4       1       1       1       1       0       1
Rows examine   0       0       0       0       0       0       0       0
Query size   100      61      15      16   15.25   15.25    0.31   14.52
String:
Hosts        localhost
Users        root
Query_time distribution
  1us
 10us
100us
  1ms
 10ms
100ms
   1s  ################################################################
 10s+  #####################
EXPLAIN /*!50100 PARTITIONS*/
select sleep(11)\G

由下面查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。

部分参数	参数说明
ID	查询的ID号，与第二部分中的Query ID对应
Databases	数据库名
Users	各个用户执行的次数（占比）
Query_time distribution	查询时间分布, 长短体现区间占比
Tables	查询中涉及到的表
Explain	SQL语句

薛定谔的码

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL性能分析与优化 - 慢查询日志

1、设置慢查询日志1.1、配置参数说明在MySQL配置文件（my.cnf）中，慢查询日志包含四个重要参数：slow_query_log：是否开启慢查询日志，默认OFF，开启则设置为 ON。slow_query_log_file：慢查询日志文件存储位置。log_queries_not_using_indexes：是否把没有使用到索引的SQL记录到日志中，默认OFF，开启则设置为ON。long_query_time：超过多少秒的查询才会记录到日志中。1.2、配置参数设置全局设置方式
复制链接

扫一扫