0

I'm using ldirectord with heartbeat for mysql load balancing. It is distributing mysql connections equally to all servers available in the pool but sometime slave remains in the pool even after encountering a Too many connections error. There should be some option to count number of running queries on the slave before sending more connections to it.

Anyone who is using ldirectord for mysql and would like to share the same experience with suggested solutions for this hitch?

Thanks.

Email geek
  • 67
  • 1
  • 8

2 Answers2

1

From these pages:

http://www.ultramonkey.org/3/topologies/ha-lb-eg.html

http://www.ultramonkey.org/3/topologies/config/lb/non-fwmark/linux-director/ldirectord.cf

It looks like what you want to do is setup some URL you can access on each back-end host (something like a tiny CGI with thttpd would do the trick) that tests the daemon on the box and reports something that doesn't match the receive string. When this happens ldirectord will pull the node from the pool.

Example config:

request="test-mysql.cgi"
receive="MYSQL OK"
polynomial
  • 4,016
  • 14
  • 24
0

If you use the least-connection load balancing algorithm, IPVS will send a new connection to the backend with the least number of active connections, which will mean that you'll only get a "too many connections" error when all the servers are full. There are currently no mechanisms in IPVS (or ldirectord) to specify a limit to the number of simultaneous connections a backend will accept before it is unable to handle any more; it wouldn't be too hard to implement, actually, but the question is what do you do with connections when you're full? RST the connection attempt? Whatever you do, it's going to be an error that the client will have to handle, and "too many connections" in MySQL-protocol-speak is easier to diagnose, in my mind, that "Connection refused", because there's a lot more and varied reasons for the latter error than the former.

If it were my cluster, I'd switch to least-connection balancing and add a lot more monitoring around workload; if you're hitting limits and you didn't know you were getting close to them well in advance, you've got a monitoring fail right there.

womble
  • 96,255
  • 29
  • 175
  • 230
  • some time a bad written SQL query stuck put other queries in queue e.g total connections on this server reaches to 200 while other slave still have 40 active connections becuase of its behaviour it doesn't check the count of connections on server end instead it just send connections with least count to all slave sql servers.. I'm looking for alternative or some solution for this hitch... like ldirector should keep count of connections on server end in memory before it should send more connections. – Email geek Aug 19 '11 at 05:42
  • 1
    So fix your SQL queries. And the least-connection balancing algorithm does exactly what you're proposing ldirectord should do, which is the wrong place to be doing it. – womble Aug 19 '11 at 05:47
  • This is what we are doing so far.. thought if any one have other suggested solution for this hitch! While @Womble For SQL queries fixation you have to fire developers as they always write dirty SQL queries which kills server and site performance. – Email geek Aug 19 '11 at 06:37
  • Or you could train them properly, or hire decent developers in the first place... – womble Aug 19 '11 at 06:47
  • I will wait on this thread if anyone else have recommendation. – Email geek Aug 20 '11 at 06:17