List:Replication« Previous MessageNext Message »
From:Rick James Date:March 20 2008 9:04pm
Subject:RE: Basic questions on slave servers
View as plain text  
below... 

> -----Original Message-----
> From: Eric Frazier [mailto:efrazier@stripped] 
> Sent: Thursday, March 20, 2008 1:02 PM
> To: Rick James
> Cc: Andy Smith; replication@stripped
> Subject: Re: Basic questions on slave servers
> 
> Rick James wrote:
> > * Write to only one of the Masters -- this eliminates various
> > auto-increment issues, etc.  Also, writing to both masters 
> buys nothing,
> > certainly not write-scalability since all writes have to be 
> performed on
> > both machines.
> >   
> It does indeed buy you something. If your masters are in 
> "geographicly 
> diverse locations"  Take 90ms latency to a webserver vs .2ms over the 
> course of a few million connections.

In one case, we chose to send the API request to the colo where the
master is.

Usually the one or two INSERTs can afford the 70ms delay we see between
coasts.

> > * Do not attempt to deploy automatic failover -- there can 
> be partial
> > network outages that will fool the automatic thingie into thinking a
> > Master is down.  You can end up with 2 masters being 
> written to; this
> > will hopelessly corrupt the data.
> >   
> Little bit of a bold statement. Given your failover script, 
> what caused 
> the problem exactly? How could you have fixed things to 
> prevent the dual 
> write? How was a dual write even possible according to your DB scheme?

More details on the bad case:
* Colo where Master is goes dark, but it is actually because of a
network glitch.
* Clients within that colo were happily writing to the Master.
* Failover script (not automated), alert us that the "master seems to be
dead".
* Manual fishing around discovered what the case really was, so we held
off on failing over.

Alas, that led to a "partial outage" for a couple hours while the
network was repaired.  But that was better than having two writable
masters.

Note: I could not reach the Master to determine its state, nor to shut
it down.

> > * If you scale reads by having slaves, some should hang off 
> each Master.
> > This way, half your slaves are still up-to-date if one 
> (either) master
> > dies.
> >   
> Good advice I think.
> 
> > * There are rare cases where "one" item in the replication stream
> > between the Masters will get lost in a hard crash.
> >
> > * Put your Masters in two geographically separate places -- why have
> > dual-master if a power failure (earthquake, etc) can still 
> bring down
> > the whole system?
> >
> > * To be doubly safe, set the backup master to read_only.
> >   
> 
> What about sync_binlog=1 ?

New feature; haven't tried it yet.  But with our Masters 70ms apart, and
avg of 150 updates/sec, it might not be viable?

> > * Replication items will not "loop" -- the server_id of the original
> > sender is noticed, thereby allowing the items to be tossed.
> >   
> No one should think of using autoinc keys in tables unless 
> they want to 
> run into larger problems as they scale, at least not with 
> MySQL. Having 
> said that, auto_increment_increment, auto_increment_offset   allow 
> screwed up DBs to still work with master - master. I use 
> those because 
> those choices are not always mine.

Yeah, I leave those tricks as a last resort.

> Thanks,
> 
> Eric
> 
> >   
> >> -----Original Message-----
> >> From: Andy Smith [mailto:a.smith@stripped] 
> >> Sent: Thursday, March 20, 2008 10:52 AM
> >> To: replication@stripped
> >> Subject: Re: Basic questions on slave servers
> >>
> >> Hi Martin,
> >>
> >>   ok thanks, so the next questions that come to mind are:
> >>
> >> You say in the past its not been recommended, that implies 
> >> that the situation has now changed and that
> >> this is seen as a stable configuration (production ready or 
> >> however u want to call it). Would that be correct?
> >>
> >> Good to hear your working on some complete documentation! In 
> >> the mean time, can you point me at any
> >> useful information regarding this?? How it handles 
> >> replication WRT writes to multiple hosts etc?
> >>
> >> If I just need HA and in practice I am never going to be 
> >> doing writes to both masters at the same time is this
> >> already a fairly robust solution for my needs? Ie perhaps I 
> >> will have a floating IP which Ill move from one system
> >> to the other in the event of any issues on the primary master,
> >>
> >> thanks for your help! Andy.
> >> There are two reasons for this:
> >>
> >> 1) It's not something that in the past we have recommended as a  
> >> solution because of the potential problems (overwriting 
> IDs, getting  
> >> DBs out of sync, etc).
> >>
> >> 2) We did have, incomplete, information and advice in the manual,  
> >> which was removed to be part of a new topology chapter. This new  
> >> chapter is currently being written :)
> >>
> >>     
> >
> >   
> 
> 
> -- 
> Eric Frazier
> Holistis.com
> 216 Main Street, Suite 312   
> Bathurst, NB, E2A 1A8         
> Canada     
> Phone  : +1 506 546 8292  ext 3303   
> Fax    : +1 506 546 8092         
> 
> 
> -- 
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:    
> http://lists.mysql.com/replication?unsub=1
> 
> 
Thread
Basic questions on slave serversAndy Smith18 Mar
  • Re: Basic questions on slave serversChris.Leeworthy18 Mar
    • Re: Basic questions on slave serversEd W23 Mar
      • Re: Basic questions on slave serversArthur Fuller23 Mar
        • Re: Basic questions on slave serversEd W23 Mar
  • RE: Basic questions on slave serversEdward F. Pauley II18 Mar
Re: Basic questions on slave serversAndy Smith18 Mar
  • RE: Basic questions on slave serversRick James19 Mar
Re: Basic questions on slave serversAndy Smith20 Mar
  • Re: Basic questions on slave serversMartin MC Brown20 Mar
Re: Basic questions on slave serversAndy Smith20 Mar
  • Re: Basic questions on slave serversMarcus Bointon20 Mar
  • RE: Basic questions on slave serversRick James20 Mar
    • Re: Basic questions on slave serversEric Frazier20 Mar
      • RE: Basic questions on slave serversRick James20 Mar
        • Re: Basic questions on slave serversMoon's Father21 Mar
Re: Basic questions on slave serversAndy Smith21 Mar