Re: How best to support global website?
Posted by:
Rick James
Date: October 12, 2014 08:55AM
> to direct read requests to the slaves and write requests to the master.
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, a colocated slaved can be behind enough for this to happen.
You must be careful of this issue; it is very hard to debug after some data goes wrong.
> My ideal is to be able to replicate this stack to other datacenters around the world
Yes. But not exactly.
> so that users have low latency, with the master databases in each datecenter replicating with each other in some way... but is this the best approach?
There is no problem scattering Slaves around the world, thereby making the SELECTs be low latency.
But the Masters...
It is a potential mess to have two live Masters replicating from each othere (dual-master, active-active). It is much safer and simpler to do active-passive. That is, one Master is effectively a 'hot backup'.
However, today there is no safe, out of the box, way to automatically failover.
Plan A: You wear a pager and hop in to deal with the active Master failing.
Plan B: Look into MHA.
Plan C: Galera-based multi-master (MariaDB 10, Percona XtraDB Cluster, etc)
Plan A also entails writing only to the active master, wherever it is in the world. Replication delay to the other side of the world is 200ms+, adding to the likelihood of this problem (which must be faced in any solution):
1. The user posts a blog (writing to a Master)
2. The UI immediately switches to another page, showing his blogs. That new blog needs to be there.
The problem is two-fold:
* This new page grabs a new connection.
* The connection is to a Slave.
* The slave could be 'behind'. You already have this problem, but global deployment accentuates it.
AWS -- I am not familiar with the details. It may have other issues on top of what I have described. (My expertise is from company-owned and serviced datacenters and leased lines.) Sure, AWS will promise the world, and maybe they do the best possible. But the speed of light is finite.
> but can it be successfully implemented (especiallly with > 2 masters?)
Yes. But you are asking for big-time trouble. Replication is uni-directional, say, clockwise. If one Master dies, look at the mess left behind. I never want to clean up that situation.
Galera, OTOH, has solved it; you can write to any Master. 3 Masters is the minimum for HA. Slaves can be hanging off each master. You get almost everything you want.
> In general each datacenter will support users in that region, and data will not generally be updated form multiple regions.
That should help craft a solution, but it does not help much.
> But, I do want to have just one database that contains all data, especially if we had to fail-over from one region to another.
If you have totally separate M-S setups in each data center, and use a technology that allows "multiple masters for one slave", you can achieve that. Galera, MySQL 10, MySQL 5.7 each allow such. But you need to think through the details.
MySQL Cluster -- too many changes, even to your code.
Tungsten -- probably a viable competitor; I forget the details.
If your datacenters will be scattered around Australia, check out the timing -- perhaps only 10-20ms between them. Not enough to justify this effort.
Also check out costs -- Australia seems to be much more expensive than elsewhere in the world.