4

I need some clarification on MySQL-Cluster (NDB) setup

I want to achieve full HA but I'm low on server budget and can't afford to rent a dozen of servers for simple solution that I'm working on.

What's the minimum amount of hosts to achieve full HA in case of one node failure?

I mean situation that cluster should continue to work with no errors when any single physical servers fails. Failed server can be any of MySQL Cluster components (management, sql or data)

I don't need extreme performance, just fully automated server failure handling on small number of servers

From the MySQL Cluster documentation I know that I need at least two full copies of data (replicas). Does one replica need to be on more than one host?

Can HA be achieved on 4 servers? 2 management nodes (kind of small VPS) + 2 physical servers each running SQL and data node?

I'm aware of MySQL master-slave, but it doesn't provide automated failover and requires application rewrite

5
  • It all depends on what type(s) of failure modes you're trying to protect against.
    – EEAA
    Aug 25, 2012 at 12:48
  • I'm asking about dealing with physical failures (broken RAM, PSU, motherboard, etc)
    – Matthias
    Aug 25, 2012 at 13:10
  • 1
    Also think about maintenance, if you have to take one node down for updates etc. you have only one node left online and a spf (single point of failure). I would put at least 3 nodes in the cluster.
    – Izac
    Aug 25, 2012 at 13:25
  • @lzac thats actually very true, you need one spare at any time to switch over Aug 25, 2012 at 13:30
  • I just want to get 'can-sleep-at-night' or 'can-go-for-holidays' level of HA. Probability of multiple server failure at the same time in such small setup is low and I accept it. It's also acceptable to have temporary SPoF during scheduled downtime of one host
    – Matthias
    Aug 25, 2012 at 13:34

5 Answers 5

3

You really need to define what level of "HA" you are looking for quantitatively -- one man's "can-sleep-at-night' is another man's "this-thing-is-a-house-of-cards".


The minimum number of systems is two (2) -- An active and a standby replica (with something like heartbeat or custom-grown scripts to handle the failover).

With a MySQL cluster this means at least two SQL nodes and two Data nodes (to continue serving requests in the event of a failure of any one node). (If you also need redunancy on the management server's functions you would need two of those as well).

The key part here is testing the failover in a development environment -- which means you need at least two more machines (or a virtual machine). You also want to test upgrade and maintenance processes to ensure they won't trigger unintended consequences (Ideally you should do nothing to production that hasn't been tested and proven in Development).

If you fail to properly test you may trigger failover, which means you incur the procedural (and possibly business) cost of a failover -- typically having to rebuild the former active server to be a new standby server.

This protects against hardware failures (power supply, NIC, disk, switch (if they're on separate switches).

Note that this doesn't just apply to your DB servers -- You need two of *EVERYTHING: Web servers, DBI servers, Firewalls, DNS servers...
Redundancy of one component is meaningless if you still have a bunch of single-points-of-failure in your stack.


The next level of protection is network failures ("What if my ISP goes down?") - this requires replicating your whole redundant environment above to a remote datacenter.

What's important here is that you diversify network connections, power, etc -- You don't want your standby datacenter across the street where it's fed by the same power and fiber as your main facility.
A company I consulted for had a requirement that any remote facility used for DR be "at least 15 degrees of longitude away" (i.e. "In the next time zone"). A common practice in the US is East-Coast/West-Coast, or NY/Chicago LA/Texas.


The next level above that is truly distributed resources (think Google) which requires a database system that supports replication and sharding (think MongoDB).
If implemented properly there's almost no chance of a true "outage", though service may occasionally be degraded and recovery can take a while.

6
  • This question is only about HA of MySQL-Cluster. I'm aware of HA issues with all other elements of my infrastructure but the database is hardest one. So again - my question is only about minimum number of MySQL-Cluster servers, not web servers, ISP links or anything other
    – Matthias
    Aug 25, 2012 at 15:58
  • @Matthias There is no relevant difference. N+1 is always the minimum number for redundancy. The minimum value of N for any required component in a system is always 1. Therefore 2 is always the minimum number of servers required for redundancy. That minimum number grows as your definition of "redundancy" expands to encompass more than just "some idiot unplugged one of the servers", per my answer.
    – voretaq7
    Aug 25, 2012 at 16:01
  • Yes you are right, but still my question is strictly about MySQL-Cluster specifics. I still don't know if I need 2 managers + 2 sql nodes + 2 data nodes = total of 6 hosts. More or less? Two replicas are OK or I need more? Thats 6 servers to host one database and still I'm not shure is it OK
    – Matthias
    Aug 25, 2012 at 16:29
  • @Matthias See my edit in the answer - You need a minimum of two of every component for which you want redundancy (if you could live without the management server's functionality for a few days there's no need to have a redundant one, but if you want to keep serving database requests you need at least (2) data nodes and (2) SQL "front-end" nodes). Note that there's no rule that says the standbys have to be separate physical systems: You could theoretically have a machine that can fail into either a data node or a sql front-end node. I don't know if MySQL officially supports that though.
    – voretaq7
    Aug 25, 2012 at 16:45
  • 1
    @Sandman4 I am familiar with MySQL-Cluster. The "generic (and well known)" advice is the answer to the question -- In fact it's the architecture described in the documentation.
    – voretaq7
    Aug 26, 2012 at 1:39
1

Answer to your question: 2.

All these data, sql and management nodes can run on a single machine. With two such 3-node machines you have full redundancy.

Keep in mind that NDB is in-memory. Failure of all nodes means not "service temporarily unavailable", but "all data is gone forever". I don't know how many nodes I need in such a setup to sleep at night. At least add some replication to disk database.

My personal conclusion was to not use MySQL cluster at all. There's a plenty of clustered databases today.

2
  • You are wrong, NDB is memory based but has also built-in HDD persistance and data continues to live even after restart of all nodes
    – Matthias
    Aug 27, 2012 at 16:41
  • @Matthias Carrier-grade, or community edition ?
    – Sandman4
    Aug 27, 2012 at 20:20
0

2 physical servers are enough for full HA, if you believe your network is robust enough. Otherwise you need additional small physical server for cluster arbitration - to avoid split brain in case of network connection lost between data nodes.

The default and recommended number of replicas is 2, it means given data is stored on two nodes: the "main" one and the "backup" one. See https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-noofreplicas for details.

0

My current setup is actually 4 machines:

On all 4 I run the management node.

On the first nodes (1,2) I run web apps + management node

On the other nodes (3,4) data/management/and sql nodes.

Web servers connect via load balanced ip on the sql nodes so they always see one active. Works for me.

0

For evaluation and evelopment purposes, you can run all nodes on a single host. For full redundancy and fault tolerance, you would need a minimum 6 x physical hosts:

2 x data nodes 2 x SQL/NoSQL Application Nodes 2 x Management Nodes Many users co-locate the Management and Application nodes which reduces the number of nodes to four.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.