pt-kill seems to ignore options

MySQL & MariaDB Other MySQL® Questions
1

I’m not sure if I’m using it wrong, but right now I’m using pt-kill 3.2.0 with MariaDB 10.3.23 on Ubuntu and it seems to indiscriminately kill any running query. Here’s what I’m using from the command line, the goal is to kill queries run by ‘username’ that have been running for at least 5 minutes:

/usr/bin/pt-kill --busy-time 300 --match-user username --kill --print --pid=/var/run/ptkill.pid --interval=5 --defaults-file=/root/.my.cnf --log=/var/log/ptkill.log

And here’s an example of the output I’m getting:

# 2020-06-26T18:01:24 KILL 18754 (Sleep 0 sec) NULL<br># 2020-06-26T18:01:29 KILL 18785 (Sleep 0 sec) NULL<br># 2020-06-26T18:01:34 KILL 18800 (Sleep 2 sec) NULL<br># 2020-06-26T18:01:39 KILL 18809 (Sleep 6 sec) NULL<br># 2020-06-26T18:01:44 KILL 18808 (Sleep 11 sec) NULL<br># 2020-06-26T18:01:49 KILL 18807 (Sleep 16 sec) NULL<br># 2020-06-26T18:01:54 KILL 18846 (Sleep 18 sec) NULL<br># 2020-06-26T18:01:59 KILL 18864 (Sleep 20 sec) NULL<br># 2020-06-26T18:02:04 KILL 19009 (Sleep 0 sec) NULL<br># 2020-06-26T18:02:09 KILL 19053 (Execute 0 sec) select s.source from report_sources s where s.profile_id = '184' and s.profile_type = '2' and s.report_type = 5 and s.date_frequency = 'FY'<br># 2020-06-26T18:02:34 KILL 19177 (Sleep 3 sec) NULL<br># 2020-06-26T18:02:49 KILL 19275 (Sleep 1 sec) NULL<br># 2020-06-26T18:02:59 KILL 19358 (Sleep 0 sec) NULL<br># 2020-06-26T18:03:04 KILL 19373 (Sleep 0 sec) NULL<br># 2020-06-26T18:03:09 KILL 19416 (Sleep 2 sec) NULL<br># 2020-06-26T18:03:49 KILL 19599 (Sleep 8 sec) NULL<br># 2020-06-26T18:03:54 KILL 19729 (Sleep 2 sec) NULL<br># 2020-06-26T18:03:59 KILL 19759 (Sleep 0 sec) NULL<br># 2020-06-26T18:04:04 KILL 19772 (Sleep 0 sec) NULL<br># 2020-06-26T18:04:24 KILL 19880 (Sleep 5 sec) NULL<br># 2020-06-26T18:04:29 KILL 19879 (Sleep 10 sec) NULL<br># 2020-06-26T18:07:09 KILL 21478 (Prepare 0 sec) select s.id as tokenId from sessions s join clients c on s.clientid = c.id where s.id != unhex(?) and userid = (?) and c.identifier = (?) and s.expires &gt; now() and c.enabled = 1

Am I missing something obvious or is pt-kill definitely not working as expected here?

2

Hi,   from the documentation: https://www.percona.com/doc/percona-toolkit/LATEST/pt-kill.html
you’ll find this: “pt-kill will kill all the queries matching ANY of the specified criteria (logical OR).”.  What you have is “kill any query running for more than 300s OR any query from username”.  You need a custom filter,  but the following in a file:
my $event_ok; if (($event->{Time} > 300) && ($event->{User} == ‘username’)) { $event_ok=1; } else { $event_ok=0; } $event_ok

The above example is untested but should work.  The documentation is a bit weak, I’ll submit a ticket for that.  $event is provided by pt-kill is essentially the output of “show full processlist” with fetchall_arrayref.





3

So, I actually tested the filter,  the filter needs to be:

and the pt-kill invocation:
pt-kill --filter /tmp/filter.txt --match-user=username --kill-busy-commands ‘Query|Execute’ --print --kill --interval=5

4

Thanks heaps! This looks like it works now. I had to add “(defined $event->{Time})” as an additional condition to the if statement to get around “InnoDB purge worker” queries appearing in my processlist with a Time of NULL which caused “Use of uninitialized value in numeric gt (>) at (eval 37) line 2.” to keep appearing, and it seems like --kill-busy-commands were being ignored as it still killed stuff running as “Sleep”, but I should be able to modify that filter a bit more to get it to catch them properly there.