Kill the long running query with events

I have a lot of clients where I use a variation of the event below to auto kill long running queries. It generally works quite well but when a system gets over loaded it has trouble keeping up. Sometimes I see systems running 4,000 queries and this event will be killing 100 queries per second but it isn't able to kill them fast enough. The queries will end of running 2~3 minutes before they finally get killed. This event is suppose to kill them when they get over 1 minute.

Having people manually kill long running queries in MySQL is typically not feasible. I once had a client where their NOC team would kill every long running SELECT statement manually. That company could have installed this event and have it only kill SELECT statements.

In general long running SELECT queries should not be allowed on a master-primary MySQL server. Long running SELECT queries should be reserved for replica read-only servers. You may also want to terminate any long running query even if it is an update. When performing migrations that do alters or you know that the query will run longer than one minute, you should either disable the event or modify the event to not kill queries under the user name you use for deployments.

Referenced site:
http://www.markleith.co.uk/2011/05/31/finding-and-killing-long-running-innodb-transactions-with-events/

Here is a copy of the event from the above site:

CREATE DATABASE IF NOT EXISTS myadmin;

USE myadmin;

DROP TABLE IF EXISTS kill_long_transactions_log;

CREATE TABLE kill_long_transactions_log (

  id INT AUTO_INCREMENT PRIMARY KEY,

  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  thd BIGINT,

  user VARCHAR(16),

  host VARCHAR(64),

  statement TEXT,

  KEY (ts, user),

  KEY (user),

  KEY (host)

) ENGINE = InnoDB;

DELIMITER $$

DROP EVENT IF EXISTS kill_long_transactions$$

CREATE EVENT kill_long_transactions

ON SCHEDULE EVERY 10 SECOND

DO

  BEGIN

      DECLARE max_transaction_time INT DEFAULT 10;

      DECLARE done INT DEFAULT 0;

      DECLARE killed_id BIGINT;

      DECLARE killed_user VARCHAR(16);

      DECLARE killed_host VARCHAR(64);

      DECLARE kill_stmt VARCHAR(20);

      DECLARE running_stmt TEXT;

      DECLARE long_transactions CURSOR FOR

       SELECT CONCAT('KILL ', trx.trx_mysql_thread_id) kill_statement,

              trx.trx_mysql_thread_id thd_id,

              ps.user,

              ps.host,

              trx.trx_query

         FROM INFORMATION_SCHEMA.INNODB_TRX trx

         JOIN INFORMATION_SCHEMA.PROCESSLIST ps ON trx.trx_mysql_thread_id = ps.id

        WHERE (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(trx.trx_started)) > max_transaction_time

          AND user != 'system_user';

      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

     

      OPEN long_transactions;

       

      kill_loop: LOOP

        FETCH long_transactions INTO

          kill_stmt, killed_id, killed_user, killed_host, running_stmt;

     

        IF done THEN

          LEAVE kill_loop;

        END IF;

         

        SET @kill := kill_stmt;

         

        PREPARE stmt FROM @kill;

        EXECUTE stmt;

        DEALLOCATE PREPARE stmt;

         

        INSERT INTO kill_long_transactions_log (thd, user, host, statement)

        VALUES (killed_id, killed_user, killed_host, running_stmt);

         

      END LOOP;

      CLOSE long_transactions;

       

      DELETE FROM kill_long_transactions_log

       WHERE ts < NOW() - INTERVAL 7 DAY;

  END$$

DELIMITER ;

Here is another way this can be done:

http://datacharmer.blogspot.com/2008/10/using-event-scheduler-to-purge-process.html