March 18th, 2013, 07:14 AM
Mysql waits for 930 seconds before responding
we have problem with our my-sql instance. Sometimes it seems to wait for exactly 930 seconds before it returns the (correct) response to a simple (index-based) (prepared) select-query.
The problem is not related to missing indexes or slow queries. Also it is not 1 particular query. And it only happens at seemingly random times, the same query would come back fast the next time it is requested.
What could cause this?
(We are using phpdo to access the database; it could be pdo or the network that causes this but i suspect mysql)
March 18th, 2013, 01:45 PM
1. Have you check the mysql logs?
If there is a problem with MySQL it's usually there.
2. Have you set the PDO attribute to warning and read the logs?
3. Turn of MySQL DNS reverse lookups with skip_name_resolve. Although your timeout is much higher than what you typically see for a problem with faulty DNS setup you should still turn it of for safety sake.
$dbh = new PDO($dsn, $user, $password);
3. Focus on the PHPPDO -> MySQL connection and see what timeouts PDO has.
4. Check the MySQL variables with:
And see if you can see any timeout variable with a value close to your 930 seconds.
mysql> SHOW VARIABLES like '%time%';
But especially look at the one named wait_timeout.
This timeout will kill a connection that hasn't issued a query within that timeout.
Because another theory is that you can get in the situation where MySQL kills the connection due to this timeout while the connection pooling in PHP doesn't really understand that it's gone until it tries to run a query and gets some kind of strange error ( A lot of years back it was quite an issue with some version of mysql/mysqli (I don't remember the exact version) ).
But start with the logs and hopefully you get something there. Because if it is as you say different queries and not an index miss I don't think it's in MySQL itself. Most of these strange long waits are network related in one way or another.
March 18th, 2013, 02:17 PM
Cheers, I will try your suggestions and get back with them.
April 26th, 2013, 02:49 AM
It turned out that we were using pdo-persistent connections and they broke it. We are now running for 2 weeks on non-persistent connections and no more problems.