How to keep multiple read/write DB servers in sync?

Question

Hello

I'm curious to know how big sites spread the load between the different DB server in the case where users write as much as they read, ie. when the standard solution of having one master to accept write, and several slaves that only let users read data doesn't work because it simply turns the master server into the bottleneck.

For those of you who manage a big site with a load balancer -> multiple web servers -> multiple DB servers, how do you spread the load evenly between the DB servers so that users (at best) don't have to wait for the master to update the slaves, or (at worst) users end up reading dirty data from slaves that haven't been updated yet?

Thank you.

Answer 1

We're not quite at the point where our master database server is the bottleneck but it is approaching quickly. We have a 70% read/30% write split.

There are several solutions, none of them much fun. We're stuck with MySQL so our main options are:

• Throw hardware at the problem. This is the strategy we've followed for many years and it has served us well so far. Unfortunately, our growth curve means that we won't get away with this for much longer.
• A multiple master MySQL setup. Have a look at "Replication Topologies" in Chapter 8 of High Performance MySQL to see what I'm talking about. As explained in that section, replication isn't a great option for scaling writes.
• MySQL cluster. I still need to research this properly and test it to see if we could improve our write performance.
• Patch our software to use less writes per operation. This has been partially done, but I don't know how much more we can squeeze out of it.
• Patch our software to shard the writes across multiple independent masters. This is very painful and I'd like to avoid doing it if possible.
• Patch our software to be less MySQL-dependent and find another SQL database which gives us better write-scaling options.

They're the options I have at the moment, but if you're working on a different database or if you're starting out and can choose, your options may be radically different.