Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
You can replace your netstat script with mtop and get more info: mtop.sourceforge.net –  dmourati Jan 20 '12 at 18:48
add comment

2 Answers

up vote 1 down vote accepted

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

share|improve this answer
    
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 Jan 20 '12 at 18:24
    
Just updated the answer –  Mathias R. Jessen Jan 20 '12 at 18:41
    
+1 for checking on TIME_WAIT. Can you group the results so you know how many are for each type, ie ESTABLISHED, TIME_WAIT, etc. –  becomingwisest Jan 20 '12 at 18:47
    
Yes, I 99% is in TIME_WAIT state. Is that an issue to have so many open? –  Mr.Boon Jan 20 '12 at 18:51
    
In theory you can run out of ports to connect from, since they are all connecting to localhost. As Judas says, you can reduce the time wait, or use persistant connections. A third option that may work is to find out what socket mysql listens on ( for example /var/lib/mysql/mysql.sock ) Then update the "host" that mysql_connect uses to be ':/var/lib/mysql/mysql.sock' per php.net/manual/en/function.mysql-connect.php –  becomingwisest Jan 20 '12 at 19:22
add comment

Looks into connection pooling - This way each connection will not need its own process.

http://php.net/manual/en/mysqlnd-ms.pooling.php

Not sure if this would work though since you aren't using persistent connections

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.