Anyway to Limit MySQL Query Execution time?

Question

Hi I'm having issues on my server at the minute because some of the MySQL queries are taking a very long time to run, and hogging the resources on the server.

I'm already in the process of optimising all the queries to improve the performance, but as we use a fair amount of 3rd party applications on the server using mysql I was hoping to put in place a safeguard to prevent future issues.

What I need is something I can put in place server wide that will apply to all queries but with the possibility of overriding it on a per query basis for some of the more complex reports that do take some time to run.

I've spent time googling to find a solution but so far no luck,

Thanks for your help

Answer 1

This is a purely php solution that seems to be the simplest solution from what I've managed to find so far.

$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) 
{
  $process_id = $row["Id"];
  if ($row["Time"] > 200 ) 
  {
    $sql="KILL {$process_id}";
    mysql_query($sql);
  }
}

And running this from a CRON script every 60 seconds.

If anyone does find a better solution to this issue please let me know