As the number of slaves connecting to a master increases, the load, although minimal, also increases, as each slave uses a client connection to the master. Also, as each slave must receive a full copy of the master binary log, the network load on the master may also increase and create a bottleneck.
If you are using a large number of slaves connected to one master, and that master is also busy processing requests (for example, as part of a scale-out solution), then you may want to improve the performance of the replication process.
One way to improve the performance of the replication process is to create a deeper replication structure that enables the master to replicate to only one slave, and for the remaining slaves to connect to this primary slave for their individual replication requirements. A sample of this structure is shown in Figure 17.3, “Using an Additional Replication Host to Improve Performance”.
For this to work, you must configure the MySQL instances as follows:
Master 1 is the primary master where all changes and updates are written to the database. Binary logging should be enabled on this machine.
Master 2 is the slave to the Master 1 that provides the
replication functionality to the remainder of the slaves in
the replication structure. Master 2 is the only machine
permitted to connect to Master 1. Master 2 also has binary
logging enabled, and the
--log-slave-updates option so
that replication instructions from Master 1 are also written
to Master 2's binary log so that they can then be replicated
to the true slaves.
Slave 1, Slave 2, and Slave 3 act as slaves to Master 2, and replicate the information from Master 2, which actually consists of the upgrades logged on Master 1.
The above solution reduces the client load and the network interface load on the primary master, which should improve the overall performance of the primary master when used as a direct database solution.
If your slaves are having trouble keeping up with the replication process on the master, there are a number of options available:
If possible, put the relay logs and the data files on
different physical drives. To do this, use the
--relay-log option to specify
the location of the relay log.
If the slaves are significantly slower than the master, you may want to divide up the responsibility for replicating different databases to different slaves. See Section 17.3.4, “Replicating Different Databases to Different Slaves”.
If your master makes use of transactions and you are not
concerned about transaction support on your slaves, use
MyISAM or another nontransactional engine
on the slaves. See
Section 17.3.2, “Using Replication with Different Master and Slave Storage Engines”.
If your slaves are not acting as masters, and you have a
potential solution in place to ensure that you can bring up a
master in the event of failure, then you can switch off
prevents “dumb” slaves from also logging events
they have executed into their own binary log.