List:Replication« Previous MessageNext Message »
From:Rick James Date:April 23 2012 6:36pm
Subject:RE: tracing cause of lag
View as plain text  
Slave lag can be caused by

* A slow query sent through replication (eg, ALTER or UPDATE on big table).  Subsequent commands are delayed.  SHOW PROCESSLIST _during_ the event will spot that.

* Multiple writes happening in parallel on the Master, but serialized by Replication.  SHOW PROCESSLIST helps, but you need to understand your app to see this.

* The SLAVE having been STOPped (for any reason).  Catchup will take some time.

* A hung query.  SHOW SLAVE STATUS (on slave) -- Normally you will see a pair of "Yes" items.  If not, look around for the cause.

* The Slave hardware being slower than the Master.  When replication is busy, even without multi-threading on the Master, this can lead to the Slave getting behind.

* Big SELECTs on the Slave locking tables (or rows), thereby blocking (delaying) replication.  SHOW PROCESSLIST.

* etc. 

> -----Original Message-----
> From: Stanisław Stadnicki [mailto:stadnicki@stripped]
> Sent: Sunday, April 22, 2012 12:06 AM
> To: ron ramos
> Cc: replication@stripped
> Subject: Re: tracing cause of lag
> 
> Hi,
> Try to run "show full processlist" and check the time of currently
> executed processes.
> 
> Regards
> Stan
> 
> 
> 
> Dnia 22 kwi 2012 o godz. 04:53 ron ramos <nhadie@stripped> napisał(a):
> 
> > Hi,
> >
> > Would it be possible to trace which query cause the lag on the slave?
> > I have a script that runs every minute to check the lag, I basically
> > have the output of "show slave status" and timestamp and how much
> > delay: "Sun Apr 22 04:16:01 SGT 2012  -- Slave Lag: 120"
> > would this be possible? thanks in advanced!
> >
> > Regards,
> > Ron
> >
> > --
> > MySQL Replication Mailing List
> > For list archives: http://lists.mysql.com/replication
> > To unsubscribe:    http://lists.mysql.com/replication
> >
> 
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:    http://lists.mysql.com/replication

Thread
tracing cause of lagron ramos22 Apr
  • Re: tracing cause of lagStanisław Stadnicki22 Apr
    • RE: tracing cause of lagRick James23 Apr
    • Re: tracing cause of lagEber Duarte24 Apr
      • Re: tracing cause of lagron ramos24 Apr