Hacker News Reader

Comment by mattlong | Hacker News Reader

I agree with you 100%. For those of us needing to run MySQL, what would be the best ways to minimize the dependency on EBS and maximize HA?

View on HN

I haven't implemented this on EC2 myself, but Galera Cluster is a godsend for MariaDB/MySQL HA. Multi-AZ is probably viable, but multi-region may hurt.

A multi-AZ cluster with ordinary binary log replication to another region (with preparations to launch a new cluster in that region based on that slave on short notice) might be a good solution.

You would not want to implement PXC across regions - the round trip time would be too hard in individual transactions, and the ISTs would be murder to your bandwidth. Between AZs should be better, but remember with PXC that your transaction time is limited by your network round trip to the slowest node.

Regular asynchronous replication is your best bet, with a regular run of pt-table-checksum scheduled to ensure your data is consistent.

For those confused (as I was at first), falcolas is referencing Percona XtraDB Cluster, a particular distribution of MySQL + XtraDB + Galera Cluster.

> Regular asynchronous replication is your best bet

That doesn't get you HA in any practical form. I've done the heartbeat thing, it created more outages by itself than would have occurred with no HA solution at all, usually failed to kick in during a real failure, and we could never fix all the split-brain scenarios. Galera is absolute magic by comparison.

Hence my suggestion that a cluster be deployed within one datacenter, with asynchronous replication to a standby.

Galera is magic, I'll agree, but there are just too many shortcomings for me to recommend it for most people.

Heartbeat is very problematic, I agree, but it's hardly the only solution out there (and far from the best solution). That said, I have learned that it's remarkably configurable, so many of the problems you encountered could probably be addressed, if you're willing to learn about Pacemaker and really dig into its configuration.

I wish you'd elaborate on those shortcomings, because individual transaction latency is the only real one I've found, and for most workloads, it's not nearly enough to overcome the HA and CPU scaling benefits.

Can't you use RDS? It has Multi-AZ replication and failover. It may seem costly, but it means you don't need to manage all that stuff yourself.

I should mention that the system I'm building that uses it has yet to go into production, so others may have more experience on this than I do.

We're already using Multi-AZ, but still had DB connectivity issues throughout the outage.

We run a master-master setup between Oregon and Virginia, and its served us very well. We monitor MySQL instances for multiple customers, so uptime is a must; having a multi region setup has filled that role perfectly.

Reply on news.ycombinator.com