百度360必应搜狗淘宝本站头条
vue dispatchspring gatewayorder by ascidea eval resetupdate canceled
当前位置：网站首页 > 技术分类 > 正文

会用这款工具，没有MySQL慢查询能逃出生天

ztj100 2025-04-11 09:49 12 浏览 0 评论


作者介绍

陈臣，甲骨文MySQL首席解决方案工程师，公众号《MySQL实战》作者，有大规模的MySQL、Redis、MongoDB、ES的管理和维护经验，擅长MySQL数据库的性能优化及日常操作的原理剖析。（本文摘录自陈臣老师新书《MySQL实战》-第九章 MySQL的常用工具）


pt-kill介绍


pt-kill是一款优秀的kill MySQL连接的工具，是percona toolkit的一部分。在因为空闲连接较多导致超过最大连接数、某个有问题的SQL导致MySQL负载很高时，都需要将一些连接kill掉，pt-kill就有着这样的功能。


pt-kill实现原理


首先，看看线上的一个高频需求，即杀掉执行时间超过30秒的慢查询。具体命令如下。


pt-kill h=192.168.244.10,P=3306,u=pt_user,p=pt_pass --busy-time 30 --interval 10 --print –kill –match-info "(?i-xsm:select)"


命令行中的 --busy-time定义了慢查询的阈值，--interval指的是检测时间间隔，这里pt-kill会每隔10秒执行一次SHOW FULL PROCESSLIST操作，看看是否有执行时间超过30秒的查询。如果有，则执行KILL操作（由 --kill参数决定），并将执行的KILL操作及被杀掉的SQL语句打印出来（--print）。


注意，--busy-time针对的是Command列为Query的操作，而SHOW PROCESSLIST中Command列为Query的操作不仅仅包括SELECT，同样也包括DELECT、INSERT、UPDATE和ALTER操作。所以为了保证杀掉的一定是SELECT操作，这里使用了--match-info进行过滤。--match-info匹配的是SHOW PROCESSLIST中Info列的内容。?i-xsm:^select是正则表达式，匹配以select开头的操作，不区分大小写。


看看该命令的输出及对应的general log。


# 2022-01-05T21:28:57 KILL 103 (Query 39 sec) select sleep(100)
# 2022-01-05T21:29:07 KILL 105 (Query 47 sec) select sleep(200)
2022-01-05T21:28:47.348592+08:00      106 Query    SHOW FULL PROCESSLIST
2022-01-05T21:28:57.349148+08:00      106 Query    SHOW FULL PROCESSLIST
2022-01-05T21:28:57.349763+08:00      106 Query    KILL '103'
2022-01-05T21:29:07.350167+08:00      106 Query    SHOW FULL PROCESSLIST
2022-01-05T21:29:07.350651+08:00      106 Query    KILL '105'
2022-01-05T21:29:17.352402+08:00      106 Query    SHOW FULL PROCESSLIST


可以看到，在杀掉第一个查询的时候，第二个查询其实也满足条件，但没被杀掉，而是等到下一轮检测才被杀掉。这个行为实际上是由 --victims参数控制的，--victims取值如下。


(1)oldest：每次只会杀掉执行时间最长的那个查询，是默认值。


(2)all：杀掉所有符合条件的查询。


(3)all-but-oldest：杀掉所有符合条件的查询，除了执行时间最长的那个。


既然是基于SHOW PROCESSLIST的输出，pt-kill就可从多个维度进行过滤，具体的过滤参数如下。


  • --ignore-user、--match-user


基于USER列的输出进行过滤。


  • --ignore-host、--match-host


基于HOST列的输出进行过滤。


  • --ignore-db、--match-db


基于db列的输出进行过滤。


  • --ignore-command、--match-command


基于command列的输出进行过滤。


  • --ignore-state、--match-state


基于State列的输出进行过滤。


  • --ignore-info、--match-info


基于Info列的输出进行过滤。


以上过滤参数均支持正则匹配。


需要注意的是，如果同时指定了 --busy-time和过滤参数，对于Command列不为Query的操作，此时起作用的将只有过滤参数，没有--busy-time。看下面这个示例。


# mysql -h 192.168.244.10 -uu1 -p123456
mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|             113 |
+-----------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from slowtech.t1 limit 1;
Query OK, 1 row affected (0.00 sec)


执行pt-kill操作。


pt-kill h=192.168.244.10,P=3306,u=pt_user,p=pt_pass --busy-time 30 --interval 10 --print --kill --match-user u1 
# 2022-01-05T21:40:43 KILL 113 (Sleep 9 sec) NULL


这本来是要将来自u1的执行时间超过30秒的操作杀掉，却意外地杀掉了一个未提交的事务。


究其原因，是 --busy-time只对Command列为Query的操作才有效果，而这个事务对应的Command列是Sleep。


pt-kill过滤逻辑


下面从源码的角度分析pt-kill的过滤逻辑，这样我们才能更加清晰地知道--busy-time和过滤参数之间的关系。


sub find {
   my ( $self, $proclist, %find_spec ) = @_;
   PTDEBUG && _d('find specs:', Dumper(\%find_spec));
   my $ms  = $self->{MasterSlave};
   # 定义一个数组，用来存储需要杀掉的操作
   my @matches;
   $self->{_reasons_for_matching} = undef;
   QUERY:
   # 遍历SHOW FULL PROCESSLIST的输出
   foreach my $query ( @$proclist ) {
      PTDEBUG && _d('Checking query', Dumper($query));
      my $matched = 0;
      # 如果命令行中不指定--replication-threads，则默认会跳过复制相关线程
      if (    !$find_spec{replication_threads}
           && $ms->is_replication_thread($query) ) {
         PTDEBUG && _d('Skipping replication thread');
         next QUERY;
      }
      # $self->{kill_busy_commands}是一张哈希表，exists用来判断哈希表中是否有指定键
      # $self->{kill_busy_commands}中的键由--kill-busy-commands指定，不指定则默认为Query。
      if ( $find_spec{busy_time} && exists($self->{kill_busy_commands}->{$query->{Command} || ''}) ) {
         next QUERY unless defined($query->{Time});
         # 如果操作的执行时间小于--busy-time，则会跳过当前操作，不会进行其他判断
         if ( $query->{Time} < find_specbusy_time ptdebug _dquery isnt running long enough next query my reason='Exceeds busy time' ptdebug _dreason push self->{_reasons_for_matching}->{$query} ||= []}, $reason;
         $matched++;
      }
      # 如果命令行中指定了--idle-time，则只会匹配Command为Sleep类型的操作
      if ( $find_spec{idle_time} && ($query->{Command} || '') eq 'Sleep' ) {
         next QUERY unless defined($query->{Time});
         # 如果操作的执行时间小于--idle-time，则会跳过当前操作，不会进行其他判断
         if ( $query->{Time} < find_specidle_time ptdebug _dquery isnt idle long enough next query my reason='Exceeds idle time' ptdebug _dreason push self->{_reasons_for_matching}->{$query} ||= []}, $reason;
         $matched++;
      }
      
      PROPERTY:
      # 判断操作是否满足--ignore-user，--match-user之类参数指定的条件
      foreach my $property ( qw(Id User Host db State Command Info) ) {
         my $filter = "_find_match_$property";
         # 如果设置了ignore相关的参数，且操作满足ignore参数指定的条件，则会跳过当前操作
         if ( defined $find_spec{ignore}->{$property}
              && $self->$filter($query, $find_spec{ignore}->{$property}) ) {
            PTDEBUG && _d('Query matches ignore', $property, 'spec');
            next QUERY;
         }
         # 如果设置了match相关的参数，且操作不满足match参数指定的条件，则会跳过当前操作
         if ( defined $find_spec{match}->{$property} ) {
            if ( !$self->$filter($query, $find_spec{match}->{$property}) ) {
               PTDEBUG && _d('Query does not match', $property, 'spec');
               next QUERY;
            }
            my $reason = 'Query matches ' . $property . ' spec';
            PTDEBUG && _d($reason);
            push @{$self->{_reasons_for_matching}->{$query} ||= []}, $reason;
            $matched++;
         }
      }
      # 将满足条件、需要杀掉的操作添加到@matches
      # $find_spec{all}对应命令行中的--match-all参数
      if ( $matched || $find_spec{all} ) {
         PTDEBUG && _d("Query matched one or more specs, adding");
         push @matches, $query;
         next QUERY;
      }
      PTDEBUG && _d('Query does not match any specs, ignoring');
   } # QUERY
   return @matches;
}


从源码中可以得出以下几点：


(1)--busy-time只适用于Command列为Query的操作。

(2)--idle-time只适用于Command列为Sleep的操作。

(3)--idle-time和--busy-time的处理逻辑相同。

(4)对于Command列不为Query的操作，只能通过 --ignore-user、--match-user之类的参数进行过滤。

(5)对于Command列为Query的操作，当执行时长超过 --busy-time时，将进一步通过 --ignore-user、--match-user之类的参数进行过滤。

(6)--match-all参数用来匹配所有未被忽略的操作，可用来实现否定匹配的功能。


pt-kill常见用法


1. 将KILL操作记录在数据库中


具体命令如下。


pt-kill h=192.168.244.10,P=3306,u=pt_user,p=pt_pass --busy-time 30 --interval 10 --print --kill --log-dsn 
h=192.168.244.10,P=3306,u=pt_user,p=pt_pass,D=percona,t=kill_log --create-log-table


KILL操作会记录在 --log-dsn指定的实例中，如果表不存在，可指定 --create-log-table创建。表中记录如下。


mysql> select *  from percona.kill_log limit 1\G
*************************** 1. row ***************************
   kill_id: 1
 server_id: 1
 timestamp: 2022-01-05 22:00:11
    reason: Exceeds busy time
kill_error:
        Id: 128
      User: root
      Host: localhost
        db: NULL
   Command: Query
      Time: 35
     State: User sleep
      Info: select sleep(120)
   Time_ms: NULL
1 row in set (0.00 sec)


2. 将pt-kill作为守护进程运行


具体命令如下：


pt-kill h=192.168.244.10,P=3306,u=pt_user,p=pt_pass --busy-time 30 --interval 10 --print --kill --log /tmp/pt-kill.log --daemonize


执行的kill操作会记录在 --log指定的文件中。


默认情况下，pt-kill不会杀掉复制相关的连接。


上述命令都指定了 --kill，此时会杀掉连接。如果只想杀掉查询，而不是连接，可指定 --kill-query。如果只是打印，而不是实际执行KILL操作，只需指定 --print。



关于我们

dbaplus社群是围绕Database、BigData、AIOps的企业级专业社群。资深大咖、技术干货，每天精品原创文章推送，每周线上技术分享，每月线下技术沙龙，每季度Gdevops&DAMS行业大会。

关注公众号【dbaplus社群】，获取更多原创技术文章和精选工具下载

相关推荐

如何将数据仓库迁移到阿里云 AnalyticDB for PostgreSQL

阿里云AnalyticDBforPostgreSQL（以下简称ADBPG，即原HybridDBforPostgreSQL）为基于PostgreSQL内核的MPP架构的实时数据仓库服务，可以...

Python数据分析:探索性分析

写在前面如果你忘记了前面的文章，可以看看加深印象：Python数据处理...

CSP-J/S冲奖第21天:插入排序

...

C++基础语法梳理:算法丨十大排序算法(二)

本期是C++基础语法分享的第十六节，今天给大家来梳理一下十大排序算法后五个！归并排序...

C 语言的标准库有哪些

C语言的标准库并不是一个单一的实体，而是由一系列头文件(headerfiles)组成的集合。每个头文件声明了一组相关的函数、宏、类型和常量。程序员通过在代码中使用#include<...

[深度学习] ncnn安装和调用基础教程

1介绍ncnn是腾讯开发的一个为手机端极致优化的高性能神经网络前向计算框架，无第三方依赖，跨平台，但是通常都需要protobuf和opencv。ncnn目前已在腾讯多款应用中使用，如QQ，Qzon...

用rust实现经典的冒泡排序和快速排序

1.假设待排序数组如下letmutarr=[5,3,8,4,2,7,1];...

ncnn+PPYOLOv2首次结合!全网最详细代码解读来了

编辑：好困LRS【新智元导读】今天给大家安利一个宝藏仓库miemiedetection，该仓库集合了PPYOLO、PPYOLOv2、PPYOLOE三个算法pytorch实现三合一，其中的PPYOL...

C++特性使用建议

1.引用参数使用引用替代指针且所有不变的引用参数必须加上const。在C语言中，如果函数需要修改变量的值，参数必须为指针，如...

Qt4/5升级到Qt6吐血经验总结V202308

00：直观总结增加了很多轮子，同时原有模块拆分的也更细致，估计为了方便拓展个管理。把一些过度封装的东西移除了（比如同样的功能有多个函数），保证了只有一个函数执行该功能。把一些Qt5中兼容Qt4的方法废...

到底什么是C++11新特性，请看下文

C++11是一个比较大的更新，引入了很多新特性，以下是对这些特性的详细解释，帮助您快速理解C++11的内容1.自动类型推导（auto和decltype）...

掌握C++11这些特性，代码简洁性、安全性和性能轻松跃升!

C++11（又称C++0x）是C++编程语言的一次重大更新，引入了许多新特性，显著提升了代码简洁性、安全性和性能。以下是主要特性的分类介绍及示例：一、核心语言特性1.自动类型推导（auto）编译器自...

经典算法——凸包算法

凸包算法（ConvexHull）一、概念与问题描述凸包是指在平面上给定一组点，找到包含这些点的最小面积或最小周长的凸多边形。这个多边形没有任何内凹部分，即从一个多边形内的任意一点画一条线到多边形边界...

一起学习c++11——c++11中的新增的容器

c++11新增的容器1:array当时的初衷是希望提供一个在栈上分配的，定长数组，而且可以使用stl中的模板算法。array的用法如下:#include<string>#includ...

C++ 编程中的一些最佳实践

1.遵循代码简洁原则尽量避免冗余代码，通过模块化设计、清晰的命名和良好的结构，让代码更易于阅读和维护...

取消回复欢迎 你 发表评论:

一周热门
最近发表
标签列表
Copyright Your WebSite.Some Rights Reserved.