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(at)mr-paradox(dot)net
> <mailto:dmk(at)mr-paradox(dot)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.