Re: Three Node Replication (US West, US East and Europe)

[Rick James]

Most databases are read more than written. So, if most of the reads hit slaves, there is less load on the Master. I know of cases with a single writable master plus 20-30 slaves scattered around the world.

"Reports" should hit Slaves.

"Viewing" things should hit the slaves.

There is no magical way to split your reads and writes; any attempt at automating such will lead to problems, such as "critical read". Example:
1. a user posts a blog comment (to the Master)
2. the UI immediately goes to the page showing all the postings for that user (using a nearby slave)
3. BUT, due to replication issues, his posting is not yet on the Slave.

There are many ways to solve (or lessen) the problem:
* Changes to the UI to avoid situations that would make the user think his posting was lost.
* Use a heartbeat to see if the Slave is caught up.
* Send critical reads (but not other reads) to the Master.

Bottom line: The application needs to be modified to do the read-write split "correctly".