Home / server / Questions / 288031
Accepted
Brad
Asked: 2011-07-08 11:24:21 +0800 CST

MySQL Multi-Master Replication vs. MySQL Cluster

  • 772

I need a MySQL database that is fast and supports many connections. Most of the connections will only be reading, but a few will be reading/writing. All connections will need to read and write at least some data.

I have 4 test servers to dedicate to experimenting. Originally I was planning on doing multi-master, but then I read a bit about MySQL Cluster. I have some questions:

  1. Is MySQL Cluster RAM only? The brochure says disk tables are supported, but even their own documentation sometimes says they aren't. I want to be able to survive a power outage.

  2. Does MySQL Cluster give me any better reliability vs. multi-master? I worry about a power outage causing my multi-master installation to be hopelessly out of sync. Being able to smoothly recover from a power outage, or other failure, is my primary reason for considering something other than multi-master.

  3. Is there any way to use temporary tables? My application uses a few temporary tables, but I see that MySQL Cluster doesn't support them. Is there a work-around other than using permanent tables as if they were temporary?

  4. Can I add and remove data nodes at any time? Without any service interruption?

mysql mysql-replication mysql-cluster

1 Answers

  1. Best Answer

    To answer your questions (they are answered in the manual, too btw.)

    1. MySQL Cluster (ndb) keeps all indexes in Memory all the time. It's data structures and access patterns are optimized for that case. They can (optionally) be written to disk, too. Non-indexed data can be stored on disk and will be read (and cached) as needed. In general for a database it is good to have enough memory to keep the working set in memory, but cluster is a bit stricter on this.
    2. MySQL/Oracle advertises MySQL Cluster as 99.999% realiability. Mind that a lot of the reliability is not in the software but the environment. If your switch or power dies the complete cluster might go down. MySQL Cluster has quite good routines to stay functional and sync if a node goes down and comes back. doing this properly in MMR is a bit more work, but can be done, too.
    3. Per 1. MySQL Cluster works well with data in RAM so probably you could use ndb for those, too. Alternatively, depending on the actuall data and use case you probably might keep the temporary tables independently on the different MySQL servers. Note that there are benchmarks (while benchmarks always lie) showing that ndb is fatser than Memory tables.
    4. Yes, recent version of MySQL cluster allow online changes to the node configuration and even online changes to the table structures. Both are more complicated with MMR.

    Having said all that good about MySQL cluster I still want to note that: MySQL Cluster has some limitations, for example doing JOIN operations on a MySQL cluster is currently painfully slow. The next version is going to fix that (look for "push down joins") so you should be careful when setting things up and do some tests before going to cluster to see whether it fits your needs.

    • 3