Re: configuration where 2 out of 3 physical servers can fail

[Mikael Ronström]

For a detailed description of how things works as mentioned by Jon
Stephens look into the manual. So I will rather discuss your specific
use case.

First of all to use 3 MySQL Servers, one on each machine is quite ok.
Each such MySQL Server will have at least one API node, by using
several cluster connections it can actually use several API nodes.
The reason for this is that one API node is represented by one socket
to each other data node and management server. So to get higher scalability
of the MySQL Server one can use several API nodes. In your case with a
small application this will most definitely not be needed.

Also 3 ndb_mgmd, one on each machine is also quite ok.

However for the data nodes only 2 replicas is currently supported.
So use 1 data node on 2 of the machines and none on the third.
These two data node will form one node group. If you had 4 data nodes
instead you would have two node groups. Each node group contains a
subset of the data in NDB, the data inside a node group is fully
replicated on all nodes in the nodes in the node group.

With this setup you will survive 1 node failing at a time and have
only one remaining computer up and running in 50% of the cases.
The first failure you will always survive and the second failure
you will survive with 50% probability. It is important that the
management server on the computer without data nodes has the highest
ArbitrationRank.

We are working on 3 replica and 4 replica support. I have tested
this to see how well it already works. It works actually very well.
Only a minor problem in graceful shutdown was found that will soon
be fixed. However the method used to decide whether a cluster will
survive is not the one we want to continue with.

So in 7.5 the following happens at node failures with 3 data nodes
using 3 replicas. If at least one data node survives AND the
management server that is currently the arbitrator survives, then
the cluster will continue to operate. If the management server that
was arbitrator fails AND one data node fails, then the cluster will
fail.

So this means that if 1 computer fails there is a 66% probability of
survival and a 33% probability of cluster failure. If 2 computer fails
at the same time then there is a 33% probability of survival and a
66% probability of cluster failure.

If two nodes fails, but they fail one at a time then there is a
33% probability of survival still.

This will change in future versions of MySQL Cluster. So as you can
see currently a setup using 2 replicas of the data nodes gives higher
availability than currently 3 replicas provide. The story becomes a bit
different when there are multiple node groups using 3 replicas.