How to limit the sql execution time

Question

Some sql is not well written. Sometimes a search costs hours in applications. When a application(maybe a website) submit a query which run long time, I have to restart the mysql. How can I limit a sql query's execution time in the database side?

Answer 1

To auto kill a query in MySQL after a long execution time:

1. Create a stored procedure as:

   DECLARE CURSOR cur1 FOR SELECT ID 
                       FROM INFORMATION_SCHEMA.PROCESSLIST 
                       WHERE COMMAND = 'Query' AND TIME > 120;
   

   then inside curosr's loop do:

   FETCH ID INTO @var_kill_id;
   KILL QUERY @var_kill_id;
   

2. Create EVENT FOR EVERY 5 SECONDS and just CALL the above procedure inside it.

Note: KILL QUERY just kills the query and MySQL connection is not broken. see here.

Answer 2

Also if possible you can try Twitter's mysql fork that suport "max_statement_time" and kills a query exceding it, at a milisecond granularity.

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

Original source.

Answer 3

The only choice is that to open another session, use SHOW PROCESSLIST and then KILL QUERY the one you want to terminate.

You can use the mysqladmin command-line tool to issue these commands.

Answer 4

This may not be the exact solution of resticting SQL execution time, but I guess it will be of help. You will be not required to restart your MySQL server at all.

Try using some MySQL GUI Tool like SQLyog. There you can manually stop executed query,(Check the X button in screen shot) if you think that it is taking longer time.

And moreover you can kill the query process as well by accessing process list Check other two images in screen shot with steps to kill processes...

Hope it helps....