How to Improve MySQL Replication Performance
As the number of slaves connecting to a master increases, the load, although minimal, also increases, as each slave uses up 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 start to 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 scaleout 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
FigureÂ 16.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 allowed 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 is really the data 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 then there are a number of options available:
- If possible, you should 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, then you may want to divide up the responsibility for replicating different databases to different slaves.Â
- If your master makes use of transactions and you are not concerned about transaction support on your slaves, then useÂ
MyISAMÂ or another non-transactional engine.Â
- 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Â –log-slave-updates. This prevents ‘dumb’ slaves from also logging events they have executed into their own binary log.