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.
> -----Original Message-----
> From: shayne.alone@strippedom [mailto:shayne.alone@stripped]
> Sent: Monday, January 07, 2013 12:29 PM
> To: replication@stripped
> Subject: Replicating on multiple Slaves DB from one Master
> I have been faced with a case of replication which may most of you had
> been faced before...
> I did some checks and test to find out the ways to solve, but I'm not
> pretty sure about the cons and pron.
> problems is as fallow:
> Single Mysql with about ~30K QPS, mainly just work as AAA data back
> not all but lots of these queries are writes (INSERT/UPDATE).
> the matter is that when the slave wants to execute Transactions with
> one thread! this will lead to a lots of replication delay...
> I'm looking for a way to rewrite DB user for queries depend on table
> name and not just rename whole of statements.
> is such away! i hope be able to use Multi thread replication ability of
> mysql5.6 for a single DB and multiple independent tables.
> Ali R. Taleghani <a.taleghani@stripped>