I am receiving the below error randomly from the php backend jobs and php web page logs. Have a app server which runs php backend jobs and php webservers. Both connect to the same database server. Using php mysqli object oriented library for connecting to the database. Have set max connections to 750 in my.cnf. Dont see that much connections is reached.
PHP Warning: mysqli::mysqli(): (HY000/2003): Can't connect to MySQL server on '77.777.120.81' (99) in /usr/local/dev/classes/Admin.php on line 15
Failed to connect to MySQL: Can't connect to MySQL server on '77.777.120.81' (99)
As described excellently in this Percona Database Performance Blog article, your problem is that your application cannot open another connection to MySQL server. You are running out of local TCP ports. As a solution i would propose to Tweak TCP parameter settings
It is possible to force the kernel to reuse a connection hanging in TIME_WAIT state by setting /proc/sys/net/ipv4/tcp_tw_reuse to 1. What happens in practice is that you’ll keep seeing the closed connections hanging in TIME_WAIT until either they expire or a new connection is requested. In the later case, the connection will be “relived”.
When you enable /proc/sys/net/ipv4/tcp_tw_recycle closed connections will not show under TIME_WAIT anymore – they disappear from netstat altogether. But as soon as you open a new connection (within the 60 seconds mark) it will recycle one of those. But everyone writing about this alternative seems to advise against it’s use. Bottom line is: it’s preferable to reuse a connection than to recycle it.
This parameter rules how many connections can remain in TIME_WAIT state concurrently: the kernel will simply kill connections hanging in such state above that number. For example, in a scenario where the server has a TCP port range composed of only 6 ports, if
/proc/sys/net/ipv4/tcp_max_tw_bucketsis set to 5, then open 6 concurrent connections with MySQL and then immediately close all 6 you would find only 5 of them hanging in the TIME_WAIT state – as withtcp_tw_recycle, one of them would simply disappear fromnetstatoutput. This situation allows to immediately open a new connection without needing to wait for a minute*.
- When it comes to connecting to database servers, many applications chose to open a new connection for a single request only, closing it right after the request is processed. Even though the connection is closed by the client (application) the local port it was using is not immediately released by the OS to be reused by another connection: it will sit in a TIME_WAIT state for (usually) 60 seconds – this value cannot be easily changed as it is hard coded in the kernel.
However, a second connection won’t be able to open until one of the other 5 connections in TIME_WAIT expire and release the local port it was using. The secret here, then, is to find a compromise between the number of available network ports and the number of connections we allow to remain in TIME_WAIT state. The default value of this setting is 65536, which means by default the system allows all possible connections to go over the TIME_WAIT state when closed.