List:Replication« Previous MessageNext Message »
From:Marcus Bointon Date:November 21 2007 3:30pm
Subject:Re: Multi Master Setup
View as plain text  
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/


Thread
Multi Master SetupChristian Schramm21 Nov
  • Re: Multi Master SetupMarcus Bointon21 Nov
    • Re: Multi Master SetupChristian Schramm21 Nov
      • Re: Multi Master SetupMarcus Bointon21 Nov
  • Re: Multi Master SetupFMGreen5 Dec
    • Re: Multi Master SetupChristian Schramm5 Dec
      • RE: Multi Master SetupRick James18 Dec