From: Simon J Mudd
Date: January 10 2013 4:29pm
Subject: Re: Replicating on multiple Slaves DB from one Master
List-Archive: http://lists.mysql.com/replication/2413
Message-Id: <m3k3rlt317.fsf@mad06.wl0.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

rjames@stripped (Rick James) writes:

> Plan A:  Minimize I/O on Slaves:
> innodb_flush_log_at_trx_commit = 2
> sync_binlog = 0
> innodb_doublewrite = OFF
> (If a slave crashes, it may be best to reclone it rather than hope that these performance settings did not lose data.)
> 
> Plan B:  Faster Hardware on Slaves.
> 
> Plan C:  Pave the way for 5.6:
> Move tables to different databases.
> Change the code to reference the tables thus:  dbname.tblname
> 
> Plan D:  If the Slaves are I/O-bound:
> Seems like there is a script (in Python?) that peeks in the relay log, turns queries into SELECTs, does the SELECTs -- thereby priming the buffer_pool using a separate thread.

Do not forget a Plan E which may be appropriate:

Use row based replication. That often can be more efficient but
depends on whether you're inserting/updating/deleting a large number
of rows per SQL statement or not.

Also using 5.6 which is almost GA you will allow the use of
binlog_row_format = minimal which reduces I/O and the size of the binlog
considerably (or can do depending on your usage).

To reduce I/O also don't forget to batch writes with BEGIN;
... COMMIT; as this reduces I/O significantly compared to doing single
statement changes and again was not mentioned.

In any case if you are close to the edge I'd be tempted to suggest
that you split the writes out into separately replicated MySQL instances.
That's usually trivial to do and scales much better. The poster will
probably have to do that anyway if he's so close to the edge I/O wise.

Simon