Force timeout on a MysqlQuery from PHP

Question

Is there a way to force Mysql from PHP to kill a query if it didn't return within a certain time frame?

I sometimes see expensive queries running for hours (obviously by this time HTTP connection timed out or the user has left). Once enough such queries accumulate it starts to affect the overall performance badly.

Answer 1

Long running queries are a sign of poor design. It is best to inspect the queries in question and how they could be optimised. This would be just ignoring the problem.

If still want this you could use the SHOW PROCESSESLIST command to get all running processes. And then use KILL x to kill a client connection. But for this to work you have to check this from another PHP script since multithreading is not yet possible. Also it is not advisable to give users utilized by PHP grants to mess with the server settings.

Answer 2

Warning: The intended behaviour should now be used with something like upstart.

You want to create DAEMONS for this sort of thing but you could also use cron.
Just have a script looking at set intervals for queries above xyz time and kill them.

// this instruction kills all processes executing for more than 10 seconds
SELECT CONCAT('KILL ',id,';') 
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE state = "executing" AND `time` >= 10

However if queries are running for such a long time... they must be optimized.

On the other hand you may be trying to admin a shared server and there can be some rogue users. On that scenario you should specify in the terms of service that scripts will be monitored and disabled and that is exactly what should be done to such offensive ones.