Re: Another way to reduce pg_subtrans lookup overhead

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.

Thoughts anyone?

regards, tom lane

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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> 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.

> 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.

How would that work? The ones in PGPROC, by the time you are examining
the snapshot, might have little to do with the ones that were valid at
the time the snap was taken. Another problem is that (AFAICS) you'd
have to lock the ProcArray to look at them, and the whole point of this
exercise is to avoid needing to lock shared data structures during
HeapTupleSatisfiesSnapshot.

regards, tom lane