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 ?
Best Answer
Duplicate keys with
AUTO_INCREMENTare not "possible". Seeauto_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.)