3

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?

Improve this question
5
  • Master-master is for redundancy. Cluster is for performance. Which one are you after? Commented Jul 7, 2011 at 22:10
  • @John, both really. I have SELECT ... INSERT queries that take an extremely long time, and clients reading from the database all the while this is going on. Multi-master with row-based replication means that one server can handle the large queries, while another one can deal with clients. I am mainly looking at cluster as a more robust option for this, but I lack understanding of what cluster actually does. Commented Jul 8, 2011 at 2:28
  • if you need both then the best is probably master-master on decent hardware, unless it's possible to master-master clusters. Commented Jul 8, 2011 at 4:41
  • @John, thank you for your comments. Is there any way to make recovery after connection loss/power failure simpler... more automatic? Do you know the answers to my four questions above? Commented Jul 8, 2011 at 13:42
  • I've never done MySQL clusters. I can however tell you that for master-master (or master-master-...-master) the service must be restarted in order to add or remove nodes. Commented Jul 8, 2011 at 20:07

1 Answer 1

Reset to default
3

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.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.