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?
SHOW GLOBAL VARIABLES LIKE 'max_connect_errors';
?SHOW PROCESSLIST
on the servers, or their states.