On 21 Nov 2007, at 14:30, Christian Schramm wrote:
> Of course you may get problems with duplicate entries and depending
> on the server performance also a synchronisation lag.
>
> Does anyone in the list have experience with multi master setups
> like this?
> Any recommendations?
Yes. I do this in several installations. This kind of replication is
not about scaling, it's about redundancy. You gain nothing by being
able to write to two servers because all masters have to execute the
same queries anyway, in fact it will actually cost you slightly in
performance.
So, don't write to your secondary master, and don't do critical (e.g.
within transactions) reads from it either, that way your data will
stay sane. Propagation delay is very, very small for small operations
(microseconds), but if you post a big transaction, it may cause the
secondary to fall several minutes behind (depending on transaction
size). A classic example might be to to do a million inserts in a
transaction, then immediately try to read one of them back form the
secondary - it won't be there.
Note the new replication features in 5.1. Row based replication is
important - imagine you have a statement like:
delete from mytable order by rand() limit 50;
This can delete different records on each master! 5.1 fixes that.
5.1's partitioning CAN give you big improvements in write speed.
Multi-master really helps when it comes to failover - you just point
your clients at the new master, and that's it. No reconfiguration of
slaves required. When you fix the original master, start it up and it
will catch up to the new master, at which point it can safely become
the new failover target.
> I want to realize the current project i'm working on with
> replication because the database is not optimized to run e.g. on a
> ndbcluster.
NDBCluster solves a different problem - and you can't have
transactions or foreign key support on it.
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/
