4

I'm trying to test the wait_timeout MySQL setting which seems to be ignored.

PHP script:

<?php
function microtime_float()
{
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}

$sql = mysqli_connect('localhost','root','root','mysql');
$query = "SHOW VARIABLES WHERE Variable_name='wait_timeout';";
$result = $sql->query($query) or die($query.'<br />'.$sql->error);
$row = $result->fetch_object();
echo "wait_timeout = " . $row->Value . "<br/>\n";

$time_start = microtime_float();
$query = "SELECT SLEEP(2) FROM mysql.user;";
$sql->query($query) or die($query.'<br />'.$sql->error);
$time_end = microtime_float();
$time = $time_end - $time_start;
echo "Query completed in $time seconds<br/>\n";
echo "You got the page";

Script output:

wait_timeout = 2
Query completed in 8.0005459785461 seconds
You got the page

My configuration

mariadb-server-5.3.5
php5.3.6

What do I need to do in order to force MySQL to time out queries after a certain amount of time?

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
Max
  • 12,794
  • 30
  • 90
  • 142
  • Both `wait_timeout` and `interactive_timeout` is the time of inactivity before the connection is dropped. So, the connection must be idle (not running a query) before it will be dropped. MySQL SLEEP() does not count, since you're running a query. You'll have to manually kill long running queries. You can script this. – Marcus Adams Apr 05 '12 at 11:16
  • I think you're trying to use wait_timeout incorrectly. wait_timeout and interactive_timeout aren't for query duration if I remember correctly. They're for inactive connections not slow queries or the SLEEP containing query, the connection is still fine and active... – Garry Welding Apr 05 '12 at 11:17

1 Answers1

5

Both wait_timeout and interactive_timeout is the time of inactivity before the connection is dropped. So, the connection must be idle (not running a query) before it will be dropped. MySQL SLEEP() does not count, since you're running a query.

You'll have to manually kill long running queries (there's no setting to have MySQL do it for you). You can script this. Use SHOW PROCESSLIST (or external tools like Innotop) and KILL.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • Is there any setting that would enable MySQL to stop the execution of queries after a given predefined amount of time? – Max Apr 10 '12 at 05:40
  • No, there is no setting that offers that functionality. As I described in my answer, you can script it using `SHOW PROCESSLIST` and `KILL`. There may be some third party scripts or programs that can do this. – Marcus Adams Apr 10 '12 at 14:46
  • 1
    @user359650, [mk-kill](http://www.maatkit.org/doc/mk-kill.html) is a command-line tool that you can specify criteria for which process to kill (e.g. busy-time). For a complete tool kit that includes mk-kill, see the [Percona Toolkit](http://www.percona.com/software/percona-toolkit/). – Marcus Adams Apr 10 '12 at 14:55
  • I'm very surprised that `MySQL` doesn't handle the termination of queries based on predefined timeouts natively. `mk-kill` seems indeed like what I need. It's now part of the `percona toolkit` (http://www.percona.com/software/percona-toolkit/) – Max Apr 10 '12 at 15:44
  • 1
    Yes, wait_timeout is client inactivity timeout. To my knowlegde, only .NET and Java clients have client-side timeouts, which would have the effect of killing long running query whenever statement takes too long. As mentioned here, scripting "SHOW PROCESSLIST" + "KILL" would work. If you have interest to use something that does not come from the official MySQL nor from forks, Twitter has the implementation on the server-side timeout in its GIT branch https://github.com/twitter/mysql/wiki/Statement-Timeout – Vladislav Vaintroub Apr 13 '12 at 01:18