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=lists@pantek.com