Thursday, July 19, 2007

mysql wait_timeout

There is an issue with mysql if you are using mysql_pconnect.

What happens is that if you have configured http or apache for 150 max connections and mysql for 100 max connections and you are using mysql_pconnect then each http thread (supposing you are running php scripts only) creates a connection with mysql. And maintains the connections. So what happens, the first 100 http take up the 100 mysql connections and then even if the query is not over, dont free them. And the 101th http gives an error Mysql:Too many connections.

The way to handle this is

Dont use mysql_pconnect for connecting with the mysql database from php scripts. Use mysql_connect.

I have come across cases when even when mysql_pconnect is not being used, still the mysql connections are used up and though mysql does not show any query in its processlist, still all the threads of mysql are still live and are in sleep state. Now that's strange.

I had a server which was performing perfectly fine. And suddenly one day i start getting "too many connections" in the server. And on checking the processlist i found that all threads of mysql are in sleep state. That's strange. Suddenly one day the server starts misbehaving. And even on restarting the server, it does not behave properly. As soon as the server is restarted, the number of connections get maxed out.

There is one variable in mysql which can save the day. The "wait_timeout". By default it is set to 28840 (or something like that). And it defines the number of seconds a mysql thread waits in idle state before mysql preempts and kills it. So all you have got to do is to change this variable to something like 30 or so. For that you will have to run a "show processlist" and check the maximum time of a thread in sleep state and what time ought to suit your environment.

simply run

set global wait_timeout = 30

in mysql prompt and your task is done. But better edit the my.cnf and save it there, so that it is incorporated when mysql is restarted. With this done, no thread of mysql will remain active if it is idle for over 30 seconds. And a query running for over 30 seconds would be allowed to execute since that is not an idle thread.


tape backup said...

Great, thanks for the post.

amernath said...

Useful article. Thanks.

DarĂ­o said...

Three years later...

Interesting, I was looking for info about pconnecting and timeout and I think I found what I was looking for.

Thank you :P

Anonymous said...

described exactly the symptoms and that cause.

thanks for the post making your insight available to all

Anonymous said...

great info in simple wrapper

Anonymous said...

It´s what I´m looking for, thanks