Percona Cluster Failed with Haproxy : Connection Limit Exceeded

Question

We have 3 node PXC cluster setup on amazon ec2. Its been working fine for around 4-5 months. Since last week, every node starting to fail due to MySQL connection limit exceeded error. we increased the max_connections and still no help. We had about 1-2hr downtime since last week.

When a one node failed(Client connection failed) i was logged in to that MySQL server and i was able to do a SHOW FULL PROCESSLIST but it has only 20-30 connections. At the same time, when i tried to connect to the same server via another terminal, I got the connection limit exceeded error message. So i did a netstat -na | grep 3306 | wc -l and there were exactly 500 connections to 3306 port from loadbalancer ip with TIME_WAIT status.

max_connect_errors 1000000
max_connections 500

Setup as follows.

App1 ---> Haproxy1 ---> PXC Node1 App2 ---> Haproxy2 ---> PXC Node2 App3 ---> Haproxy3 ---> PXC Node3

Each haproxy configured with 1 active mysql node and 2 backup servers

server SQL1 xx.xx.xx.xx:3306 check port 50000 inter 3000 maxconn 160 rise 3 fall 3 backup
server SQL2 xx.xx.xx.xx:3306 check port 50000 inter 3000 maxconn 160 rise 3 fall 3
server SQL3 xx.xx.xx.xx:3306 check port 50000 inter 3000 maxconn 160 rise 3 fall 3 backup

We also use clustercheck script from percona to monitor mysql nodes on port 50000 with help of xinex.d

Any suggestions?

Answer 1

OBSERVATION #1

You have max_connect_errors to 1000000 (One Million). If you ever reach that many consecutive connection failures, you would simply run FLUSH HOSTS; to clear up blocked connections. Given the high value of that setting, you will never need to run FLUSH HOSTS;

OBSERVATION #2

I am glad you mentioned your TIME_WAITs. I wrote this post in ServerFault back on Feb 01, 2012 : MySQL lowering wait_timeout value to lower number of open connections. This is a bit of a hack but it is a necessary evil in this instance.

In short, just run this on every PXC node

SEC_TO_TIMEWAIT=1
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_recycle
echo ${SEC_TO_TIMEWAIT} > /proc/sys/net/ipv4/tcp_tw_reuse

This will make TIME_WAITs timeout in 1 second. You have to run these lines every time a node reboots.

• I got this idea from this post: How to forcibly close a socket in TIME_WAIT?
• The accepted answer has a pictorial representation of when a TIME_WAIT comes into existence.
• The answer with the idea that I liked is the one I am now suggesting.