Re: Calculating Replication Lag - units

On Tue, Jun 26, 2012 at 1:47 AM, David Kerr <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.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/