Skip site navigation (1) Skip section navigation (2)

Re: pg_last_xact_insert_timestamp

From: Chris Redekop <chris(at)replicon(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Fujii Masao <masao(dot)fujii(at)gmail(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_last_xact_insert_timestamp
Date: 2011-09-08 22:33:39
Message-ID: CAC2SuRJ1ViWqi_pg6tjRZKGC6-5=odaDyzKG3m_NwZGuB6FVtw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Thanks for all the feedback guys.  Just to throw another monkey wrench in
here - I've been playing with Simon's proposed solution of returning 0 when
the WAL positions match, and I've come to the realizatiion that even if
using pg_last_xact_insert_timestamp, although it would help, we still
wouldn't be able to get a 100% accurate "how far behind?" counter....not
that this is a big deal, but I know my ops team is going to bitch to me
about it :).....take this situation: there's a lull of 30 seconds where
there are no transactions committed on the server....the slave is totally
caught up, WAL positions match, I'm reporting 0, everything is happy.  Then
a transaction is committed on the master....before the slave gets it my
query hits it and sees that we're 30 seconds behind (when in reality we're
<1sec behind).....Because of this affect my graph is a little spikey...I
mean it's not a huge deal or anything - I can put some sanity checking in my
number reporting ("if 1 second ago you were 0 seconds behind, you can't be
more than 1 second behind now" sorta thing).  But if we wanted to go for
super-ideal solution there would be a way to get the timestamp of
pg_stat_replication.replay_location+1 (the first transaction that the slave
does not have).


On Thu, Sep 8, 2011 at 7:03 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, Sep 8, 2011 at 6:14 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> > OTOH, new function enables users to monitor the delay as a timestamp.
> > For users, a timestamp is obviously easier to handle than LSN, and the
> delay
> > as a timestamp is more intuitive. So, I think that it's worth adding
> > something like pg_last_xact_insert_timestamp into core for improvement
> > of user-friendness.
>
> It seems very nice from a usability point of view, but I have to agree
> with Simon's concern about performance.  Actually, as of today,
> WALInsertLock is such a gigantic bottleneck that I suspect the
> overhead of this additional bookkeeping would be completely
> unnoticeable.  But I'm still reluctant to add more centralized
> spinlocks that everyone has to fight over, having recently put a lot
> of effort into getting rid of some of the ones we've traditionally
> had.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

In response to

pgsql-hackers by date

Next:From: George BarnettDate: 2011-09-09 00:04:58
Subject: Patch to improve reliability of postgresql on linux nfs
Previous:From: Josh BerkusDate: 2011-09-08 21:59:45
Subject: Re: Large C files

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group