Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


MySQL Cluster vs Master-Master
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

MySQL Cluster vs Master-Master

My application requires a lot of MySQL writes from two application servers. I want the data to be shared between the two servers, but I don't want to have a single point of failure.
I tried setting up MySQL Master-Master replication, but it didn't work well for me: when one master went down and then restarted, it didn't know which transaction to start from and just failed.
I need the servers to be able to recover and failover in unattended manner.

Should I try to setup a Cluster instead? Are there any products which automate (or simplify) the setup?

Comments

  • You should use percona mysql or mariadb with galara and active-active. You must use at least 3 servers to get a quorum.

  • Just to remove some confusion, percona mysql with galera is also known as XtraDB. The third server can also be a lightweight arbitrator.

  • Using galera/percona aint going to be 100% unattended, especially for ip failover, whilst 1 will be active, 2 wont be usable until syncing has finished iirc, so you will have to either run these behind some sort of load balancer which also has failover or make sure the applications are pointing to that 1 active instance if anything occurs.

  • You need only a small load balancer like haproxy and a virtual ip (VRRP) before your sql servers. It shouldn't be a problem and really fast to setup it. Enough tutorials on percona's website for haproxy + mysql health check.

  • If you have the capability to manage a MySQL cluster, go with the cluster. It allows multiple servers to act as a single DB server and is transparent to the application (it's just a different storage engine in MySQL). Any node is fully safe for both reads and writes. You define how many nodes you want your data replicated across (so all data is available if nodes go down), and if a node goes down, it instantly starts sharding data to other nodes so it gets back to having at least xx live copies of data.

    There are many nice advantages of a MySQL cluster. For example it supports hot backups without anything complicated (ndb_mgm -e "START BACKUP").

  • Thank you guys, I think I will go with this approach: http://www.percona.com/doc/percona-xtradb-cluster/5.5/howtos/virt_sandbox.html using 3 Percona XtraDB Cluster nodes (instead of the originally envisioned 2) and HAProxy on each of the application servers to route requests to the cluster.
    My concern with this setup is that I will be incurring extra latency on every write, but hopefully it won't be too bad. Any ideas how much RAM I need on each one of the 3 nodes? I was really hoping to use two nodes with 2GB RAM each, if I have to add a third node I am limited to either having 256MB on it or using an arbitrator (not sure if I will be able to set it up).

  • I've managed to squeak it into a 256MB vSwap once (128MB was no-go). I wouldn't call it the most stable at that level but since its not an active reader, it's probably less of an issue (mine was active writing just for kicks). The arbitrator is even better since that would only need to ack and doesn't touch disk at all thus reducing the latency all-round.

    The latency isn't too bad though given how Galera works under the hood especially if they're right next to each other. Still, it pales in comparison to sharding though.

    There are a few gotchas though. Depending on transaction throughput you might run out of memory sooner than later. Unlike a standalone, transactions are kept in-memory so if you're doing benchmark runs with inserts, keep that in mind. When I was trying playing around, it didn't release the memory immediately even after commit. Dunno if that was a bug waiting to be fixed or whether it was just waiting for a sync update round to flush it all.

Sign In or Register to comment.