Re: Block level concurrency during recovery

On Mon, 2008-10-20 at 14:23 +0100, Gregory Stark wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>
> > * re-create the Cleanup lock on blocks, when the original operation was
> > performed while a Cleanup lock was held.
> >
> > So the plan is to introduce a new XLogLockBufferForCleanup() function
> > and then use it in all places where a cleanup lock was held during the
> > write of the WAL record.
>
> I'm not sure I'm following what you're talking about. Are you saying the slave
> will have to stop when it reaches a cleanup log entry and wait until it can
> obtain the exclusive lock with no other pins?

Yes.

> That sounds like the right thing though it could cause a long pause in WAL
> recovery.

It could.

> Perhaps we should plan on being able to kill other processes holding
> pins on the buffer just as we discussed killing transactions holding up
> advancing the globalxmin.

I'll copy my notes straight out of the Wiki:

We can't cancel a query that holds a pin on a particular block since we
don't keep track of who holds a pin. We just know a backend has a pin
and that the startup process must wait.

This may become a problem, or it may not. In most cases, backends hold
5-15 pins concurrently and pins are held for relatively short times.
Startup process will provide debug information for when pin times are
extended, and for monitoring total delay from pin waits.

Some strategies, if this becomes a problem:

* minimise pin hold times wherever this occurs
* deferring application of WAL for pinned blocks (requiring us to
use conditional locking)
* making VACUUM FREEZE hold stronger locks on standby to prevent
query access (but doesn't help HOT)
* ensuring that we perform read ahead I/O for WAL records that
have not yet reached head of apply queue

Most people on-list were comfortable with the idea of recovery waiting
for queries to finish, up to a limit, re: discussion around
max_standby_delay. So waiting for cleanup locks is just another source
of potential wait time.

I think it will take a while for people to come up with some user
experience of whether this will be a pain or not.

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