From: | David Kerr <dmk(at)mr-paradox(dot)net> |
---|---|
To: | Stuart Bishop <stuart(at)stuartbishop(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 15:16:32 |
Message-ID: | 4FE9D250.4070106@mr-paradox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 06/26/2012 05:11 AM, Stuart Bishop wrote:
> 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.
>
>
Ah, awesome. I don't need to calculate it on the master so that's perfect.
Thanks!
From | Date | Subject | |
---|---|---|---|
Next Message | David Kerr | 2012-06-26 15:25:32 | Re: Calculating Replication Lag - units |
Previous Message | Albe Laurenz | 2012-06-26 13:21:37 | Re: dblink causing import errors |