Very large number open connections from web to DB server

Question

I run 2 servers, 1 web (nginx/php), 1 database (mysql).

Nginx has about 1500 active processes per second, and mysql status shows about 15 currently option connections on average.

Now today i started running: netstat -npt | awk '{print $5}' | grep -v "ffff\|127\.0\.0\.1" | awk -F ':' '{print $1}' | sort -n | uniq -c | sort -n

This showed that there were over 7000 active connections from my webserver to my database server IP. This seems kind of extreme. I do not use persistent connections in PHP to connect to Mysql.

Any idea why there are so many open connections?

Solution

Though this is getting a bit stackoverflow'y, here goes:

Probably because you don't close your connections in the code. If so, I would recommend you switch to mysql_pconnect(), or just add mysql_close() to the end of all requested php-pages

If all the connections to the mysql server is in state: TIME_WAIT, try lowering the wait_timeout variable in your mysqld configuration. Check out the MySQL documentation for more info

UPDATE: As ChristopherEvans pointed out, you can connect directly to the mysql socket instead of using IP endpoints, to avoid running out of unused ports on the local interface

Comments

[dmourati]

You can replace your netstat script with mtop and get more info: mtop.sourceforge.net

[Mr.Boon]

mysql_close() should not be needed anymore with normal mysql_connect(). I have tried it with that at the end of my scripts, but it makes no difference.

[Mr.Boon]

Yes, I 99% is in TIME_WAIT state. Is that an issue to have so many open?