September 28, 2025

Automatically Killing Long-Running Queries in Aurora MySQL (Serverless v2)

Long-running queries can wreak havoc on Aurora MySQL performance. Learn how to automatically detect, log, and kill queries that exceed a time limit using MySQL’s event scheduler. This step-by-step guide walks through building a killer procedure, testing safely, scheduling it, and keeping full audit logs.

Introduction

One of the most frustrating issues for database administrators is when a query runs far longer than expected. Long-running queries can tie up CPU, block locks, and degrade performance for every other workload on the cluster.

On Amazon Aurora MySQL (Serverless v2), you don’t have direct OS-level access to cron or external process managers, so you need to handle this inside the database itself. Fortunately, MySQL’s event scheduler and a bit of stored procedure logic provide everything you need to automatically find, log, and kill queries that exceed a defined threshold.

In this post, we’ll walk step-by-step through building a solution that:

Detects queries running longer than a threshold (e.g., 120 seconds).
Kills them cleanly.
Logs every action in an audit table.
Provides a smoke test so you can verify behavior without actually killing queries.
Lets you adjust frequency, thresholds, and exclusions to fit your environment.

Step 1. Create the Log Table

We’ll start by creating a table that records every action. This log will capture details like the thread ID, user, query text, and who killed it.

CREATE SCHEMA IF NOT EXISTS admin;
CREATE TABLE IF NOT EXISTS admin.kill_log (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  ts         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  thread_id  BIGINT,
  user       VARCHAR(128),
  host       VARCHAR(255),
  db         VARCHAR(128),
  time_sec   INT,
  info       TEXT,
  killed_by  VARCHAR(128)
) ENGINE=InnoDB;

CREATE SCHEMA IF NOT EXISTS admin;

CREATE TABLE IF NOT EXISTS admin.kill_log (

id BIGINT AUTO_INCREMENT PRIMARY KEY,

ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

thread_id BIGINT,

user VARCHAR(128),

host VARCHAR(255),

db VARCHAR(128),

time_sec INT,

info TEXT,

killed_by VARCHAR(128)

) ENGINE=InnoDB;

This table will also serve as a target for our test events to verify that the event scheduler is working.

Step 2. Verify and Enable the Event Scheduler

Aurora MySQL uses the event scheduler to run recurring jobs inside the database. By default, this may be turned off.

Check the status:

SHOW VARIABLES LIKE 'event_scheduler';

1 2	SHOW VARIABLES LIKE 'event_scheduler';

ON → Events will run normally.
OFF or DISABLED → You’ll need to enable it.

In Aurora, this requires changing the DB cluster parameter group:

In the AWS Console, go to RDS → Databases → Your Aurora cluster → Configuration.
Note the DB cluster parameter group.
Go to RDS → Parameter groups and edit the group (or create a new one).
Set event_scheduler = ON.
Apply the group to your cluster. Aurora Serverless v2 usually applies changes quickly.

Optional Test

To confirm the scheduler is working, create a test event that inserts a row into the log after one minute:

CREATE EVENT IF NOT EXISTS admin.test_evt
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO
  INSERT INTO admin.kill_log(thread_id,user,host,db,time_sec,info,killed_by)
  VALUES (NULL, 'event_test', NULL, NULL, 0, 'event fired', CURRENT_USER());

CREATE EVENT IF NOT EXISTS admin.test_evt

ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE

INSERT INTO admin.kill_log(thread_id,user,host,db,time_sec,info,killed_by)

VALUES (NULL, 'event_test', NULL, NULL, 0, 'event fired', CURRENT_USER());

After a minute, query the log:

SELECT * FROM admin.kill_log ORDER BY id DESC LIMIT 5;

1 2	SELECT * FROM admin.kill_log ORDER BY id DESC LIMIT 5;

If you see a row with event_test, the scheduler is active. Clean up when done:

DROP EVENT IF EXISTS admin.test_evt;

1 2	DROP EVENT IF EXISTS admin.test_evt;

Step 3. Build a Smoke Test (No Kill)

Before introducing any destructive behavior, it’s good practice to confirm your logic is sound. The smoke test scans the processlist for long-running queries and logs them—but does not kill them.

DELIMITER //

DROP PROCEDURE IF EXISTS admin.scan_long_queries_log_only//
CREATE PROCEDURE admin.scan_long_queries_log_only(IN p_threshold_seconds INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_id   BIGINT;
  DECLARE v_user VARCHAR(128);
  DECLARE v_host VARCHAR(255);
  DECLARE v_db   VARCHAR(128);
  DECLARE v_time INT;
  DECLARE v_info LONGTEXT;

  DECLARE cur CURSOR FOR
    SELECT ID, USER, HOST, DB, TIME, INFO
    FROM INFORMATION_SCHEMA.PROCESSLIST
    WHERE COMMAND = 'Query'
      AND TIME    >= p_threshold_seconds
      AND ID      <> CONNECTION_ID()
      AND USER NOT IN ('rdsadmin','system user','event_scheduler')
      AND COALESCE(INFO,'') NOT LIKE 'SHOW FULL PROCESSLIST%';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id, v_user, v_host, v_db, v_time, v_info;
    IF done THEN LEAVE read_loop; END IF;

    -- Log only, no kill
    INSERT INTO admin.kill_log(thread_id,user,host,db,time_sec,info,killed_by)
    VALUES (v_id, v_user, v_host, v_db, v_time, LEFT(v_info, 1024),
            CONCAT(CURRENT_USER(), ' (log-only)'));
  END LOOP;

  CLOSE cur;
END//

DELIMITER ;

DELIMITER //

DROP PROCEDURE IF EXISTS admin.scan_long_queries_log_only//

CREATE PROCEDURE admin.scan_long_queries_log_only(IN p_threshold_seconds INT)

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE v_id BIGINT;

DECLARE v_user VARCHAR(128);

DECLARE v_host VARCHAR(255);

DECLARE v_db VARCHAR(128);

DECLARE v_time INT;

DECLARE v_info LONGTEXT;

DECLARE cur CURSOR FOR

SELECT ID, USER, HOST, DB, TIME, INFO

FROM INFORMATION_SCHEMA.PROCESSLIST

WHERE COMMAND = 'Query'

AND TIME >= p_threshold_seconds

AND ID <> CONNECTION_ID()

AND USER NOT IN ('rdsadmin','system user','event_scheduler')

AND COALESCE(INFO,'') NOT LIKE 'SHOW FULL PROCESSLIST%';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;

read_loop: LOOP

FETCH cur INTO v_id, v_user, v_host, v_db, v_time, v_info;

IF done THEN LEAVE read_loop; END IF;

-- Log only, no kill

INSERT INTO admin.kill_log(thread_id,user,host,db,time_sec,info,killed_by)

VALUES (v_id, v_user, v_host, v_db, v_time, LEFT(v_info, 1024),

CONCAT(CURRENT_USER(), ' (log-only)'));

END LOOP;

CLOSE cur;

END//

DELIMITER ;

You can call it manually:

CALL admin.scan_long_queries_log_only(120);

1 2	CALL admin.scan_long_queries_log_only(120);

Check the kill_log table to see if anything was captured. Optionally, you can schedule this procedure as a recurring event for a short period of time to validate it runs automatically, then drop the event.

Step 4. Create the Killer Procedure

Now that you’ve confirmed the smoke test works, it’s time to add the actual enforcement. The killer procedure is almost identical, except it issues a KILL QUERY for each offending thread.

DELIMITER //

DROP PROCEDURE IF EXISTS admin.kill_long_queries//
CREATE PROCEDURE admin.kill_long_queries(IN p_threshold_seconds INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_id   BIGINT;
  DECLARE v_user VARCHAR(128);
  DECLARE v_host VARCHAR(255);
  DECLARE v_db   VARCHAR(128);
  DECLARE v_time INT;
  DECLARE v_info LONGTEXT;

  DECLARE cur CURSOR FOR
    SELECT ID, USER, HOST, DB, TIME, INFO
    FROM INFORMATION_SCHEMA.PROCESSLIST
    WHERE COMMAND = 'Query'
      AND TIME    >= p_threshold_seconds
      AND ID      <> CONNECTION_ID()
      AND USER NOT IN ('rdsadmin','system user','event_scheduler')
      AND COALESCE(INFO,'') NOT LIKE 'SHOW FULL PROCESSLIST%';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id, v_user, v_host, v_db, v_time, v_info;
    IF done THEN LEAVE read_loop; END IF;

    -- Log
    INSERT INTO admin.kill_log(thread_id,user,host,db,time_sec,info,killed_by)
    VALUES (v_id, v_user, v_host, v_db, v_time, LEFT(v_info, 1024), CURRENT_USER());

    -- Kill the query
    SET @k := CONCAT('KILL QUERY ', v_id);
    PREPARE stmt FROM @k;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;
END//

DELIMITER ;

DELIMITER //

DROP PROCEDURE IF EXISTS admin.kill_long_queries//

CREATE PROCEDURE admin.kill_long_queries(IN p_threshold_seconds INT)

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE v_id BIGINT;

DECLARE v_user VARCHAR(128);

DECLARE v_host VARCHAR(255);

DECLARE v_db VARCHAR(128);

DECLARE v_time INT;

DECLARE v_info LONGTEXT;

DECLARE cur CURSOR FOR

SELECT ID, USER, HOST, DB, TIME, INFO

FROM INFORMATION_SCHEMA.PROCESSLIST

WHERE COMMAND = 'Query'

AND TIME >= p_threshold_seconds

AND ID <> CONNECTION_ID()

AND USER NOT IN ('rdsadmin','system user','event_scheduler')

AND COALESCE(INFO,'') NOT LIKE 'SHOW FULL PROCESSLIST%';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;

read_loop: LOOP

FETCH cur INTO v_id, v_user, v_host, v_db, v_time, v_info;

IF done THEN LEAVE read_loop; END IF;

-- Log

INSERT INTO admin.kill_log(thread_id,user,host,db,time_sec,info,killed_by)

VALUES (v_id, v_user, v_host, v_db, v_time, LEFT(v_info, 1024), CURRENT_USER());

-- Kill the query

SET @k := CONCAT('KILL QUERY ', v_id);

PREPARE stmt FROM @k;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END LOOP;

CLOSE cur;

END//

DELIMITER ;

Step 5. Schedule the Killer

With the procedure in place, you can now schedule it to run automatically every 30 seconds:

CREATE EVENT IF NOT EXISTS admin.kill_long_queries_evt
ON SCHEDULE EVERY 30 SECOND
DO CALL admin.kill_long_queries(120);

CREATE EVENT IF NOT EXISTS admin.kill_long_queries_evt

ON SCHEDULE EVERY 30 SECOND

DO CALL admin.kill_long_queries(120);

This setup means:

Every 30 seconds, the procedure runs.
Any query running longer than 120 seconds is killed.
A log entry is created in kill_log.

Step 6. Operations and Maintenance

Adjust the Frequency

You can change how often the killer runs by altering the event:

Run every 1 minute:

ALTER EVENT admin.kill_long_queries_evt ON SCHEDULE EVERY 1 MINUTE DO CALL admin.kill_long_queries(120);

1
2
3
4

ALTER EVENT admin.kill_long_queries_evt
ON SCHEDULE EVERY 1 MINUTE
DO CALL admin.kill_long_queries(120);
Run every 5 minutes:

ALTER EVENT admin.kill_long_queries_evt ON SCHEDULE EVERY 5 MINUTE DO CALL admin.kill_long_queries(120);

1
2
3
4

ALTER EVENT admin.kill_long_queries_evt
ON SCHEDULE EVERY 5 MINUTE
DO CALL admin.kill_long_queries(120);

The minimum interval is 1 second, but in most environments, every 30–120 seconds is sufficient.

Exempt Specific Users

If you have ETL jobs or reporting queries that are expected to run long, you can exclude their usernames in the procedure’s cursor query:

AND USER NOT IN ('rdsadmin','system user','event_scheduler','etl_user','reporting_user')

1 2	AND USER NOT IN ('rdsadmin','system user','event_scheduler','etl_user','reporting_user')

Log Retention

Over time, the kill_log table will grow. Set up a daily purge event:

CREATE EVENT IF NOT EXISTS admin.kill_log_purge_evt
ON SCHEDULE EVERY 1 DAY
DO DELETE FROM admin.kill_log WHERE ts < NOW() - INTERVAL 30 DAY;

CREATE EVENT IF NOT EXISTS admin.kill_log_purge_evt

ON SCHEDULE EVERY 1 DAY

DO DELETE FROM admin.kill_log WHERE ts < NOW() - INTERVAL 30 DAY;

Maintenance Windows

You can temporarily disable or re-enable the killer:

ALTER EVENT admin.kill_long_queries_evt DISABLE;
ALTER EVENT admin.kill_long_queries_evt ENABLE;

ALTER EVENT admin.kill_long_queries_evt DISABLE;

ALTER EVENT admin.kill_long_queries_evt ENABLE;

Cleanup

To remove everything:

DROP EVENT IF EXISTS admin.kill_long_queries_evt;
DROP EVENT IF EXISTS admin.kill_log_purge_evt;
DROP PROCEDURE IF EXISTS admin.kill_long_queries;
DROP PROCEDURE IF EXISTS admin.scan_long_queries_log_only;
DROP TABLE IF EXISTS admin.kill_log;
DROP SCHEMA IF EXISTS admin;

DROP EVENT IF EXISTS admin.kill_long_queries_evt;

DROP EVENT IF EXISTS admin.kill_log_purge_evt;

DROP PROCEDURE IF EXISTS admin.kill_long_queries;

DROP PROCEDURE IF EXISTS admin.scan_long_queries_log_only;

DROP TABLE IF EXISTS admin.kill_log;

DROP SCHEMA IF EXISTS admin;

Conclusion

Aurora Serverless v2 doesn’t give you shell access, but with MySQL’s event scheduler you can still build lightweight, reliable jobs to manage runaway queries.

By combining a logging table, a smoke test, a killer procedure, and scheduled events, you now have a way to: