6 ms·

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?

nknighthb 13y ago
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.
- falcolas 13y ago
  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.
  - nknighthb 13y ago
    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.
    - falcolas 13y ago
      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.
      - nknighthb 13y ago
        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.
falcolas 13y ago
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.
scott_w 13y ago
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.
- mattlong 13y ago
  We're already using Multi-AZ, but still had DB connectivity issues throughout the outage.

nknighthb 13y ago

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.

falcolas 13y ago
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.
- nknighthb 13y ago
  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.
  - falcolas 13y ago
    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.
    - nknighthb 13y ago
      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.

falcolas 13y ago

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.

scott_w 13y ago

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.

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