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.
Adding this to twitter great info. [url=http://www.cheappinkretrojordans.com]Basketball Shoes[/url] Basketball Shoes
Howdy. Very nice site!! Guy .. Excellent .. Wonderful .. I will bookmark your web site and take the feeds also…I am glad to locate so much useful information here in the post. Thank you for sharing. [url=http://www.cheappinkretrojordans.com]Basketball Shoes[/url] Basketball Shoes
Please let me know if you’re looking for a article author for your blog. You have some really good articles and I think I would be a good asset. If you ever want to take some of the load off, I’d really like to write some material for your blog in exchange for a link back to mine. Please shoot me an e-mail if interested. Thank you! [url=http://www.authentic-jordans.com]authentic jordans[/url] authentic jordans