#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0

    Mysql waits for 930 seconds before responding


    Hi,

    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)
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    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?
    Code:
        $dbh = new PDO($dsn, $user, $password);
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
    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.
    Ref: http://www.mysqlperformanceblog.com/...-installation/

    3. Focus on the PHPPDO -> MySQL connection and see what timeouts PDO has.

    4. Check the MySQL variables with:
    Code:
    mysql> SHOW VARIABLES like '%time%';
    And see if you can see any timeout variable with a value close to your 930 seconds.
    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.
    /Stefan
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0
    Cheers, I will try your suggestions and get back with them.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0
    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.

IMN logo majestic logo threadwatch logo seochat tools logo