Too many TIME_WAIT connections

views:

2003

answers:

Too many TIME_WAIT connections

We have a fairly busy website (1 million page views/day) using Apache mod proxy that keeps getting overloaded with connections (>1,000) in the TIME_WAIT state. The connections are to port 3306 (mysql), but mysql only shows a few connections (show process list) and is performing fine. We have tried changing a bunch of things (keep alive on/off), but nothing seems to help. All other system resources are within reasonable range.

I've searched around, which seems to indicate changing the tcp_time_wait_interval. But that seems a bit drastic. I've worked on busy website before, but never had this problem.

Any suggestions?

We had a similar problem, where our web servers all froze up because our php was making connections to a mysql server that was set up to do reverse host lookups on incoming connections.

When things were slow it worked fine, but under load the responstimes shot through the roof and all the apache servers got stuck in time_wait.

The way we figured the problem out was through using xdebug to create profiling data on the scripts under high load, and looking at that. the mysql_connect calls took up 80-90% of the execution time.

xkcd150 2009-05-02 00:23:58

But wouldn't the connections all be coming from the same server, your web server or your application server? I would think mysql would just hit it's host cache after the first connection.

Brent Baisley 2009-05-02 00:55:49

they did, and it didn't.

xkcd150 2009-05-02 01:12:01

+2 A:

Each time_wait connection is a connection that has been closed.

You're probably connecting to mysql, issuing a query, then disconnecting. Repeat for each query on the page. Consider using a connection pooling tool, or at very least, a global variable that holds on to your database connection. If you use a global, you'll have to close the connection at the end of the page. Hopefully you have someplace common you can put that, like a footer include.

As a bonus, you should get a faster page load. MySQL is quick to connect, but not having to re-connect is even faster.

Craig Lewis 2009-05-02 01:01:01

We are using PHP, so you can't really do connection pooling since the process ends once the page is delivered.

Brent Baisley 2009-05-02 02:18:09

You can still reuse your connection within the page by assigning your dbconnection to a global variable. Instead of recreating every time. You can also use something like http://httpd.apache.org/docs/2.2/mod/mod_dbd.html to do the pooling outside of the php process.

Trey 2009-05-02 16:45:41

Hi, If your client applications are using JDBC, you might be hitting this bug: http://bugs.mysql.com/bug.php?id=56979 I believe that php has the same problem Cheers, Gilles.

Gilles Rayrat 2010-09-24 07:21:05

ansaurus

tags:

views:

answers:

Too many TIME_WAIT connections

related questions