Re: SSI performance

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote:
> The logic of PredicateLockAcquire is:
> 
> 1. Check if we already have a lock on the tuple.
> 2. Check if we already have a lock on the page.
> 3. Check if we already have a lock on the relation.
> 
> So if you're accessing a lot of rows, so that your lock is
> promoted to a relation lock, you perform three hash table lookups
> on every PredicateLockAcquire() call to notice that you already
> have the lock.
> 
> I was going to put a note at the beginning of this mail saying
> upfront that this is 9.2 materila, but it occurs to me that we
> could easily just reverse the order of those tests. That would cut
> the overhead of the case where you already have a relation lock by
> 2/3, but make the case where you already have a tuple lock slower.
> Would that be a good tradeoff?
The problem with that is that we'd need to figure out how to handle
LW locking.  The target at each level may be protected by a
different partition lock, so locks must be released and acquired for
each level checked.  When we promote granularity we add to the
higher level first and then remove from the lower level, again
protected by potentially different partition locks.  To avoid having
a search miss during concurrent promotion, we did the searches from
the finer to the coarser granularity.  In early benchmarks we were
held up mostly by LW lock contention, and went to some effort to
rearrange locking to minimize that; we'd need to be very careful
trying to go the other direction here.
I just had a thought -- we already have the LocalPredicateLockHash
HTAB to help with granularity promotion issues without LW locking. 
Offhand, I can't see any reason we couldn't use this for an initial
check for a relation level lock, before going through the more
rigorous pass under cover of the locks.  We won't have a relation
lock showing in the local table if it never was taken out, and it
won't go away until the end of transaction (unless the whole
transaction is deamed safe from SSI conflicts and everything is
cleaned up early).
Dan, does that look sane to you, or am I having another "senior
moment" here?
If this works, it would be a very minor change, which might
eliminate a lot of that overhead for many common cases.
-Kevin