Home / server / Questions / 302417
In Process
Email geek
Asked: 2011-08-18 22:54:18 +0800 CST

Distributing mysql connections with ldirectord

  • 772

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.

mysql lvs ldirectord

2 Answers

  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"
    • 1

  2. 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.

    • 0