Master-Master replication for wide area configurations

[Michael Varlik]

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
--
GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
Jetzt dabei sein: http://www.shortview.de/?mc=sv_ext_mf@gmx

--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster?unsub=lists@...

[ewen fortune]

Hi,
I've never done this but from Johan Andersson's blog entry

http://johanandersson.blogspot.com/2008/02/mysql-cluster-features-what-they-are.html

---
Asynchronous Replication (Geo Redundancy)

Use asynchronous replication between Cluster that are geographically
separated in order to increase redundancy even more.
In CGE 6.3 it is also possible to have master-master replication with
conflict detectiona and resolution.
---
More info here:
http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-conflict-resolution.html


Ewen

On Sat, May 10, 2008 at 2:22 PM, Michael Varlik <M.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
> --
> GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
> Jetzt dabei sein: http://www.shortview.de/?mc=sv_ext_mf@gmx
>
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:    http://lists.mysql.com/cluster?unsub=ewen.fortune@...
>
>

--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster?unsub=lists@...

[Anders Karlsson-3]

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
>  

--
    __  ___     ___ ____  __
   /  |/  /_ __/ __/ __ \/ /  Anders Karlsson (anders@...)
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
 /_/  /_/\_, /___/\___\_\___/ Stockholm
        <___/   www.mysql.com Cellphone: [phone]
                              Skype: drdatabase



--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster?unsub=lists@...

[Ben Wiechman]

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.

Ben Wiechman

> -----Original Message-----
> From: Anders Karlsson [mailto:anders@...]
> Sent: Monday, May 12, 2008 1:36 AM
> To: Michael Varlik
> Cc: cluster@...
> 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
> >
>
>
> --
>     __  ___     ___ ____  __
>    /  |/  /_ __/ __/ __ \/ /  Anders Karlsson (anders@...)
>   / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
>  /_/  /_/\_, /___/\___\_\___/ Stockholm
>         <___/   www.mysql.com Cellphone: [phone]
>                      Skype: drdatabase
>
>
>
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:    http://lists.mysql.com/cluster?unsub=ben@wisper-
> wireless.com

--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster?unsub=lists@...

[Jason Snell]

I have a feeling Anders is referring to the concept of pre-heating the slave's cache using queued binlog statements.  I've implemented this in two quite useful forms.

First, write a simple program that reads binlog statements from the master, converts them to selects, and then executes the selects on the slave.  The idea here is that the selects will bring the rows to be updated into the buffers for faster lookup.

Another optimization I've discovered is on some tables in our database, the order of deletes is not important.  Therefore, I can pull deletes from the master and execute them in separate threads on the slave (yay for threaded slave replication!).  When the mysql slave thread reaches the already-executed delete, it takes far less time to determine there's nothing to delete than actually deleting the row.  It also functions as a failsafe, ensuring the row was actually deleted.  Using this simple approach, we've been able to speed up our slaves considerably.

I've also written a more complete version of this method that executes all write statements in parallel.  However, this solution is extremely application specific and highly prone to nasty replication bugs.

I did a bit of googling but was unable to come up with anything on these subjects.

Cheers,
-Jason


-----Original Message-----
From: Ben Wiechman [mailto:ben@...]
Sent: Monday, May 12, 2008 9:14 AM
To: cluster@...
Subject: RE: Master-Master replication for wide area configurations

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.

Ben Wiechman

> -----Original Message-----
> From: Anders Karlsson [mailto:anders@...]
> Sent: Monday, May 12, 2008 1:36 AM
> To: Michael Varlik
> Cc: cluster@...
> 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
> >
>
>
> --
>     __  ___     ___ ____  __
>    /  |/  /_ __/ __/ __ \/ /  Anders Karlsson (anders@...)
>   / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
>  /_/  /_/\_, /___/\___\_\___/ Stockholm
>         <___/   www.mysql.com Cellphone: [phone]
>                             Skype: drdatabase
>
>
>
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:    http://lists.mysql.com/cluster?unsub=ben@wisper-
> wireless.com

--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster?unsub=jsnell@...


--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster?unsub=lists@...

[Anders Karlsson-3]

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.
>
> Ben Wiechman
>
>
>  
>> -----Original Message-----
>> From: Anders Karlsson [mailto:anders@...]
>> Sent: Monday, May 12, 2008 1:36 AM
>> To: Michael Varlik
>> Cc: cluster@...
>> 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
>>>
>>>      
>> --
>>     __  ___     ___ ____  __
>>    /  |/  /_ __/ __/ __ \/ /  Anders Karlsson (anders@...)
>>   / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
>>  /_/  /_/\_, /___/\___\_\___/ Stockholm
>>         <___/   www.mysql.com Cellphone: [phone]
>>                      Skype: drdatabase
>>
>>
>>
>> --
>> MySQL Cluster Mailing List
>> For list archives: http://lists.mysql.com/cluster
>> To unsubscribe:    http://lists.mysql.com/cluster?unsub=ben@wisper-
>> wireless.com
>>    
>
>
>
>
>  

--
    __  ___     ___ ____  __
   /  |/  /_ __/ __/ __ \/ /  Anders Karlsson (anders@...)
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
 /_/  /_/\_, /___/\___\_\___/ Stockholm
        <___/   www.mysql.com Cellphone: [phone]
                              Skype: drdatabase



--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster?unsub=lists@...