Master-Master or Master with Many SlavesPeter Zaitsev
I just found post by Kevin, in which he criticizes Master-Master approach, finding Master with many slaves more optimal. There is surely room for master-N-slaves systems but I find Master-Master replication much better approach in many cases.
Kevin Writes “It requires extra hardware thats sitting in standby and not being used (more money and higher capital expenditures)”, I’m surprised why would it ? Typically you can use both nodes for most of the reads, and this is in fact use pattern MMM was designed for.
“There’s only one machine to act as the standby master. If you have 10 slaves you should be able to fail five times and still be operational.” This is valid consideration but honestly for most of applications it is enough unless you’re using unreliable hardware.
Typically other risks of downtime are higher, what is about having application error which deletes/trashes all the data and gets replicated to all the slaves ?
When you compare master-master to multiple slaves you should compare it for same amount of servers. For example if we have 6 boxes we can use 1 master and 5 slaves or 3 master-master pairs. In this case each of the master-master nodes gets 1/3rd of database size and about 1/3rd of traffic.
The benefits of Master-Master replication are the following
It is more simple especially if you just write to one node fallback and recovery are rather easy. Even if all things are automated simple things mean less software bugs.
Handling write load If your application is write intensive master-N-slave configuration will be saturated much faster because it has to handle much more write load. Especially keeping into account MySQL replication is single thread it might be not long before it will be unable to keep up.
Waste of cache memory If you have same data on the slaves you will likely same data cached in their database caches. You can partially improve it by load partitioning but still it will not be perfect – for example all of write load has to go to all nodes getting appropriate data in the cache. In our example if you have 16GB boxes and say 12GB allocated to MySQL database caches you can get 12GB effective cache on the master-N-slave configuration compared to 36GB of effective cache on 3 master-master pairs.
Waste of Disk Disk is cheap but for IO bound workloads you may need fast disk array, which becomes not so cheap so having less data to deal with becomes important.
More time to clone If replication breaks you may need more time to re-clone it (or restore database from backup) compared to multiple master-master pairs.
I however agree if you have small database (compared to memory size) with insignificant write load master-N-slave configuration may be indeed better as it allows to keep application simple, having all data on the same server.
The customer for which MMM cluster was implemented has about 600GB per node and some other installations have similar sizes.
MMM Cluster support Master with multiple slaves configuration as well, this however was not the main focus.