MySQL lowering wait_timeout value to lower number of open connections

Question

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.

Answer 1

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

[mysqld]
interactive_timeout=30
wait_timeout=30

Then, you can do something like this

mysql -uroot -ppassword -e"SET GLOBAL wait_timeout=30; SET GLOBAL interactive_timeout=30"

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.