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

作者介绍

陈臣，甲骨文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社群】，获取更多原创技术文章和精选工具下载

相关推荐

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

Vue自定义Hook示例:useUrlState（vue中的自定义指令如何使用）

Vue-实现自定义插件弹窗（vue 实现弹窗）

MySQL中这14个小玩意，让人眼前一亮!

旗舰机新标杆 OPPO Find X2系列正式发布售价5499元起

什么是幂等?分布式锁如何实现业务幂等?

手把手教你搞定菜单权限设计，精确到按钮级别，建议收藏

详解MySQL 字符串拼接之concat\concat_ws\group_concat

如何发个 npm 包?

如何快速切换node版本?利用n包快速切换nodejs版本

【Python机器学习系列】建立多层感知机模型预测心脏疾病