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/
