Cross-Continent SQL High-Latency Data Synchronization

Question

How would one go about synchronizing a MySQL database cross-continent, while keeping the distributed application using the DB running fast (<0.2s page loads) ?

My database contains about 2.5GB of data, having almost all 200 tables indexed by auto_increment int key, so some kind of MASTER-MASTER replication seems not quite possible, as duplicate keys might occur.

The hardware my project runs at are located in 2 datacenters in US-West and EU-West (130ms ping latency between each other)

I've thought about using a Master SQL server in EU-West, and having a slave server in both regions, and having the application servers connect directly to the master server for insert/update queries only, and connecting to the slave servers for select queries.

The thing is that when having to execute a few insert/update queries, this may cause really slow page loads for users connected to US-West. Even when running the queries async can cause a slow user experience.

Is there a different way how I can guarantee that users in neither region experience slow page loads due to having to write to a high-distance SQL server, while keeping data synchronized between the 2 regions fast, without having to worry about duplicate keys and synchronization errors ?

Answer 1

Duplicate keys with AUTO_INCREMENT are not "possible". See auto_increment_increment, which must be set to 2.

In general M-M is problematic for a number of reasons, which I will not go into, especially if you write to both of them. You are better off writing to only one of them, leaving the other as a hot failover server.

For pages that only need read access, have Slaves in both datacenters.

Have client machines in both both datacenters; have them hit the local Slave for reading (if it is not down).

But beware of the "critical read" problem.

Plan A: Change user expectations so that they understand that writing may be slow.

Plan B: Use NDB Cluster with its "eventual consistency" model of replication. Caveat: There are a lot of differences between InnoDB and NDB.

A technique that may help: If you need to send multiple queries to a 'remote' server, package them in a Stored Procedure so that there is only one cross-continent hop. (This, of course, is not always possible.)