MySQL - can I limit the maximum time allowed for a query to run?

I'开发者_运维技巧m looking for a way to limit the max running time of a query on mysql server. I figured this could be done through the my.cnf configuration file, but couldn't find anything relevant in the docs. Anyone knows if this could be done? thanks.

Update

As of MySQL 5.7, you can include a MAX_EXECUTION_TIME optimizer hint in your SELECT queries to instruct the server to terminate it after the specified time.

As far as I know, if you want to enforce a server-wide timeout, or if you care about queries besides SELECTs, the original answer is still your only option.

Original answer

There is no way to specify a maximum run time when sending a query to the server to run.

However, it is not uncommon to have a cron job that runs every second on your database server, connecting and doing something like this:

1. SHOW PROCESSLIST
2. Find all connections with a query time larger than your maximum desired time
3. Run KILL [process id] for each of those processes

You could use a query as follows:

SELECT MAX_STATEMENT_TIME=1000 * FROM table;

UPDATE: You should use max_execution_time instead.

SELECT /*+ MAX_EXECUTION_TIME(1000)*/ * FROM table;

MAX_STATEMENT_TIME was renamed to max_execution_time in MySQL 5.7.8. http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time

In the meantime the Twitter team released their changes to MySQL which implements this:

- Reduce unnecessary work through improved server-side statement timeout support. This allows the server to proactively cancel queries that run longer than a millisecond-granularity timeout.

See http://engineering.twitter.com/2012/04/mysql-at-twitter.html and https://github.com/twitter/mysql/wiki/Statement-Timeout

http://mysqlserverteam.com/server-side-select-statement-timeouts/

Interesting upgrade. I will check it:

"MySQL 5.7.4 introduces the ability to set server side execution time limits, specified in milliseconds, for top level read-only SELECT statements".

SET GLOBAL MAX_STATEMENT_TIME=1000;
SET SESSION MAX_STATEMENT_TIME=2000;
SELECT MAX_STATEMENT_TIME=1000 * FROM table;