Also, keep in mind that replication is serialized: any number of
inserts/updates/deletes may commit at the same time on the master, but
they will be serialized on the binary log.
I don't know if that's possible to you, but you could try having
partial slaves replicating only a few tables needed (for specific
parts of your application). If a slave needs to replicate less
statements/tables, that usually means more resources are available for
doing other things on the server.
On 10/30/07, Rick James <rjames@stripped> wrote:
> In some applications, one has to do a number of SELECTs to decide what
> to INSERT. For consistency (as mentioned below), these SELECTs must be
> done on the Master. But SELECTs are not replicated. That is, in this
> case the "insert" (which includes the SELECTs) is more costly on the
> master than on the slave.
> At the moment, I am seeing 140 SELECTs/sec on my master and 30/sec on
> each slave. The Master is not configured to receive any readonly
> traffic, only the Slaves. (Total on Master = 352/sec averaged over the
> 147 days since it started. About 200 queries/sec are being replicated,
> hence executed on Master and Slaves. Oops; these figures may not be
> right, it has probably wrapped 32-bit ints. But the principle is
> > -----Original Message-----
> > From: Marcus Bointon [mailto:marcus@stripped]
> > Sent: Tuesday, October 30, 2007 2:16 AM
> > To: Tim Stoop
> > Cc: replication@stripped
> > Subject: Re: performance increase for massive write and read
> > operations?
> > On 30 Oct 2007, at 08:18, Tim Stoop wrote:
> > > I was wondering, will a Master-Slave setup increase
> > performance (apart
> > > from the obvious extra proc and mem available) in a setup
> > where there
> > > are a lot of INSERTs on the master and a lot of SELECTs on
> > the slave?
> > > Like, the replication itself, is it in some way optimised so it
> > > doesn't take as much time on the slave as it does on the
> > master? Both
> > > the INSERTs and the SELECTs aren't very complicated.
> > Replication buys you nothing in write performance - and can actually
> > be slightly worse as all inserts/updates/deletes have to be
> > run on all
> > nodes. Read performance can improve dramatically if you spread your
> > reads across the available master and slaves, however, you risk
> > transactional integrity by doing this - if you do a
> > transaction with a
> > large number of inserts then immediately read from a slave, you may
> > not get what you just wrote because the transaction has to
> > complete on
> > the master before it gets replicated. Though the docs say that this
> > delay is negligible, I've had it cause problems in real apps.
> > e.g. insert into tablea values(...) (on master)
> > select count(*) from tablea (on slave)
> > The second query may give varying results depending on how big the
> > first query is.
> > I mainly use replication to give redundancy rather than
> > speed. If you
> > want real speed boosts, you need to look at partitioning and better
> > abstraction, perhaps using something like Continuent's Sequoia.
> > Marcus
> > --
> > Marcus Bointon
> > Synchromedia Limited: Creators of http://www.smartmessages.net/
> > UK resellers of info@hand CRM solutions
> > marcus@stripped | http://www.synchromedia.co.uk/
> > --
> > MySQL Replication Mailing List
> > For list archives: http://lists.mysql.com/replication
> > To unsubscribe:
> > http://lists.mysql.com/replication?unsub=1
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe: http://lists.mysql.com/replication?unsub=1