Load balancing MySQL

So I have HAProxy 1.3.22 running on CentOS 5.4 load-balancing a MySQL Cluster 7.0.8. It works, for the most part :)

I'm wondering if anyone has sane numbers for various timeout values for HAProxy. I find HAProxy (I'm guessing) is killing the connection after timeout amount of time.

As long as I generate traffic (ie: I send a query, or I get results back form the MySQL server) the overall connection stays alive. But if I generate no traffic from my client in timeout client amount of time (I'm typing in a particularly annoying query for example), then when I finally hit return, the mysql binary kicks up a warning saying my MySQL server went away and is trying to reconnect. It does reconnect, and it's fine, but only because the mysql binary is capable of handling such problems.

I arbitrarily put my timeout client and timeout server values to 60s. So as long as my client generates a query every 60s, the client-HAProxy side of the connection remains alive. And I figure as long as I make such a query and the MySQL server can process the result and generate a response and send it back w/in 60s, the HAProxy-server/backend side of the connection stays alive too.

I had hoped to merely use tcpka but that didn't do it. I didn't quite want to risk leaving the timeouts at infinite. I realize once this goes into production, there really aren't any manually entered queries, and it's just a bunch of applications that want to make use of a load-balanced MySQL server.

What I'm afraid of, and technically not HAProxy's problem but I'm trying to resolve this at the HAProxy level anyway, are those clients that might create a connection to the MySQL server when the program starts up. Then sleeps idle until some user comes along on the web and does something, at which point the webserver (or just any application) then finally has to go query the MySQL server looking for data. Since I certainly can't guarantee that a client will come along every 60s, I don't kno what to do. Again, I suppose I could put in an infinite timeout but I'm a bit loathe to do that.

If tcpka is supposed to keep the connection alive even though there isn't any "real" traffic, then I've done something wrong and it isn't.

If anyone has deal with such issues of putting a service behind HAProxy, where a client might iniiate a connection upon startup and then sleep until "something happens", I'd greatly appreciate a note, or a reply back to the list.

Thank you,
PH

--
Paul Hirose          : pthirose#ucdavis.edu : Sysadm Motto: rm -fr /MyLife