Haproxy for mysql
-
Dear All,
Basically, I am a very happy user of haproxy-1_5 (thank you PiBa for the substantial exchange on this in the past!). I very much like to run haproxy on pfSense, as I have a CARP situation on adequately powerful servers (C2758), so high availability is nothing to really worry about.
What I would also like to use is haproxy for MySQL. Basic MySQL settings are available in the package. I run a percona xtradb cluster which is based on MySQL and haproxy is basically one of the standard tools to ensure high availability in case one of the cluster members is down. Particularly in one application with frequent MySQL access via perl (this is the ASSP anti spam SMTP proxy, assp.sf.net), I do get problems with "MySQL server has gone away" if I put haproxy in front of the percona cluster. If I set the application to contact one of the cluster members directly, no problems of this kind will occur.
What I obviously did to try to cure this is to set the client timeout in the front end, the connection timeout and the server timeout in the back end to one day each plus the retries to 16. However, this did not solve the issue.
With other applications, e.g., collecting asterisk call detail records from two locations partly via a VPN, such issues did never occur.
Does anyone have similar experiences and/or ideas how to avoid this?
Regards,
Michael
-
Hi Michael,
I dont have a mysql install to test against.. But haproxy doesnt care much as tcp is tcp.. Or so i think. Anyway i routed a ssh connection through haproxy with 30 second timeouts and it could sustain the connection for a hour untill i stopped viewing the output from 'top'.. When no traffic was flowing the timeouts kicked in.. I imagine something similar would happen to a mysql connection…
Have you configured syslogging in haproxy with detailed information? What gets logged when the connection breaks?
If possible some tcpdump captures of a broken connection could be interesting.. See what packets are exchanged when it breaks..
While you say that queries are regularly send and thus the timeout should never strike, could it be that a connection-pool is used of which some connections are rarely used?
Also a firewall might be dropping a connection, maybe keep alive options should be set on haproxy?
option clitcpka option srvtcpkaHope some of this helps..
Regards
PiBa-NL -
Dear PiBa,
Thank you very much! While I could not really figure out the cause, what did help was to set "Stick on SourceIP" in the backend. Generally, the cluster to which HAProxy does connect has locking set up in a way that it should not matter if one request goes to one cluster member and the next request goes to another cluster member. Nevertheless, this does seem to help.
Regards,
Michael