Re: Master-Master replication for wide area configurations - Anders Karlsson - com.mysql.lists.cluster

6 messages in com.mysql.lists.clusterRe: Master-Master replication for wid...

From	Sent On	Attachments
Michael Varlik	10 May 2008 05:21
ewen fortune	10 May 2008 09:09
Anders Karlsson	11 May 2008 23:35
Ben Wiechman	12 May 2008 09:13
Jason Snell	12 May 2008 09:43
Anders Karlsson	12 May 2008 10:05

Subject:	Re: Master-Master replication for wide area configurations
From:	Anders Karlsson (and...@mysql.com)
Date:	05/12/2008 10:05:31 AM
List:	com.mysql.lists.cluster

Actually, I seem to have lost track of this. Anyway, the principle is simple, but effective:

The relay log keeps a queue of DML to be applied, INSERT, UPDATE, DELETE etc. A script is in place that reads upcoming UPDATE and DELETE DML statements, convert the WHERE clause into a SELECT and issues that SELECT. Once the UPDATE / DELETE is to be applied, the data is already in the cache, speeding up the read + write cycle of UPDATE and DELETE.

This can speed up performance of the SLAVE quite significantly in some situations.

/Karlsson Ben Wiechman wrote:

Anders,

Do you have a reference for the look ahead replication? Or a hint where to start? Maybe my google skills just aren't working well today but I'm not pulling up anything of interest in my searches so any kind of nudge in the general vicinity would be awesome.

-----Original Message----- From: Anders Karlsson [mailto:and...@mysql.com] Sent: Monday, May 12, 2008 1:36 AM To: Michael Varlik Cc: clus...@lists.mysql.com Subject: Re: Master-Master replication for wide area configurations

Michael!

The answer is probably no. What Master-Master is about is replication with conflict detection / resolution. This is in CGE to allow several MySQL Cluster setup to replicate between them. The mechanism is the same MySQL Replication you are already using, baically, but with some features being added. It is still asynchronous though. In your case I'd think hard about if I really want synchronous replication in a WAN. If you want real-time (I assume here that you want synchronous), you should think hard about the consequences of this. It will slow down writes quite significantly, due to network latency (async is more bandwidth-hungry, sync more latency so) and the added complexity (distributed locking etc), which will also increase network traffic. There are a few other options though. One is the semi-sync replication as documented here: http://code.google.com/p/google-mysql-tools/wiki/SemiSyncReplicationDesign another is using the replication slave look-ahead, which will speed up replication (Google for this guy). The Master-Master replication setup will of course still do you some good. It will allow you to catch replication conflicts and handle them, instead of the replication system stopping or some other ill effect of the "blind" replication as in "normal" MySQL replication. This said, CGE is mainly a Cluster release, even though other storage engines are part of it. For this reason, if I was in your shoes, I'd go with MySQL replication and doing what I can to speed up replication performance, the relay log lookahead being particularily interesting, as it does not require any changes to the MySQL core.

Cheers /Karlsson Michael Varlik wrote:

Hi,

I have several sites which are connected via wide area networks. Each of these sites has one MySQL database which serves it's local clients. But the data from one single site has to be available to all other sides. If possible in realtime. First, I thought about setting up a cluster with data nodes spread across all sites but this doesn't seem to be a good choice, isn't it? Now I've read about master-master replication of MySQL Cluster CGE.

What exactly is master-master replication and can it be a suitable technology to replicate data between several sites close to realtime?

Many thanks in advance for your sugestions. Best regards, Michael