会用这款工具，没有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_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 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。
社群福利
本文精选自陈臣《MySQL实战》一书，想阅读此书更多超卓内容的伴侣，可在微信订阅号（dbaplus社群）评论区留言，分享#关于数据库的进修心得#或#目前亟需处理的数据库工做疑难#，小编将鄙人周五（4月28日）中午12点，根据留言超卓度选出5位读者，送出《MySQL实战》一本~
特殊喊谢@图灵教导供给赠书。当然，迫不及待想进手那本书的伴侣，可间接登录网址：，近期购书优惠很给力哦~
关于我们
dbaplus社群是围绕Database、BigData、AIOps的企业级专业社群。资深大咖、手艺干货，天天精品原创文章推送，每周线上手艺分享，每月线下手艺沙龙，每季度GdevopsDAMS行业大会。
存眷公家号【dbaplus社群】，获取更多原创手艺文章和精选东西下载