5 ms·

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

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

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.