MySQL wait_timeout setting

We were having issues with mysql threads where they would be in sleep mode and wouldn’t die off for long time. At the same time we started having issues with our servers where the load will spike and eventually server will come to halt unless we killed all the apache processes and restarted apache (which seems to be the hung application). We traced it back eventually and noticed that the time when server hung was when it burned through all the ram and was using up all the swap also. So we started to work backwards and tried to resolve one thing at a time. We started with MySQL. We put in wait_timeout = 30 in to my.cnf and restarted mysql. Than I closely watched the server for few hours and noticed that we didn’t have any more of those sleep connections. GREAT! A work around until we get to bottom of whats causing this. That was on Friday. Sat we started noticing different problem. Problem worsened and we started to look into what might’ve caused it and found out that we had a script which was pulling row at a time, processing it, and deleting the row. Except, it was never getting to delete the row due to timeout would kick in and close the connection. We found this out when we watched error logs and saw: Mysql has gone away message.

We took out the wait timeout and everything seems to started to work fine. Did anybody ever notice this behavior where you would loose connection to the mysql server due to timeout? The script which processes line by line and deletes line by line takes fraction of second to process that particular line. Does wait timeout starts counting from the starting of the connection? Does it mean that wait timeout is actually a max connection time limit? Suggestions/comments?

Edit 5/31/09: Friend of mine was getting this error: database error: Lost connection to MySQL server during query
he got around it by adjusting wait_timeout setting.

7 thoughts on “MySQL wait_timeout setting

  1. maghiel

    When a mysql process is in SLEEP state, it could indicate the process is waiting for another, long query to finish.

    In your case, the query selecting rows is probably taking more than 30 seconds, your timeout setting kills the process, so the delete query is not executed anymore.

  2. Shopping Details

    We got the “too many connection” error in our site. we were approached to our server peoples. they did some of the correction in mysql system variable (wait_timeout set to 28800, interactive_timeout set to 28800) and restart the mysql server. Our problem solved. I think this will help you sort your problem.

  3. The Mikeness

    The Microsoft way to solve this problem is to increase the max connections.

    Nah but seriously tho, some mysql_close() calls would be in order, if you are having problems i doubt its one script thats making tons of connections its probably just other connections are being made in other highly used scripts and are not getting closed after. If youre using PHP for example and objects, you can create a destructor like __destruct(){$this->link->close();} to close it at the end of execution because when the object is destroyed it closes the connection then.

  4. Pingback: Web Services — Troubleshooting Sleeping Processes in MySQL

  5. prabu

    Hi..
    If you set the property value wait_timeout as 30 it seems your web application ll going to met MySQLNonTransientConnectionException.

    In my web application met this problem while my setup wait_timeout as 30. When it sets to default variable 8 hours which is 28800 seconds its working fine.

    i m using c3p0 connection pooling with hibernet in JAVA. Here what happends means the connections goign to kill every 30 seconds which is sleeping state but it live.

    So its major problem keep aware with this time out variable on mysql server.

    Hope this information usefull for all. :)

  6. Chris DePuy

    At a dos prompt, type:
    C:\>mysql –verbose –help

    Then look for
    …i-am-a-dummy FALSE
    connect_timeout 0
    max_allowed_packet 16777216

    Then, at mysql prompt

    mysql> set GLOBAL connect_timeout=259200;
    Query OK, 0 rows affected (0.03 sec)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>