Re: performance increase for massive write and read operations?

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.

-- 
Augusto Bott

On 10/30/07, Rick James  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
> right.)
>
> > -----Original Message-----
> > From: Marcus Bointon [mailto:marcus@synchromedia.co.uk]
> > Sent: Tuesday, October 30, 2007 2:16 AM
> > To: Tim Stoop
> > Cc: replication@lists.mysql.com
> > 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(at)synchromedia.co.uk | 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=rjames@yahoo-inc.com
> >
> >
>
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:    http://lists.mysql.com/replication?unsub=augusto.bott@gmail.com
>
>


-- 
MySQL Replication Mailing List
For list archives: 
http://lists.mysql.com/replication
To unsubscribe:    
http://lists.mysql.com/replication?unsub=lists@pantek.com