会用这款工具，没有MySQL慢查询能逃出生天（文末送书）

陈臣 2023-04-22 10:16:00
作者介绍

陈臣，甲骨文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 PROCESSLIST2022-01-05T21:28:57.349148+08:00      106 Query    SHOW FULL PROCESSLIST2022-01-05T21:28:57.349763+08:00      106 Query    KILL '103'2022-01-05T21:29:07.350167+08:00      106 Query    SHOW FULL PROCESSLIST2022-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 -p123456mysql> 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_spec{busy_time} ) {            PTDEBUG && _d("Query isn't running long enough");            next QUERY;         }         my $reason = 'Exceeds busy time';         PTDEBUG && _d($reason);         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_spec{idle_time} ) {            PTDEBUG && _d("Query isn't idle long enough");            next QUERY;         }         my $reason = 'Exceeds idle time';         PTDEBUG && _d($reason);         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 timekill_error:        Id: 128      User: root      Host: localhost        db: NULL   Command: Query      Time: 35     State: User sleep      Info: select sleep(120)   Time_ms: NULL1 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。

 

 

 
社群福利
 

 

本文精选自陈臣《MySQL实战》一书，想阅读此书更多精彩内容的朋友，可在微信订阅号（dbaplus社群）评论区留言，分享#关于数据库的学习心得#或#目前亟需解决的数据库工作疑难#，小编将在下周五（4月28日）中午12点，根据留言精彩度选出5位读者，送出《MySQL实战》一本~

 

特别鸣谢@图灵教育提供赠书。当然，迫不及待想入手这本书的朋友，可直接登录网址：https://item.jd.com/13677965.html，近期购书优惠很给力哦~

热门文章
最新评论
访客 2024年04月08日

如果字段的最大可能长度超过255字节，那么长度值可能…

访客 2024年03月04日

只能说作者太用心了，优秀

访客 2024年02月23日

感谢详解

访客 2024年02月20日

一般干个7-8年（即30岁左右），能做到年入40w-50w；有…

访客 2023年08月20日

230721

活动预告
Copyright © 广州新炬网络技术有限公司 All rights reserved. 粤ICP备2024180830号-3