From: Marcus Bointon Date: November 21 2007 3:30pm Subject: Re: Multi Master Setup List-Archive: http://lists.mysql.com/replication/1001 Message-Id: MIME-Version: 1.0 (Apple Message framework v915) Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit 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/