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

Importing tables with deep rows into Aurora - turning off extended-insert

I was having trouble setting up an Aurora instance as a slave of a physical server. I wanted to get  all the data into Aurora in preparation for a failover to Aurora. The problem is I kept getting an error when dumping and restoring all the data:

ERROR 1041 (HY000) at line 29836: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

I got this error when using both MySQL dump and restore and MyDumper and MyLoader. I found the table which was the culprit and it has a structure like this:

CREATE TABLE `my_data` (
  `Token` varchar(50) NOT NULL,
  `Data` mediumtext NOT NULL,
  PRIMARY KEY (`Token`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

In the Data column was a large amount of JSON data.

I had already restored the table structure on the target and just had to truncate the table from the last failed import.

I stopped replication on one of the slaves so that I would have static log position to start the Aurora instances once this was done. First I did a dump and restore of all the structure for all tables. Then dumped and restore data for all the non-problematic tables.

Then did a separate dump and restore just for this table

Dumped the table like this:

mysqldump --defaults-file='my.cnf' -h<my server> --no-create-info --no-create-db --skip-opt --extended-insert=FALSE --quick MyDB my_data > My_DB_my_data.sql

Restore like this:

mysql --defaults-file='my.cnf' -h aurname.cluster-blablabla.rds.amazonaws.com MyDB < My_DB_my_data.sql

By turning off the extended-insert, I was able to get the data to load but it was MUCH slower because it was doing one insert at a time. However, the inserts were very large and this was the only way I was able to get the data to restore.

Can’t create more than max_prepared_stmt_count statements

I kept seeing this error in the logs for a script I wrote which uses prepared statements:

Error Code: 1461
Can’t create more than max_prepared_stmt_count statements

I was a bit perplexed because I had not run into this before. Fortunately, someone else has already blogged about it for me:

https://iammysql.wordpress.com/2012/05/04/cant-create-more-than-max_prepared_stmt_count-statements/

The client for this database server had added new code which was not closing the prepared statements. After I informed the client, he fixed it and the problem went away.