I'm an architect working on a project to rebuild our current consumer site, possibly using Drupal, and am new to Drupal and learning as much as I can as fast as I can. We currently serve all users to our site out of a single colocation (colo), but in the near future are planning to add additional colos in other geographic locations and serve the site concurrently from them all, with users being routed to the nearest colo.
I'm wondering if and how folks have architected Drupal websites that are hosted in multiple colos, e.g., east, central, and west US. Do you guys 1) know of or work on any such sites, and 2) if so, know what database architecture they're using?
It seems like there are only 2 options for how to handle multi-colo Drupal database writes: 1) have the writes in each colo go to a local master DB, and use multi-master replication across the N colos, and 2) have all the writes go to a master DB at a single colo, and replicate to read slaves in each colo.
Are Drupal core and most modules written such that a multi-master setup would be feasible and not result in database inconsistencies from concurrent writes to different masters, e.g., from auto-incrementing fields and such? What about when external caching (memcache) is enabled -- is the assumption generally that all DB writes happen through the Drupal stack and thus flush or update caches on write, which would be violated if the writes happen in a remote colo and are only replicated locally at the database level (not through the Drupal stack)?
Option 2), writing to a single colo, has major limitations in terms of latency and scalability so doesn't seem like much of an alternative either.
Comments / thoughts?