1

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 ?

| improve this question | | | | |
  • What would be the reason for „may cause really slow page loads“. Are you expecting that or did you measured it? (Replication can be async it won’t delay your writes, however you app will have to deal with stale slaves then) – eckes Mar 18 '18 at 16:04
0

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.)

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.