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?

Brad
  • 1,389
  • 20
  • 43
  • Master-master is for redundancy. Cluster is for performance. Which one are you after? – John Gardeniers Jul 07 '11 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. – Brad Jul 08 '11 at 02:28
  • if you need both then the best is probably master-master on decent hardware, unless it's possible to master-master clusters. – John Gardeniers Jul 08 '11 at 04: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? – Brad Jul 08 '11 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. – John Gardeniers Jul 08 '11 at 20:07

1 Answers1

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.

johannes
  • 583
  • 2
  • 10