As discussed in several different email threads here and on
performance , when using pg_dump a on large number of objects, the
server has a quadratic behavior in LockReassignCurrentOwner where it
has to dig through the entire local lock table to push one or two
locks up from the portal being dropped to its parent.

The attached patch fixes that by remembering up to 10 local locks, and
pushing them up specifically rather than digging through the entire
lock table.  If the list overflows, then it reverts to the old
behavior of digging through the entire local lock table.

The same change was made to the case where the locks are being
released, rather than reassigned (i.e. subtransaction abort rather
than commit).  I have no evidence that digging through the local lock
table during bulk release was ever a bottleneck, but it seemed
inconsistent not to make that change as well.

When it needs to  forget a lock, it searches backwards in the list of
released lock and then just moves the last lock into the place of the
one to be forgotten.  Other ResourceOwner Forget functions slide the
entire list down to close the gap, rather than using the
selection-sort-like method.  I don't understand why they do that.  If
Forgets are strictly LIFO, then it would not make a difference.  If
they are mostly LIFO but occasionally not, maybe the insertion method
would win over the selection method.  From what I can tell, Locks are
mostly released in bulk anyway at transaction end, and rarely released

This patch reduces the time needed to dump 20,000 simple empty tables
from 3m50.903s to 20.695s, and of course larger gains at larger

dropdb test; createdb test
for f in `seq 1 10000 4000000` ; do
  perl -le "print qq{create table foo\$_ (k integer , v integer);}
foreach ($f..$f+9999)" | psql test>& /dev/null
  time pg_dump test|wc -c

For degrading performance in other cases, I think the best test case
is "pgbench -P" (implemented in another patch in this commitfest)
which has a loop which pushes one or two locks up from a portal to the
parent (which already owns them, due to previous rounds of the same
loop) very frequently.  There might be a performance degradation of
0.5% or so, but it is less than the run to run variation.  I plan to
run some longer test to get a better estimate.  If there is a
degradation in that range, how important is that?

I wanted to test a real worst case, which would be a malicious
ordering of lock releases (take 10 locks, release the first lock
taken, then release the other 9 in reverse order), but with the
absence of UNLOCK TABLE command, I can't figure out how to engineer



Attachment: resowner_lock_v1.patch
Description: Binary data

Sent via pgsql-hackers mailing list (
To make changes to your subscription:

Reply via email to