0

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?

1

2 Answers 2

1

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

5
  • 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, 2012 at 18:24
  • Just updated the answer Jan 20, 2012 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. Jan 20, 2012 at 18:47
  • Yes, I 99% is in TIME_WAIT state. Is that an issue to have so many open?
    – Mr.Boon
    Jan 20, 2012 at 18:51
  • 1
    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 Jan 20, 2012 at 19:22
0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .