Re: How best to support global website?
Posted by:
Jack Lewis
Date: October 16, 2014 07:14PM
Thanks Rick. Just a few follow-up comments/questions:
> If you are blindly sending all SELECTs to Slaves, you could be sending a SELECT that supports a subsequent UPDATE or DELETE. If the Slave is 'behind', you could get the wrong effect.
Yes, I'm aware of this. I'm using PHP's Mysqlnd replication and load balancing plugin, which has worked quite well. You can configure it to send all statements to the master, if within a transaction, which is how I handle the situation you describe.
> Plan A: You wear a pager and hop in to deal with the active Master failing.
If this is just for failure of the master, under normal cicumstances, I'm happy to do this, but what I'm really looking for is a way to write (not for scale) to multiple masters, which as I understand it, could cause the likelyhood of replication errors increase significantly.
> Plan B: Look into MHA.
Seems to be just for master failover, and as above, I need to write to multiple masters.
> Plan C: Galera-based multi-master (MariaDB 10, Percona XtraDB Cluster, etc)
This seems to be the only real option. Have you done this? Is it very difficult to setup/maintain?
> If your datacenters will be scattered around Australia, check out the timing -- perhaps only 10-20ms between them. Not enough to justify this effort.
No, I'm considering data centers in Sydney, London, Singapore and US regions.
Which brings me back to my original first/simplest/safest option:
1. Don't globally distribute. Just deploy this stack in one datacenter, and service all clients from that stack. Yes, some clients would get higher latency, but maybe the latency is not significant enough to overly complicate the infrastructure?
Do you or anyone else have any advise on this? What is typical for SaaS services? I know I've dealt with SaaS provides who don't have data centers in AU, so at least this is not unheard of in some regions.