To replicate, or not to replicate

Question

1.Intro

Hello, I am running web application on 2 separate geographical locations(e.g. Europe, Hong Kong). Let's suppose Hong Kong server is an Amazon EC2 instance(paying for disk space, IO disk operations and traffic). Inspect the following figure:

2.Problem

When you should remotely access DB server A and when you should add DB server B and replicate in between these 2 DB servers(MySQL master-master) ?

3.Additional questions

The cost of either approach can be easily calculated. I just need to "load test" the following variables for particular web application:

-replication traffic/db access traffic

-disk operations

-disk space

-num of users(load test)

Q: What's the best way to get the replication traffic ? Is there any good replication monitoring software or I should stick with wireshark, collectd ?

Q: Are there any good blogs, papers, articles writing about similar topic ?

Thank you :)

Answer 1

Use maatkit heartbeat. You will need another perl process running is very helpful.
You can also use mk-slave-delay to generate lag. Protection purposes.

Answer 2

Replication is always tricky. If you can, avoid it as long as possible.

You have to keep in mind that you have to setup a multimaster cluster which complicates things a lot. We've tried mysql multimaster setup several times but under load the databases got async - and that's the real horror.

Also have a look at the CAP theorem - you have to decide, which of the properties you can drop.

An alternative approach maybe could be: Move the web server of site B to europe or move the web server of site A to hong kong -> Plus: The amount of traffic will be restricted only to web traffic between site and b