List:Replication« Previous MessageNext Message »
From:Rick James Date:October 30 2007 7:44pm
Subject:RE: performance increase for massive write and read operations?
View as plain text  
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
> UK resellers of info@hand CRM solutions
> marcus@stripped |
> -- 
> MySQL Replication Mailing List
> For list archives:
> To unsubscribe:    
performance increase for massive write and read operations?Tim Stoop30 Oct
  • Re: performance increase for massive write and read operations?Marcus Bointon30 Oct
    • RE: performance increase for massive write and read operations?Rick James30 Oct
      • Re: performance increase for massive write and read operations?Augusto Bott30 Oct