From: | Stuart Bishop <stuart(at)stuartbishop(dot)net> |
---|---|
To: | David Kerr <dmk(at)mr-paradox(dot)net> |
Cc: | Steve Crawford <scrawford(at)pinpointresearch(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Calculating Replication Lag - units |
Date: | 2012-06-26 12:11:56 |
Message-ID: | CADmi=6NDxC8_CQW_ToqB4k4AkcA6EqewBT2eofdEDUWZDy7uQw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Jun 26, 2012 at 6:21 AM, David Kerr <dmk(at)mr-paradox(dot)net> wrote:
> On Mon, Jun 25, 2012 at 02:17:22PM -0700, Steve Crawford wrote:
> - On 06/25/2012 01:17 PM, David Kerr 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?
> - >
> - How about now()-pg_last_xact_replay_timestamp() (however this can be a
> - large number if there have not been any recent transactions on the
> - master). I suppose you could do something like:
> -
> - case when pg_last_xlog_receive_location() =
> - pg_last_xlog_replay_location() then '0 seconds'::interval
> - else now()-pg_last_xact_replay_timestamp() end as log_delay;
>
> i don't know for sure that 111512 is a time value.. that's kind of
> what i'm wondering. If i knew that it was like miliseconds or something
> that would be helpful.
On the hot standby:
SELECT now()-pg_last_xact_replay_timestamp() AS lag;
This gives you the lag time as a PostgreSQL interval.
(It also might give you a value if you run it on a database that is
not a hot standby if it started in recovery mode).
It seems difficult or impossible to calculate this on the master.
--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/
From | Date | Subject | |
---|---|---|---|
Next Message | Stuart Bishop | 2012-06-26 12:18:57 | Re: Minimal streaming replication |
Previous Message | Jasen Betts | 2012-06-26 10:20:25 | Re: txid and current_timestamp |