List:General Discussion« Previous MessageNext Message »
From:Eric Robinson Date:January 26 2011 5:18am
Subject:RE: InnoDB and rsync
View as plain text  
> On 1/25/2011 10:45, Robinson, Eric wrote:
> >>> There is a very good reason: it is the phenomenon of row 
> drift. The 
> >>> master and slave can appear to be in good sync, but often 
> it is not 
> >>> actually the case.
> >>
> >> ... sounds interesting; have you got any document explaining this 
> >> phenomenon? AFAIK, the things that (silently) break 
> replication are:
> >> - non-deterministic functions in statement-based replication
> >> - hand-made updates on the slave db
> >> is this enough to justify a *daily* resync?!
> >
> >
> > I'm definitely no expert on this. All I know is that we used to
> > frequently experience situations where queries to the slaves would
> > return different recordsets than the same queries to the 
> masters. Yet by
> > all other indications the servers were in sync. All the replication
> > threads were running and the row counts were identical, but 
> the data in
> > the rows was sometimes different. I asked about this in the 
> list and the
> > answers I got back were that the phenomenon was called row 
> drift and was
> > fairly well known and not always easy (or sometimes even 
> possible) to
> > eliminate because of bad programming practices in some off-the-shelf
> > applications. At that time, the consensus in the list was 
> that it was
> > not safe to trust replication slaves for backup purposes. 
> That's when I
> > came up with the idea of doing an rsync every night, which creates a
> > slave that is 100% reliable for using as a backup source and also
> > eliminates problems with row-drift. Since we started using that
> > technique, we don't get calls from users complaining that 
> their reports
> > are showing bogus totals and such.
> >
> 
> I suspect that your queries were not as deterministic as you thought 
> they were. Do you have a sample of a query that produced different 
> results between the master and the slave? We shouldn't need 
> the results, 
> just the query.
> 


Sorry, no. The software is a canned medical application so we cannot
easily inspect the queries that could have been causing the problem.
Even though we could capture them in various ways (sniffer, proxy, query
logs) it would not be easy to isolate the culprits out of the tens of
thousands issued every day. And it was a year or more ago. We have not
had the problem since we started rsyncing. :-)

 


Disclaimer - January 25, 2011 
This email and any files transmitted with it are confidential and intended solely for
Shawn Green (MySQL),Mattia Merzi,Reindl Harald,mysql@stripped. If you are not the
named addressee you should not disseminate, distribute, copy or alter this email. Any
views or opinions presented in this email are solely those of the author and might not
represent those of Physicians' Managed Care or Physician Select Management. Warning:
Although Physicians' Managed Care or Physician Select Management has taken reasonable
precautions to ensure no viruses are present in this email, the company cannot accept
responsibility for any loss or damage arising from the use of this email or attachments. 
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
Thread
Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford21 Jan
  • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesReindl Harald21 Jan
  • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesMySQL)21 Jan
    • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford21 Jan
    • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesJoerg Bruehe24 Jan
      • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford24 Jan
        • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford24 Jan
          • RE: Slow query on MySQL4 server doing simple inner join of twoInnoDB tablesGavin Towey25 Jan
            • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford25 Jan
  • InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncJohan De Meersman25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncMattia Merzi25 Jan
    • Re: InnoDB and rsyncJohan De Meersman25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
      • Re: InnoDB and rsyncJohan De Meersman25 Jan
        • Re: InnoDB and rsyncReindl Harald25 Jan
        • RE: InnoDB and rsyncJerry Schwartz25 Jan
    • Re: InnoDB and rsyncSteve Musumeche25 Jan
    • Re: InnoDB and rsyncMySQL)26 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncMySQL)26 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
    • Re: InnoDB and rsyncJohan De Meersman26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncMichael Dykman28 Jan
    • Re: InnoDB and rsyncReindl Harald28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncReindl Harald28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncEric Bergen1 Feb