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 ?