I run a rather busy site, and during peek hours I see over 10.000 open connections to my database server on my webserver when a run a netstat command. 99% of the connections are in the TIME_WAIT
state.
I learned about this mysql variable: wait_timeout
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_wait_timeout today. Mine is still set at the default 28.800 seconds.
Is lowering this value safe?
Non of my queries usually takes over a second. So it seems silly to keep a connection open for 480 minutes.
I also heard about using mysql_pconnect
instead of mysql_connect
, but i've been reading nothing but horror stories about it, so I think i'll stay away from that.
Best Answer
Lowering the value is pretty trivial without a mysql restart
Let's say you want to lower timeouts to 30 seconds
First, add this to my.cnf
Then, you can do something like this
All DB Connections after this will timeout in 30 seconds
WARNING
Make sure to use explicitly use mysql_close. I do not trust Apache as most developers do. If not, sometimes, there is a race condition where Apache closes a DB Connection but does not inform mysqld and mysqld hold that connection open until it times out. Even worse, you may see TIME_WAITs more often. Choose your timeout values wisely.
UPDATE 2012-11-12 10:10 EDT
CAVEAT
After applying my posted suggestions, create a script called
/root/show_mysql_netstat.sh
with the following lines:When you run this, you should see something like this:
If you still see a lot of mysql
TIME_WAITs
for any given web server, here are two escalation steps to take:ESCALATION #1
Login to the offending web server and restart apache as follows:
If necessary, do this to all the web servers
ESCALATION #2
You can force the OS to kill TIME_WAITs for mysql or any other app with the following:
This will make TIME_WAITs time out in 1 second.
To give credit where credit is due...
TIME_WAIT
comes into existence.