Automatically Kill Slow MySQL Queries After N Seconds

Stop runaway MySQL queries automatically – max_execution_time, pt-kill, and an event-scheduler watchdog, with exact commands and safe defaults.

Kill Slow MySQL Queries

Last updated: July 2026

One runaway SELECT can pile up connections until the whole application stalls. Instead of manually running SHOW PROCESSLIST and KILL at 2 a.m., set an automatic time limit. Three ways, in order of preference.

1. max_execution_time – built into MySQL

MySQL (5.7.8+) can abort long SELECT statements by itself. The value is in milliseconds:

-- kill SELECTs running longer than 10 seconds, server-wide
SET GLOBAL max_execution_time = 10000;

Make it permanent in my.cnf:

[mysqld]
max_execution_time = 10000

Or scope it where it belongs — per session or even per query:

SET SESSION max_execution_time = 5000;
SELECT /*+ MAX_EXECUTION_TIME(2000) */ * FROM huge_table WHERE ...;

Limitations: it applies to read-only SELECTs only (UPDATE/DELETE are not aborted, deliberately – killing writes mid-flight risks long rollbacks), and it doesn’t exist on MariaDB, which uses max_statement_time (in seconds) instead and applies it more broadly.

2. pt-kill — the classic watchdog

Percona Toolkit’s pt-kill watches the processlist and kills what matches:

sudo apt install percona-toolkit

# dry run first: print what WOULD be killed
pt-kill --busy-time 30 --match-command Query --print --interval 5

# then actually kill, as a daemon
pt-kill --busy-time 30 --match-command Query --kill --daemonize         --match-user app_user --log /var/log/pt-kill.log

--match-user is important – scope it to your application user so you never kill replication threads or backup jobs. Always run with --print before --kill.

3. Event scheduler – no extra tools

A watchdog inside MySQL itself:

SET GLOBAL event_scheduler = ON;

CREATE EVENT kill_slow_queries
ON SCHEDULE EVERY 10 SECOND
DO
  BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE qid BIGINT;
    DECLARE cur CURSOR FOR
      SELECT id FROM information_schema.processlist
      WHERE command = 'Query' AND time > 30
        AND user = 'app_user' AND info NOT LIKE '%processlist%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur;
    kill_loop: LOOP
      FETCH cur INTO qid;
      IF done THEN LEAVE kill_loop; END IF;
      KILL QUERY qid;
    END LOOP;
    CLOSE cur;
  END;

KILL QUERY aborts the statement but keeps the connection alive – usually what you want, since the application can retry.

Recommendation

Use method 1 as a safety net on every server (it costs nothing), and add pt-kill on servers where non-SELECT runaways or MariaDB are in play. And treat every kill as a symptom: pull the offending query from the slow query log and fix the missing index – killing is triage, not a cure. If you use these links to buy something, I may earn a commission at no extra cost to you.

Comments

comments