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