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
