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?

Mr.Boon
  • 1,441
  • 4
  • 24
  • 41

2 Answers2

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

Mathias R. Jessen
  • 24,907
  • 4
  • 62
  • 95
  • 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
  • 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 http://php.net/manual/en/function.mysql-connect.php – becomingwisest Jan 20 '12 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

ckliborn
  • 2,750
  • 4
  • 24
  • 36