Re: [REVIEW] pg_last_xact_insert_timestamp

On Sat, Dec 10, 2011 at 7:29 AM, Greg Smith <greg@2ndquadrant.com> wrote:
> -It adds overhead at every commit, even for people who aren't using it.
>  Probably not enough to matter, but it's yet another thing going through the
> often maligned as too heavy pgstat system, often.

The bit about the pgstat system being too heavy is a red herring; this
could equally well be stored in the PGPROC.  So, the overhead is
basically one additional store to shared memory per commit, and we can
arrange for that store to involve a cache line that has to be touched
anyway (by ProcArrayEndTransaction).  I suppose you can make the
argument that that still isn't free, but there aren't very many places
in the code where we worry about effects this small.  Operations like
AcceptInvalidationMessages() or LockRelationOid() happen more often
than transaction commit, and yet we were happy for years with a system
where AcceptInvalidationMessages() did three spinlock
acquire-and-release cycles that were unnecessary in 99% of cases.
That cost was vastly greater than what we're talking about here, and
it affected an operation that is more frequent than this one.

> [ usability complaints ]

Fair enough.

> I'm normally a fan of building the simplest thing that will do something
> useful, and this patch succeeds there.  But the best data to collect needs
> to have a timestamp that keeps moving forward in a way that correlates
> reasonably well to the system WAL load.  Using the transaction ID doesn't do
> that.  Simon did some hand waving around sending a timestamp every
> checkpoint.  That would allow the standby to compute its own lag, limit
> overhead to something much lighter than per-transaction, and better track
> write volume.  There could still be a bigger than normal discontinuity after
> server restart, if the server was down a while, but at least there wouldn't
> ever be a point where the value was returned by the master but was NULL.
>
> But as Simon mentioned in passing, it will bloat the WAL streams for
> everyone.

But, as with this proposal, the overhead seems largely irrelevant; the
question is whether it actually solves the problem.  Unless I am
misunderstanding, we are talking about 4 bytes of WAL per checkpoint
cycle, which strikes me as even less worth worrying about than one
store to shared memory per transaction commit.  So, personally, I see
no reason to fret about the overhead.  But I'm skeptical that anything
that we only update once per checkpoint cycle will help much in
calculating an accurate lag value.  It also strikes me that anything
that is based on augmenting the walsender/walreceiver protocol leaves
anyone who is using WAL shipping out in the cold.  I'm not clear from
the comments you or Simon have made how important you think that use
case still is.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company