MySQL Replication - one website, many servers, different continents

Question

Consider a reasonably large website (2M+ pageviews / m, lots of users) with 2 frontend servers: one front server in the US, and one in Europe. Two dedicated URL bring the visitors on one of the server, one in the french language, the other one in english. Both sites share exactly the same data.

What would be the most cost effective solution? (DB used at my company: MySQL)

1/ A single Master server on Amazon EC2 (US), and slaves on the frontend servers?

• Advantages: no master-master rep, meaning no risk of data conflict with autoincrement and duplicates on unique columns, etc..

• Drawbacks: The lag! Won't there be too much lagging for writing in the US when you are in Europe? Another drawback could be the lack of quick n dirty solution in case the master dies. And what about having slaves on same server as front?

2/ Two Amazon EC2 instances, one in the US, one in Europe, acting as master-master replication servers. Plus two slaves on each of the frontends?

• Adv: Speed, and security of data. Of course there is no load balancer, but making a hack to switch the master to the other one seems pretty trivial.

• Drwbcks: Price. And the risk of corruption on the DB

3/ Any other solution ?

As it is my first time working with servers in 2 continents, I would really appreciate learning from you experience in that area, including MySQL or not, including EC2 or not.

Thanks Marshall

Answer 1

As usual, what I'm about to say depends on your app, how it uses the database, etc. You need to ask yourself:

• If you're using off the shelf software, what have other people done in this situation?
• Does the app need to work on the entire dataset, or can you partition?
• Is your app built to handle multi-master replication (usually means uses autoincrement pk's)
• What are the chances of update/delete collisions? What are the costs?
• What's the read:write ratio? What's the nature of the writes? Are they typically update or append operations?

I'm assuming the french server is in Europe, while the English server is in the US? If you can partition your data so that the french site uses one DB and the english site uses the other, you're better off. Even if both sites access both DB's, since you don't have to worry about collisions. You can even run two mysql instances on each master server and do multimaster replication for both.

If you can't partition, I'd probably go with #2, but I'd designate one of the machines as the 'true' master and send all the writes to it to help avoid data clobber. This way it's easy to switch in a pinch.

If you're cost sensitive and you're going to run replicas on your front end servers anyway, just run the master databases on the front end servers. You can always pull it off later. Replicas can often have higher CPU/IO costs than masters taking the same read load: they have to execute their writes in serial, which can really screw things up.

Also, don't use m1.small instances for your DB. Or at least keep an eye on your performance. m1.smalls are significantly under powered, and if you watch top, you'll notice a significant percentage of your CPU time being stolen by the hypervisor. I recommend c1.medium's.

Answer 2

Don't use master-master replication, ever. There is no mechanism for resolving conflicts. If you try to write to both masters at the same time (or write to one master before it has caught up with changes you previously wrote to the other one), then you will end up with a broken replication scenario. The service won't stop, they'll just drift further and further apart making reconciliation impossible.

Don't use MySQL replication without some well-designed monitoring to check that it's working ok. Don't assume that becuase you've configured it correctly initially it'll either keep working, OR stay in sync.

DO have a well-documented, well-tested procedure for recovering slaves from being out of sync or stopped. Have a similarly documented procedure for installing a new slave from scratch.

Your application may need sufficient intelligence to know that a slave is out of sync or stopped, and that it should not be used, if you care about correct or up-to-date data. You'll need some kind of feedback from your monitoring to do this.

If you have a slave in, say the US when your master is in Europe, that would normally give you the amount of latency you expect, i.e. something in the order of 150ms more than if they were co-located.

In MySQL, the slave does not start a query until the master finishes it, so it will always be behind by the length of time an update takes.

Also, the slave is single-threaded, so a single "hard" update query will delay all subsequent ones.

If you're pushing your master hard on multithreaded write-load, assuming your slaves have identical hardware, it is very unlikely that they'll be able to keep up.