MySQL Multi-Master Replication vs. MySQL Cluster

MySQLmysql-clustermysql-replication

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:

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.
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.
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?
Can I add and remove data nodes at any time? Without any service interruption?

Best Answer

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

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.
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.
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.
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.

Related Solutions

MySQL 5.1 Replication Topologies: Multi-Master

Master-master replication is asynchronous, hence it will definitely break if you write to both servers at once.

Even if the auto-increments are working, any other unique index and many other situations can break it - it's too brittle to be used.

But it is possible to use master-master as PART of a HA solution, you just need to ensure that applications only ever write to one of the pair and in a "clean" failover situation, e.g. admin failing over, it waits for the slave to catch up before switching.

This is not extremely difficult in practice, but a bit inconvenient.

Your main other option is to use DRBD, which is also not massively difficult to set up - but in this case, the 2nd machine is not even usable as a read replica - it just sits there being a hot spare. DRBD synchronously replicates the underlying storage, so everything is written safely to both machines.

There are some applications which are specially designed to tolerate the multi-master problems - these need to be designed VERY carefully with that exact situation in mind - in which case, it's ok. You can't use applications not designed for it though.

auto-increment is not the only, or the main problem.

Mysql – Sporadic crash of master-slave MySQL replication process

Double check that your character sets on the databases and connections are what you expect them to be - your replication choking on a statement with a non-ASCII character is immediately suspicious.

Look at the master's binary log to see how the statement it logged is different from the statement that the replica is trying to execute - that will offer clues to the problem.

Sadly, you should also make sure that the versions of the server for both the master and slave are the same. I recently encountered a weird instance where we had bad SQL trying to update a non-NULL numeric column to NULL. In the version that the master was running, MySQL silently converted it to 0, but in the version that the replica was running, it was an error.

Also, instead of doing CHANGE MASTER to fix the replica, you should be doing

STOP SLAVE;
[correct and execute whatever statement is the problem]
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

http://dev.mysql.com/doc/refman/5.1/en/set-global-sql-slave-skip-counter.html

Best Answer

Related Solutions

MySQL 5.1 Replication Topologies: Multi-Master

Mysql – Sporadic crash of master-slave MySQL replication process

Related Topic