Re: [HACKERS] Measuring replay lag - Mailing list pgsql-hackers

Hi

Just adding a couple of thoughts on this.

On 03/14/2017 08:39 AM, Thomas Munro wrote:> Hi,>> Please see separate replies to Simon and Craig below.>> On Sun, Mar
5,2017 at 8:38 PM, Simon Riggs <simon@2ndquadrant.com> wrote:>> On 1 March 2017 at 10:47, Thomas Munro
<thomas.munro@enterprisedb.com>wrote:>>> I do see why a new user trying this feature for the first time might>>> expect
itto show a lag of 0 just as soon as sent LSN =>>> write/flush/apply LSN or something like that, but after some>>>
reflectionI suspect that it isn't useful information, and it would be>>> smoke and mirrors rather than real data.>>>>
PerhapsI am misunderstanding the way it works.>>>> If the last time WAL was generated the lag was 14 secs, then
nothing>>occurs for 2 hours aftwards AND all changes have been successfully>> applied then it should not continue to
show14 secs for the next 2>> hours.>>>> IMHO the lag time should drop to zero in a reasonable time and stay at>> zero
forthose 2 hours because there is no current lag.>>>> If we want to show historical lag data, I'm supportive of the
idea,>>but we must report an accurate current value when the system is busy>> and when the system is quiet.>> Ok, I
thoughtabout this for a bit and have a new idea that I hope> will be more acceptable.  Here are the approaches
considered:

(...)> 2.  Recognise when the last reported write/flush/apply LSN from the> standby == end of WAL on the sending
server,and show lag times of> 00:00:00 in all three columns.  I consider this entirely bogus: it's> not an actual
measurementthat was ever made, and on an active system> it would flip-flop between real measurements and the
artificial>00:00:00.  I do not like this.
 

I agree with this; while initially I was expecting to see 00:00:00,
SQL NULL is definitely correct here. Anyone writing tools etc. which need to
report an actual interval can convert this to 00:00:00 easily enough .

(...)
> 5.  The new proposal:  Show only true measured write/flush/apply data,> as in 1, but with a time limit.  To avoid the
scenariowhere we show> the same times during prolonged periods of idleness, clear the numbers> like in option 3 after a
periodof idleness.  This way we avoid the> dreaded flickering/flip-flopping.  A natural time to do that is when>
wal_receiver_status_intervalexpires on idle systems and defaults to> 10 seconds.>> Done using approach 5 in the
attachedversion.  Do you think this is a> good compromise?  No bogus numbers, only true measured> write/flush/apply
times,but a time limit on 'stale' lag information.
 

This makes sense to me. I'd also add that while on production servers
it's likely there'll be enough activity to keep the columns updated,
on a quiescent test/development systems seeing a stale value looks plain
wrong (and will cause no end of questions from people asking why lag
is still showing when their system isn't doing anything).

I suggest the documentation of these columns needs to be extended to mention
that they will be NULL if no lag was measured recently, and to explain
the circumstances in which the numbers are cleared.


Regards

Ian Barwick

-- Ian Barwick                   http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services


--  Ian Barwick                   http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training &
Services