MySQL慢查询mysqldumpslow和pt-query-digest工具

最新推荐文章于 2024-07-23 09:37:51 发布

hikktn

最新推荐文章于 2024-07-23 09:37:51 发布

阅读量571

点赞数

分类专栏： # MySQL

本文链接：https://blog.csdn.net/qq_41520636/article/details/116546413

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

Windows环境

准备

https://dev.mysql.com/doc/index-other.html

sakila-schema.sql是创建表的sql

sakila-data.sql是数据的sql

第一个sql文件可以执行，但是第二个sql就不行了。报错！

选择使用命令行界面，将sakila-data.sql里面的数据，全部复制到命令行界面，回车执行。

ok！没有任何问题。

开启慢查询

临时开启慢查询

set global slow_query_log = on;

修改慢查询日志存储目录

set global slow_query_log_file='F:\\logs\\mysql\\mysql5.7.23\\data\\my-hikktn-slow.log';

查询慢查询

mysql> show variables like 'slow_query%';
+---------------------+---------------------------------------------------+
| Variable_name       | Value                                             |
+---------------------+---------------------------------------------------+
| slow_query_log      | ON                                                |
| slow_query_log_file | F:\logs\mysql\mysql5.7.23\data\my-hikktn-slow.log |
+---------------------+---------------------------------------------------+
2 rows in set (0.07 sec)

开启无索引查询存储日志

set global log_queries_not_using_indexes=on;

设置查询速度最大执行时间，存储日志

注意：默认10秒，并且需要重新进入mysql，才会起作用。

set global long_query_time= 1.0;

测试

select sleep(15);

格式说明：

1、# Time: 2021-05-08T11:18:28.830342Z -------查询的执行时间

2、# User@Host: root[root] @ localhost [::1] Id: 2 -------执行sql的主机信息

3、# Query_time: 14.999794 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 -------SQL的执行信息：

Query_time：SQL的查询时间
Lock_time：锁定时间
Rows_sent：所发送的行数
Rows_examined：锁扫描的行数

4、SET timestamp=1620472708; ------SQL执行时间

5、select sleep(15); -------SQL的执行内容

永久开启慢查询

在my.ini配置文件修改

# 开启慢查询
slow_query_log = ON
# 慢日志存储目录
log-slow-queries = "F:/logs/mysql/mysql5.7.23/data/my-hikktn-slow.log"
# 最长执行事件（查询的最长事件，超过这个时间则记录到日志中）
long_query_time = 1
# 没有使用索引的查询也会保存到日志中
log-queries-not-using-indexes = ON

慢查日志分析工具 mysqldumpslow

perl mysqldumpslow.pl -h

perl mysqldumpslow.pl -v

perl mysqldumpslow.pl -t 10 "F:\logs\mysql\mysql5.7.23\data\my-hikktn-slow.log"

和上面的解释一样没有什么变化

执行时间，锁定时间，发送的行数，扫描的行数

慢查日志分析工具 pt-query-digest

直接慢查询日志分析

 perl pt-query-digest.pl  "F:\logs\mysql\mysql5.7.23\data\my-hikktn-slow.log"

时间内慢查询日志分析

 perl pt-query-digest.pl --since=6h "F:\logs\mysql\mysql5.7.23\data\my-hikktn-slow.log"

时间范围内慢查询日志分析

perl pt-query-digest.pl --since='2021-05-07 00:00:00.000' --until '2021-05-09 00:00:00.000' "F:\logs\mysql\mysql5.7.23\data\my-hikktn-slow.log"

select慢查询日志分析

perl pt-query-digest.pl --filter '$event->{fingerprint} =~ m/^select/i' "F:\logs\mysql\mysql5.7.23\data\my-hikktn-slow.log"

Linux环境

上面的命令一样，只是不同的方法执行，不再赘述。

而mysqldumpslow和pt-query-digest工具的使用只是出现了一些变化。本人是在docker环境安装的mysql，所以需要进入容器内。

mysqldumpslow -h

mysqldumpslow -v

mysqldumpslow -t 10 /var/lib/mysql/3de8e50705e9-slow.log

安装pt-query-digest工具

wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum localinstall -y percona-toolkit-2.2.16-1.noarch.rpm

验证

pt-summary

结果如下

pt-summary工具命令

查看服务器信息

pt-summary

查看磁盘开销使用信息

pt-diskstats

查看mysql数据库信息

pt-mysql-summary --user=root --password=123456

慢查询日志

注意：如果你们没有安装在docker容器里面，那么还是执行下面这句简单的命令就行

pt-query-digest /data/mysql/data/db-3-12-slow.log

docker容器，就比较麻烦了，需要找到对应的log文件，全路径查询

一般默认存放docker容器的文件地址 /var/lib/docker/

这里面有以id名存放的文件夹

[root@localhost ~]# cd /var
[root@localhost var]# find -name '3de8e50705e9-slow.log*' -ls
11988246    4 -rw-rw----   1 polkitd  input        3214 5月  9 02:21 ./lib/docker/volumes/8b1c06c762f2a13d8973e253d2d69c28839a013e455083cd23edfab8030c716d/_data/3de8e50705e9-slow.log

当我们查到地址后，和上面一样，只是地址变了。

 pt-query-digest ./lib/docker/volumes/8b1c06c762f2a13d8973e253d2d69c28839a013e455083cd23edfab8030c716d/_data/3de8e50705e9-slow.log

查找mysql的从库和同步状态

pt-slave-find --host=localhost --user=root --password=123456

查看mysql的死锁信息

pt-deadlock-logger --user=root --password=123456 localhost

根据我的测试，docker容器就放弃吧，不兼容。

从慢查询日志中分析索引使用情况

pt-index-usage slow_20131009.log

查找数据库表中重复的索引

pt-duplicate-key-checker --host=localhost --user=root --password=123456

查看mysql表和文件的当前活动IO开销

pt-ioprofile

查看不同mysql配置文件的差异

pt-config-diff /etc/my.cnf /etc/my_master.cnf

pt-find查找mysql表和执行命令

查找数据库里大于2G的表：

pt-find --user=root --password=123456 --tablesize +2G

查找10天前创建，MyISAM引擎的表：

pt-find --user=root --password=123456 --ctime +10 --engine MyISAM

查看表和索引大小并排序

pt-find --user=root --password=123456 --printf "%T\t%D.%N\n" | sort -rn

pt-kill 杀掉符合标准的mysql进程

显示查询时间大于60秒的查询

pt-kill --user=root --password=123456 --busy-time 60 --print

kill掉大于60秒的查询

pt-kill --user=root --password=123456 --busy-time 60 --kill

查看mysql授权

1、pt-show-grants --user=root --password=123456
2、pt-show-grants --user=root --password=123456 --separate –revoke

验证数据库复制的完整性

pt-table-checksum --user=root --password=123456