From: Marcus Bointon
Date: November 21 2007 5:53pm
Subject: Re: Multi Master Setup
List-Archive: http://lists.mysql.com/replication/1003
Message-Id: <438FA5AC-54E4-4FA2-8BC0-08EDD0A5C31C@synchromedia.co.uk>
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 15:50, Christian Schramm wrote:

> Ok, that's interesting. But what if you have e.g. 4 webservers. Each  
> webserver is always writing to the same master.
> The user stays always on the same webserver.

Yes, but every master has to execute all statements issued to every  
other master too, otherwise they are not in sync, so you get no speed  
advantage.

> The inconsistency fact is currently holding me back from that!
> For the delete statement I fully agree, but I never execute  
> statements like this, except perhaps for session tables, which are  
> currently excluded from replication.

It's not just deletes - it's any write statement (insert, update,  
delete) that includes SQL functions with non-deterministic output,  
such as RAND(), NOW() and so on. Bet you do some of them ;*)

> With my Master-Master setup I wanted to spread the mySQL traffic to  
> raise the maximum number of possible connections and as you already  
> said raise performance.

With master-master or master slave, you do get to increase your  
connection count overall, particularly for reads, and you can get much  
improved performance by distributing reads.

> The second aim, of course is to have a failover.
>
> I must say that I don't know much about partitioning (yet). It  
> sounds interesting.

Partitioning is the only real way to scale writes.

> In your conf. it would be possible even with heartbeat2 and a  
> virtual IP, then it does the failover automatically and sends error  
> reportings by mail.

You can do, but with master-master it's easier to deal with failover  
in the client, saves a lot of admin hassle.

> What other possibilities do you have? You can split read/write  
> statements to different servers.


For a completely different way to implement your DB layer, take a look  
at Continuent's Sequoia: http://www.continuent.org/HomePage You can  
get all kinds of transparent failover, replication, partitioning and  
back-end independence all in one system. Helps if you have lots of  
boxes though!

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/