mysqli php random connect error

Question

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)

Alkene

Impossible to tell from this. This is a network or server issue, not a problem with your script. Maybe there's a rate limit on the firewall? – Potent

I think I faced this before, I agree with @Potent it's definitely a server issue. What server are you on and are you able to access the mysql through that server? – Uboat

Have you seen #10883531 – Catercousin

And if not, try google.com/… – Catercousin

all servers running on aws linux. Thx for the wait_timeout info. when using sql workbench to see the connected threads its around 125 but max connections set is 750. have checked the limit on ports /proc/sys/net/ipv4/ip_local_port_range its been set to 32768 61000 it looks good – Alkene

How many clients are there? Maybe you should throttle that limit. – Anecdotic

Max connections is 151 by default; did you restart the MySQL server after changing config to 750? Some other questions: "All servers on AWS": are you using RDS or hosting MySQL on EC2? If on EC2 is mySQL on a different EC2 instance? (If not, use "localhost", is so, use persistent connections.) – Aphanite

So... you're connecting and disconnecting to MySQL in your php code, right? If the answer is yes - don't do that. Use the power of FCGI, make persistent connections work for you. More than a few connections to MySQL is just pointless in real world scenario, especially if you have a single server to do all this. – Merrileemerrili

Can you show the connection script? – Dateless

Answer 1

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

• tcp_tw_reuse (Boolean; default: disabled; since Linux 2.4.19/2.6) Allow to reuse TIME_WAIT sockets for new connections when it is safe from protocol viewpoint. It should not be changed without advice/request of technical experts.

  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”.

• tcp_tw_recycle (Boolean; default: disabled; since Linux 2.4) Enable fast recycling of TIME_WAIT sockets. Enabling this option is not recommended since this causes problems when working with NAT (Network Address Translation).

  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.

• tcp_max_tw_buckets (integer; default: see below; since Linux 2.4) The maximum number of sockets in TIME_WAIT state allowed in the system. This limit exists only to prevent simple denial- of-service attacks. The default value of NR_FILE*2 is adjusted depending on the memory in the system. If this number is exceeded, the socket is closed and a warning is printed.

  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_buckets is 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 with tcp_tw_recycle, one of them would simply disappear from netstat output. 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.

PS: There more possible solutions to your problem, read the full article for detailed description of the problem.

Curvy

Thanks for your time and info. when I do a netstat able to find the 25044 are in TIME_WAIT on mysql port 3306 and total TIME_WAIT count on all ports is 30044 . When I checked the values of /proc/sys/net/ipv4/tcp_max_tw_buckets it has a values of 32768. Is it recycle setting would do or do I need to reduce the tcp_fin_timeout? – Alkene

@Alkene i think the tcp_tw_reuse option is best. – Curvy

Thanks will try tcp_tw_reuse option – Alkene

Answer 2

Update 1:

tcp_tw_reuse looks better solution. Here is described why:

tcp_tw_reuse vs tcp_tw_recycle : Which to use (or both)?

Original answer:

mysql error (99) means that you are running out of the tcp ports.

Enabling tcp recycle should fix it.

echo 1 >/proc/sys/net/ipv4/tcp_tw_recycle 

Credits.

Caliginous

Thanks for your time and info. when I do a netstat able to find the 25044 are in TIME_WAIT on mysql port 3306 and total TIME_WAIT count on all ports is 30044 . When I checked the values of /proc/sys/net/ipv4/tcp_max_tw_buckets it has a values of 32768. Is it recycle setting would do or do I need to reduce the tcp_fin_timeout? – Alkene

tcp_tw_reuse. I've updated the answer and add a reference, that describe which one is better solution. – Caliginous

Thanks will try tcp_tw_reuse. – Alkene