Re: Reducing relation locking overhead

On Thu, 2005-12-01 at 21:37 -0500, Tom Lane wrote:
> In looking at the current pgbench results, I notice that one
> considerable contribution to LWLock contention is access to the
> heavyweight-lock manager.

> 4. The only reason we need to take relation-level locks on indexes
> at all is to make the world safe for REINDEX being done concurrently
> with read-only accesses to the table (that don't use the index being
> reindexed). If we went back to requiring exclusive lock for reindex we
> could forget all about both #2 and #3. Particularly for updates of
> relations with lots of indexes, this could be a pretty significant win.
> However we'd definitely be giving up something that was seen as a
> feature at one point, so I'm not sold on this idea ... unless someone
> can see a way to reduce the overhead without giving up concurrent
> REINDEX.

In a high txn rate workload the majority of lock requests will
definitely be towards indexes. This is a major overhead and contention
point and as you say, exists only to allow REINDEX (and online CREATE
TABLE when it exists) to operate.

There is a considerable price to be paid for this flexibility, so
although I think that flexibility is important, I do think we need to
look at ways of reducing the cost for the 95%+ of the time we pay the
price for no benefit. It seems clear that if we do not, then we are
going to have to completely redesign the lock manager, so any way
forward from here is a reasonable size task.

Putting extra restrictions on REINDEX-like operations would be
acceptable in these circumstances.

Now follows various ideas, many of which are quite possibly bogus, but
the main point is that we need a way, even if it isn't one of these:

One such idea might be to require that they occur outside of a
transaction block, just like VACUUM.

Further thoughts:
1. Normally, we do not lock indexes via the LockMgrLock

2. When a REINDEX-like operation comes along, it first of all updates an
MaintIntentLock flag on the index relation, which causes a relcache
invalidation. It then waits until all backends have updated their
relcache.

3. While the MaintIntentLock is set, all operations acquire and release
LockMgrLocks on the index.

4. The REINDEX-like operation proceeds, having accepted a possibly
extensive delay in acquiring the MaintIntentLock, in the name of
concurrent access.

5. When the REINDEX completes, we unset the MaintIntentLock and other
backends return to normal operation.

Best Regards, Simon Riggs