Re: Calculating Replication Lag - units

On Tue, Jun 26, 2012 at 09:13:44AM -0700, Steve Crawford wrote:
- On 06/26/2012 08:16 AM, David Kerr wrote:
- >On 06/26/2012 05:11 AM, Stuart Bishop wrote:
- >>On Tue, Jun 26, 2012 at 6:21 AM, David Kerr<dmk@mr-paradox.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!
- >
- >
- But beware. If your master server has any idle time you may be misled by
- the simple calculation. I was running a pgbench test on a replicated
- pair of machines. It finished this morning sometime so the lag delay
- shows 02:31:11.651118, a value that might set off alarm bells. That's
- why I used the case statement to force the interval to 0 if the replay
- is up-to-date.
-
- I think it is still worthwhile to check pg_stat_replication on the
- master to make sure that it is still *sending* logs and perhaps
- cross-checking the current log position on the master with the replayed
- log location on the standby to see if they are reasonably close.
-
- An additional verification check I've toyed with is to have a cron
- script on the master update a one-row one-column table that holds a
- timestamp and checking that timestamp on the standby(s) to double-check
- that it is not too-far out-of-date. (This would also force regular data
- delivery to the standby so that pg_last_xact_replay_timestamp() should
- not lag far behind on an otherwise idle server.)
-
- Cheers,
- Steve
-

I see, thanks. I was completely missing that you were using different functions
than me and that was throwing me off. =)