1

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?

  • What is the output of SHOW GLOBAL VARIABLES LIKE 'max_connect_errors'; ? – RolandoMySQLDBA Aug 10 '13 at 22:50
  • There are several aspects of this question that are vague and unclear. It would be helpful if you would at least provide the exact error message from MySQL and what you did when you "increased the connection limit," since there is not a variable called "connection limit." What do the HAProxy instances report, as far as instance health and max connections? Presumably the "nodes" are not failing, as stated, but actually client connections are failing, but this is also unclear. You also don't mention how many connections you're seeing in SHOW PROCESSLIST on the servers, or their states. – Michael - sqlbot Aug 11 '13 at 6:12
  • Question edited, with proper information. – adinindu Aug 11 '13 at 19:40
0

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.

  • Thank you for your suggestions. But now we have come up against another issue here and we would like to hear your thoughts on it. Thanks again. – adinindu Aug 23 '13 at 11:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.