会用这款工具,没有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 --printkill –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,近期购书优惠很给力哦~

最新评论
访客 2023年08月20日

230721

访客 2023年08月16日

1、导入Mongo Monitor监控工具表结构(mongo_monitor…

访客 2023年08月04日

上面提到: 在问题描述的架构图中我们可以看到,Click…

访客 2023年07月19日

PMM不香吗?

访客 2023年06月20日

如今看都很棒

活动预告