What are the limits on the amount of slaves per master?

Question

I currently have a Master with 2 slaves, all running MySql 5.5.

What are the limitations on the amount of slaves I can connect to a single master? what parameters should be taken into affect?

Answer 1

There are no limits in terms of settings, but you need to be cognizant of some aspects.

You could use semisynchrnous replication as it uses a nice algorithm for shipping SQL to Slaves by making sure at least one server has all the latest SQL in the Slaves relay logs. (See my August 5, 2011 post Is MySQL Replication Affected by a High-Latency Interconnect?)

However, semisynchronous replication is only good for one out of N Slaves.

Whether you have semisynchronous replication or not, it would suffice to say that the more Slaves you have, the more CPU processing a Master has to do to keep its Slaves up-to-date.

For a Master with N Slaves, that's...

• N SQL Statements to send to Slave
• N Acknowledgments from Slaves 1-N that SQL was Executed

With semisynchrnous replication, it is not much better because For a Master with N Slaves, that's ...

• N SQL Statements to send to Slave
• Acknowledgments from Slave
  • 1 Acknowledgment that a Slave 1 received the latest SQL
  • N-1 Acknowledgments from Slaves 2-N that SQL was Executed

If you have to have many Slaves, think of using a Distribution Master in a Star Topology

• Best way to setup master to multi master replication
• Is it a good idea to split MySQL DB into two servers
• Infrastructure for Highly Concurrent, High Write DB
• https://stackoverflow.com/a/5784578/491757

If you cannot setup a Distribution Master in a Star Topology, then here is my simple rule: Use Semisynchrnous Replication but use as few Slaves as possible.

Answer 2

There is no hard limit to the number of slaves you can attach to a master, but there is a practical limit and it's based on your workload and hardware. Let's assume that you have a "bunch" of identical machines that you want to stick together in a star topology (1 master, lotsa slaves). In this hypothetical cluster, let's assume that your master became overworked because it reached its maximum capacity of 2000 operations per second where 1000 of them are writes and 1000 are reads (for simplicity, we'll assume that 1 read and 1 write have an equivalent "cost").

So, we add a slave and move all the reads off to the slave. In practice, it is impossible to move all reads to a slave, but this is for the sake of an exercise. Remember, you can't scale writes with replication because the writes have to still happen at each node.

Master - 1000 wps/0 rps - 1000 free ops
Slave1 - 1000 wps/1000 rps - 0 free ops

Hmm...We've added a slave, but we haven't actually increased our capacity. The slave is already at maximum capacity. So, we add another slave and load balance the reads

Master - 1000 wps/0 rps - 1000 free ops
Slave1 - 1000 wps/500 rps - 500 free ops
Slave2 - 1000 wps/500 rps - 500 free ops

Now we're getting somewhere and we have a little bit of growing room. But, uh-oh, we hit the front page of slashdot, so all of a sudden our traffic doubled and now we're doing 2000 wps and 2000 rps!

Master - 2000 wps/0 rps - 0 free ops
Slave1 - 2000 wps/1000 rps - 1000 ops OVER CAPACITY!
Slave2 - 2000 wps/1000 rps - 1000 ops OVER CAPACITY!

That's ok. We'll have a few extra boxes sitting around, so we'll add some more slaves

Master - 2000 wps/0 rps - 0 free ops
Slave1 - 2000 wps/500 rps - 500 ops OVER CAPACITY!
Slave2 - 2000 wps/500 rps - 500 ops OVER CAPACITY!
Slave3 - 2000 wps/500 rps - 500 ops OVER CAPACITY!
Slave4 - 2000 wps/500 rps - 500 ops OVER CAPACITY!

Uh, oh. No matter what we do, we can't improve this situation without either sharding to reduce the writes or by scaling ALL of the servers up so they have a greater capacity for operations per second.

Another consideration is that your master is taking in multiple reads concurrently, but they are serialized into the binary log and then played back in a single thread on the slave. So, while you may be able to keep up with 1000 wps on the master, the slave might only be able to handle 250wps without getting seriously lagged behind. This means that in high-write environments, the slaves may actually need to be more powerful (in terms of CPU and I/O capacity) than the master in order to keep up.

Answer 3

I deal with systems with more than a dozen Slaves hanging off a Master. No problem.

The overhead on the Master is very low -- shove stuff out a socket (per Slave).

It is possible to "fan out", but the "relays" become "single points of failure". That is, the Master could send to a few Relays (both Slave and master), each of which sends to a few Slaves.

Move most reads to the Slaves. This is "read scaling". This will help offload the Master.

Write scaling requires Sharding. (Aaron alludes to this.)

"Dual Master" is two machines that are both Master and Slave to each other. It is handy for failover, but it is not useful for write scaling. Do not write to both machines in a dual-master setup; you are asking for trouble. Instead, have the reads go to the 'backup master'.

Comment on Aaron's numbers -- The master rarely has "0 rps". There are usually a few reads that are necessary to prep for the writes. These are called "critical reads" they cannot be moved to the Slave(s).