From: Marcus Bointon
Date: October 30 2007 9:15am
Subject: Re: performance increase for massive write and read operations?
List-Archive: http://lists.mysql.com/replication/968
Message-Id: <43CB86C0-90D4-4441-AC84-6F0F41EE6107@synchromedia.co.uk>
MIME-Version: 1.0 (Apple Message framework v912)
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit

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/