Geographic redundancy and MySQL replication

Question

Hi folks,

I has a fairly simple site getting 200K hits/day (3-5 hits/sec) using a LAMP configuration. The host is somewhat flaky (read: cheap), so we want to add a 2nd host for guaranteed uptime (I wouldn't call this mission critical, but more annoyance-prevention).

MySQL is idling at about 200 qps, spiking for 450 at times.

I have some experience with master-master and maatkit setup, so I was going to duplicate the site in a 2nd location, use master-master replication, and validate the data with maatkit and monitoring slave-lag, etc.

However, in reading all the dire warnings about this setup, I'm trying to assess if this is the right move. What's an alternative architecture?

Additional question: Say you have 2 servers, one on the east coast, and one on the west coast. What's a reasonable strategy for load balancing the database for those? Would you have the west coast apache server access mysql on the east coast? Is that really an option? I thought the latencies would kill the performance...

Any thoughts? TIA Mike

Answer 1

The US is about 50ms across, so given that you're doing 40-90 mysql queries per http query you can expect a latency increase of at least 2-4.5 seconds for http queries. This is ignoring the time it'll take to transfer any large payloads. You don't want to do cross-continent mysql for this app.

Another reason to avoid cross-continent mysql queries is that the two masters may be out of sync so if you round robin queries acorss the two databases uses may see inconsistent data and get confused. However if your local database is hard down, falling back to the remote one is reasonable. You'd also want to divert traffic away from the local webserver in this case to avoid the aforementioned latency hit.

You will want to make sure that a user sticks to the same apache server so that they get a consistent view of the data. A simple way to do this is to redirect users from www.yoursite.com to server1.yoursite.com and server2.yoursite.com, making sure that all your URLs are relative. If one server goes down you can repoint DNS, and until then users will probably keep refreshing www.yoursite.com (which would be served as a DNS roundrobin) until they get the other server. The one danger with this is that users will bookmark server1.yoursite.com.

Answer 2

Guaranteed uptime can be done using active standby approach using virtual IP at the same site.You can even have different site configured as active standby as well. Geo redundancy is another issue.