Re: Calculating Replication Lag - units

On 6/25/2012 9:55 PM, Raghavendra wrote:
>
> On Tue, Jun 26, 2012 at 1:47 AM, David Kerr <dmk@mr-paradox.net
> <mailto:dmk@mr-paradox.net>> wrote:
>
>     Howdy,
>
>     When calculating Replication lag, I know that we have to compare the
>     pg_current_xlog_location
>     to pg_last_xlog_receive_location, etc. but what I'm trying to figure
>     out is what are
>     the units that I'm left with after the calculation.
>
>     (i.e., does the xlog_location imply some time value?)
>
>     Here's the output of the (slightly modified script)
>     Master: 5003964876715
>     Receive: 5003964876715
>     Replay: 5003964765203
>
>     receive.value 0
>     apply.value 111512
>
>     111512 isn't inherently useful to me on its own.
>
>     Any tips?
>
> A common method I did in Oracle, I followed the same, I may be wrong in
> calculating exactly. Someone would have better solution on lag calculation.
>
> My checking goes like this, Since its streaming replication, every DML
> should be replicated as fast it could to slave.
>
> 1. Create table on master as Lagcheck(lagtime timestamp) and insert one
> row with now() (current_time of server).
> 2. every minute update the same row with latest time by putting entry in
> cronjob
> 3. Step 2 will be replicated to Slave (i.e., SR box).
> 4. Now on slave calculate the lag by now() - lagcheck.lagtime(column
> which has value of Master time).
>
> Here you get the time how much slave is behind from master.
> Note: Special attention required on Timezones.

Well, I think the way I'm doing it is the correct way, it's even that
way in Simon's book. I'm just not sure what the # is.. is it miliseconds
since last update on master. or just some arbitrary number.