List:Replication« Previous MessageNext Message »
From:Rick James Date:December 18 2007 5:00pm
Subject:RE: Multi Master Setup
View as plain text  
Don't automatically split reads from writes -- there will be times when you need
consistency, and that might be thwarted by a replication delay.  Instead, split the
connects from your app into readwrite vs readonly.  Readonly connections would be those
that are searching for tickets, reporting stats, etc.

When inserting a new ticket, it must go to the readwrite master.  Furthermore, right after
you insert the ticket, you probably want to see it.  This requires continuing to hit the
master.  One way to do this (if going thru a web service) is to use cookies.  Another way
is some form of "session" management.

It is quite annoying for someone to add a comment to a ticket, then find that it seems to
be missing.

Summary:
* Write to only one master, even in a dual-master setup.
* Stick with the master when consistency is desired.
* Send readonly searches, reports, etc to slave(s).
* Scale the readonly traffic by adding more slaves.
* No simple way to scale or load-balance the writes.


> -----Original Message-----
> From: Christian Schramm [mailto:christian@stripped] 
> Sent: Wednesday, December 05, 2007 5:27 AM
> To: replication@stripped; FMGreen
> Subject: Re: Multi Master Setup
> 
> Thanks.
> Meanwhile i've setup a master-master replication but i'm 
> currently using 
> only one really as master.
> That means i'm having the same databases on both servers, but i'm 
> sending statements only to server A, which then replicates 
> them to server B.
> I'm considering testing the mysql proxy which can do 
> read/write splitting.
> 
> Mit freundlichen Grüßen / Kind regards
> Christian Schramm
> <http://www.cplinux.de>
> 
> 
> FMGreen schrieb:
> > Does 'run away' count as a recommendation? ;o)
> >
> > I have master<->master replication set up (it's for a 
> load-balanced helpdesk
> > system) and am currently trying to change back to 
> master<->slave.  When it
> > worked it was fine, but when it broke it caused chaos.  The 
> reason is that
> > when replication broke (due to a duplicate db entry) it 
> took us two days to
> > realise what was going on.  Both dbs were acting as 
> 'masters' and processing
> > data individually so it wasn't immediately obvious there 
> was a problem.  We
> > ended up with helpdesk tickets that had the same ticket 
> number but different
> > contents on each server.  So when we decided to restore we 
> had to pick one
> > db and lose the data from the other.  We're still having 
> lots of problems. 
> > Unless you have a way of monitoring when replication goes down then
> > master<->master has the potential a create a real mess.  
> I'm very new to
> > mysql but that's been my experience.
> >
> >
> > Christian Schramm-5 wrote:
> >   
> >> Hi @all,
> >>
> >> I've created a simple setup with 2 servers, each acting as 
> master and as 
> >> slave.
> >> So it does not matter on which server you will write.
> >>
> >> 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?
> >>
> >> 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.
> >> -- 
> >> Mit freundlichen Grüßen / Kind regards
> >> Christian Schramm
> >> <http://www.cplinux.de>
> >>
> >>
> >>     
> >
> >   
> 
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