[Linux-cluster] FWD: Question on RH Cluster from a MySQL Customer

[Lon Hohberger]

On Sat, 2007-02-24 at 22:04 -0500, Tom Lane wrote:
> Can someone help out this questioner?  I know zip about Cluster.
> I looked at the FAQ for a bit and thought that what he wants is
> probably doable, but I couldn't tell if it would be easy or
> painful to do load-balancing in this particular way.  (And I'm not
> qualified to say if what he wants is a sensible approach, either.)

The short answer is "yes, sort of".

With all the data on separate places on the SAN, you can certainly spawn
as many instances of MySQL as you want, and have them fail over.

There, however, is currently no way to make linux-cluster figure out
where to place new instances of MySQL based on the number of instances
of MySQL which are currently running.

Now, you can set sort of an affinity for specific nodes, and manually
have the instances of MySQL set up, say, like this:

  node 1 -> runs instances 1 and 5
  node 2 -> runs instances 2 and 6
  node 3 -> runs instances 3 and 7
  node 4 -> runs instances 4 and 8

You can make it decide to split the load, for example, set the preferred
list for instance 1 to:

   {1 2 3}

While setting instance 5 to:

   {1 4 2}

If node 1 fails, instance 1 will start on node 2, and instance 5 will
start on node 4.  With enough thought, you probably could get it so that
the instances will be equally distributed regardless of the failure
model.  Something like this for 4 nodes + 8 instances (did not check for
correctness):

Inst.  Node list (e.g. ordered/unrestricted failover domain)
  1   {1 2 3 4}
  2   {2 3 4 1}
  3   {3 4 1 2}
  4   {4 1 2 3}
  5   {1 4 3 2}
  6   {2 1 4 3}
  7   {3 2 1 4}
  8   {4 3 2 1}

-- Lon