The Geek Diary

MySQL: How to kill a Long Running Query using max_execution_time

by

In this post, we will learn about what options are available if you need to automatically kill long-running queries.

MySQL 5.7 and Later For SELECT Statement

Starting from MySQL 5.7.4 there is support for automatic timeouts of read-only SELECT statements (the following discussion assumes 5.7.8 or later as the feature had changes to the option/hint name and syntax).

There are two ways to set an upper limit of how long a read-only SELECT statement is allowed to execute:

  • the max_execution_time option
  • the MAX_EXECUTION_TIME() optimizer hint for SELECT statements

The following applies:

  • Only read-only SELECT statements are affected.
  • Only the top level (i.e. not subqueries) are affected.
  • SELECT statements inside stored programs are not affected (the MAX_EXECUTION_TIME() hint is not supported in stored programs).

These two options will be discussed in the following.

The max_statement_time Option

The max_execution_time option specifies the timeout for read-only SELECT statements if the MAX_EXECUTION_TIME() optimizer hint is not added. The value is in milliseconds.

The option exists both at the GLOBAL and SESSION level and can be changed dynamically. The GLOBAL value is used as the default for the SESSION value. A value of 0 means that no timeout is in effect.

mysql> connect
Connection id:    5
Current database: *** NONE ***

mysql> SELECT @@global.max_statement_time, @@session.max_statement_time;
+-----------------------------+------------------------------+
| @@global.max_statement_time | @@session.max_statement_time |
+-----------------------------+------------------------------+
|                        1000 |                         1000 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)
mysql> SET SESSION max_statement_time = 2000; -- 2 seconds
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.max_statement_time, @@session.max_statement_time;
+-----------------------------+------------------------------+
| @@global.max_statement_time | @@session.max_statement_time |
+-----------------------------+------------------------------+
|                        1000 |                         2000 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)

A query taking 1.5 seconds succeeds:

mysql> SELECT SLEEP(1.5);
+------------+
| SLEEP(1.5) |
+------------+
|          0 |
+------------+
1 row in set (1.51 sec)

A query taking more than 2 seconds fails:

mysql> SELECT SLEEP(5);
+----------+
| SLEEP(5) |
+----------+
|        1 |
+----------+
1 row in set (2.02 sec)

Only SELECT statements are affected:

mysql> DO SLEEP(5);
Query OK, 0 rows affected (5.00 sec)

The MAX_STATEMENT_TIME Clause for SELECT Statements

The MAX_EXECUTION_TIME() optimize hint can be used to change the timeout for a specific query. For example if max_execution_time is set to 1 second, but you know a given query takes 5 seconds, then you can change the timeout:

mysql> SELECT @@global.max_execution_time, @@session.max_execution_time;
+-----------------------------+------------------------------+
| @@global.max_execution_time | @@session.max_execution_time |
+-----------------------------+------------------------------+
|                        1000 |                         1000 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT /*+ MAX_EXECUTION_TIME(6000) */ /* may take up to 5+ seconds - don't kill */ SLEEP(5);
+----------+
| SLEEP(5) |
+----------+
|        0 |
+----------+
1 row in set (5.00 sec)

or you can use it to lower the timeout:

mysql> SELECT /*+ MAX_EXECUTION_TIME(500) */ /* may take up to 5+ seconds - don't kill */ SLEEP(5);
+----------+
| SLEEP(5) |
+----------+
|        1 |
+----------+
1 row in set (0.50 sec

All Versions and All Statement Types

Important: If you are changing data in non-transactional tables, such as MyISAM tables, killing the query will not roll back the transaction. This means your data will be in an unknown state where the effects of the statement is partly applied. If you use replication, you will need to rebuild the replicas. In general, for non-transactional engines, it is better to leave the statement running.

Before MySQL 5.7.4 there is no built-in way to automatically kill long running queries. Instead, one of the two following solutions can be used:

  • Write a script that periodically checks whether there are any long running queries and kills them if needed.
  • Write a stored procedure that can be executed using the event scheduler. This option is only available in MySQL 5.1 and later as earlier versions did not have support for events.

Both works in very similar ways. The first has the advantage that you can choose the programming language of your choice whereas the stored procedure/event solution keeps everything inside the database.

Note: InnoDB transactions that have modified a large number of rows will take a while to roll back. As a rule of thumb, you should expect the rollback to take 10 times as long as making the modifications. So you want to consider the number of undo entries for a transaction before killing it.

To implement the check you will need to use the process list. In MySQL 5.1 and later, you can use the information_schema.PROCESSLIST table which makes it possible to get the process list from a standard SELECT query which is necessary if the solution is implemented inside a stored procedure. In MySQL 5.6 and later the performance_schema.threads table can be used (and is in general preferred as it requires fewer locks than SHOW PROCESSLIST or information_schema.PROCESSLIST).

For InnoDB, you may be interested in long running transactions even as well. These can be monitored through the INNODB_TRX in the Information Schema. The INNODB_TRX table includes when the transaction was started.

You may also want to take into consideration whether any other queries are waiting for locks held by the long running query. For InnoDB tables, this can easily be checked using the INNODB_LOCK_WAITS and INNODB_TRX tables in the Information Schema. The InnoDB Information Schema tables are available starting from MySQL 5.1 with the InnoDB Plugin and MySQL 5.5 and later.

While it is in general not good to have long-running queries and transactions as they may block other connections and increase the usage of resources, some queries will inevitably take a long time, for example, table rebuilds of large tables, bulk imports, etc. So you may want to consider a way to flag queries that you do not want to terminate; this could for example be through a comment at the beginning of the query.

Note: Make sure you test your solution. The above references are just examples and are meant as suggestions how to get started and are should not be considered production-ready solutions.

You May Also Like