Mastering pt‑kill: Safely Terminate Problematic MySQL Connections

Introduction

pt‑kill is a powerful tool from the Percona Toolkit that can terminate MySQL connections. It is especially useful when idle connections exceed the maximum limit or a problematic query causes high load.

Basic Usage

To kill queries running longer than 30 seconds, use:

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)"

The --busy-time option sets the slow‑query threshold, while --interval defines how often pt‑kill runs SHOW FULL PROCESSLIST. If a query exceeds the threshold, pt‑kill issues a KILL command (enabled by --kill) and prints the killed SQL.

Victim Selection (--victims)

When multiple queries meet the criteria, pt‑kill’s --victims parameter controls which ones are killed:

oldest : kills only the longest‑running query (default).

all : kills every matching query.

all-but-oldest : kills all matching queries except the longest‑running one.

Filtering Parameters

pt‑kill can filter connections on many columns of SHOW PROCESSLIST using pairs of --ignore‑* and --match‑* options. Supported columns include USER, HOST, DB, STATE, COMMAND, and INFO. All filters accept regular expressions.

Note that --busy-time only applies to rows where COMMAND='Query'. For non‑query commands (e.g., SLEEP), only the filter options take effect.

Source‑Code Logic

The core of pt‑kill iterates over the process list, applies replication‑thread skipping, checks --busy-time or --idle-time, and then evaluates each filter. Matching rows are collected in an array @matches and returned for killing.

sub find {
  my ($self, $proclist, %find_spec) = @_;
  my @matches;
  QUERY:
  foreach my $query (@$proclist) {
    # skip replication threads if not requested
    next QUERY if !$find_spec{replication_threads} && $self->{MasterSlave}->is_replication_thread($query);
    my $matched = 0;
    # busy‑time handling
    if ($find_spec{busy_time} && exists $self->{kill_busy_commands}{$query->{Command} || ''}) {
      next QUERY unless defined $query->{Time};
      next QUERY if $query->{Time} < $find_spec{busy_time};
      push @{$self->{_reasons_for_matching}{$query} ||= []}, 'Exceeds busy time';
      $matched++;
    }
    # idle‑time handling (for SLEEP)
    if ($find_spec{idle_time} && ($query->{Command} || '') eq 'Sleep') {
      next QUERY unless defined $query->{Time};
      next QUERY if $query->{Time} < $find_spec{idle_time};
      push @{$self->{_reasons_for_matching}{$query} ||= []}, 'Exceeds idle time';
      $matched++;
    }
    # apply ignore/match filters for each property
    foreach my $property (qw(Id User Host db State Command Info)) {
      my $filter = "_find_match_$property";
      if (defined $find_spec{ignore}{$property} && $self->$filter($query, $find_spec{ignore}{$property})) {
        next QUERY;
      }
      if (defined $find_spec{match}{$property}) {
        unless ($self->$filter($query, $find_spec{match}{$property})) { next QUERY }
        push @{$self->{_reasons_for_matching}{$query} ||= []}, "Query matches $property spec";
        $matched++;
      }
    }
    if ($matched || $find_spec{all}) { push @matches, $query; next QUERY }
  }
  return @matches;
}

Common Use Cases

1. Record KILL actions in a database table

pt‑kill h=192.168.244.10,P=3306,u=pt_user,p=pt_pass \
  --busy-time 30 --interval 10 --print --kill \
  --log-dsn "DBI:mysql:database=percona;host=192.168.244.10" \
  --create-log-table

The tool logs each kill operation to the specified DSN; if the table does not exist, --create-log-table creates it.

Sample log entry:

kill_id: 1
server_id: 1
timestamp: 2022-01-05 22:00:11
reason: Exceeds busy time
kill_error: NULL
User: root
Host: localhost
Command: Query
Time: 35
Info: select sleep(120)

2. Run pt‑kill as a daemon

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

This continuously monitors and kills offending connections, writing actions to the log file.

By default, pt‑kill does not kill replication threads. Use --kill-query to kill only the query instead of the whole connection, or --print to preview actions without executing them.