Re: Hot standby and b-tree killed items - Mailing list pgsql-hackers

On Wed, 2008-12-24 at 17:56 +0530, Pavan Deolasee wrote:
> On Wed, Dec 24, 2008 at 5:26 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> >
> 
> >
> > The patch does go to some trouble to handle that case, as I'm sure
> > you've seen. Are you saying that part of the patch is ineffective and
> > should be removed, or?
> >
> 
> Umm.. are you talking about the "wait" mechanism ? That's the only
> thing I remember. Otherwise, prune record is pretty much same as any
> vacuum cleanup record.

With respect, I was hoping you might look in the patch and see if you
agree with the way it is handled. No need to remember. The whole
latestRemovedXid concept is designed to do help.

> > Should/could there be a way to control frequency of prune operations? We
> > could maintain cleanupxmin as a constant minimum distance from xmax, for
> > example.
> >
> 
> Well, there can be. But tuning any such thing might be difficult and
> would have implications on the primary. I am not saying we can do
> that, but we will need additional tests to see its impact.
> 
> > Are we saying we should take further measures, as I asked upthread? If
> > it is a consensus that I take some action, then I will.
> >
> 
> Again, I haven't seen how frequently queries may get canceled. Or if
> the delay is set to a large value, how far behind standby may get
> during replication, so I can't really comment. Have you done any tests
> on a reasonable hardware and checked if moderately long read queries
> can be run on standby without standby lagging behind a lot.

Queries get cancelled if data they need to see if removed and the
max_standby_delay expires. So lag will be max_standby_delay, by
definition.

Not sure what further tests would show. Queries that run for longer than
max_standby delay plus mean time between cleanup records will currently
end up being cancelled.

> I would prefer to have a solution which can be smarter than canceling
> all queries as soon as a cleanup record comes and timeout occurs. 

Currently, it was the consensus view that queries should be cancelled,
though there are other options still on the table.

It's discussed in Design Notes on the Wiki. "Simply ignoring WAL removal
has been discussed and rejected (so far).
http://archives.postgresql.org/pgsql-hackers/2008-05/msg00753.php
Explicitly defining the tables a transaction wishes to see has also been
discussed and rejected (so far).
http://archives.postgresql.org/pgsql-hackers/2008-08/msg00268.php"

> For
> example, if the queries are being run on a completely different set of
> tables where as the updates/deletes are happening on another set of
> tables, there is no reason why those queries should be canceled. I
> think it would be very common to have large history tables which may
> receive long read-only queries, but no updates/deletes. Whereas other
> frequently updated tables which receive very few queries on the
> standby.

There is currently no way to tell which tables a query will touch during
the course of its execution. Nor is there likely to be because of
user-defined volatile functions.

I attempted to find ways to explicitly limit the set of tables over
which a query might venture, but that cam to nothing also.

We've also discussed storing lastCleanedLSN for each buffer, so queries
can cancel themselves if they need to read a buffer that has had data
removed from it that they would have needed to see. I'll write that up
also.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support