Re: Another way to reduce pg_subtrans lookup overhead

Tom Lane wrote:
> I mentioned yesterday that I'm looking at the problem of excessive
> accesses to pg_subtrans when there is an old open transaction:
> http://archives.postgresql.org/pgsql-hackers/2005-11/msg01547.php
> 
> I thought of a different approach to it, which is to make snapshot
> checking take a hint from TransactionIdIsInProgress: use the subxid
> caches from the PG_PROC array.  The idea is to have GetSnapshot save
> not just the top-level active xids, but also the subxids, plus an
> indicator of whether any of the subxids caches are overflowed.
> Then, when checking to see if an xid is active according to a snapshot,
> we can skip the SubTransGetTopmostTransaction() call if the overflow
> flag isn't set --- instead, just look through the subxids for a match.
> 
> This approach would reduce the cost of snapshot checking in the "normal"
> case where there are no overflowed subxid caches, but it doesn't help at
> all if there are; plus it increases the cost of capturing a snapshot.
> So I'm not sure how much net win there would be, if any.

Yeah, I had thought about using the XidCache while checking your other
approach yesterday.  I was thinking however not in saving the Xids in
the snapshot, but using the ones already in PGPROC.  Not sure if that is
as useful, but looks like at least it would be able to reduce the
probability of examining pg_subtrans in some cases without introducing
extra cost.

Except that PGPROC will be staying locked longer ... I wonder if that is
problematic.  If it is, maybe it can be alleviated by introducing one
spinlock per backend in ProcArray in addition to the global lock, so the
latter can be released while each backend is checked.

But then maybe I'm just talking out of my caffeine deficit.

> Thoughts anyone?

Maybe it's possible to get useful numbers from the XidCache
XIDCACHE_DEBUG code ...

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support