I have configured XtraDB cluster in there nodes. All the there servers has same MySQL configurations, 32 GB RAM and CentOS 5.
The Server version is 5.5.29-23.7.1 (64 bit).
During the load test we are getting the Too Many Connection error. I have found that MySQL process list occupied with creating tmp table statements which leads to reaching the Thread connection threshold value 1000.
The creating tmp table issue occurs randomly in one server, other two servers are working as expected.
Please let me know increasing the tmp table size will fix this issue or any thoughts why this issue happens,
Your tmp table sizes are already quite big and with 1000 connections all on this state this bound to cause problems even on production. What kind of queries are you running? I suggest looking at the queries first then your tmpdir - is it going to disk or consuming too much RAM and swapping?
The query creates disk tables and consuming RAM. The servers are behind the hardware load balancer and requests are assigned in round robin. I have verified the requests evenly distributed in the cluster. I wonder why this issue occurs randomly in one server during that time, other servers has no issues. Say the issue is occurring in DB1, it consuming low amount of RAM compare to other servers, but it dropping connections.