lwlock contention with SSI

About a month ago, I told Kevin Grittner in an off-list conversation
that I'd work on providing him with some statistics about lwlock
contention under SSI.  I then ran a benchmark on a 16-core,
64-hardware thread IBM server, testing read-only pgbench performance
at scale factor 300 with 1, 8, and 32 clients (and an equal number of
client threads).  The following non-default configuration settings
were used:

shared_buffers = 8GB
maintenance_work_mem = 1GB
synchronous_commit = off
checkpoint_segments = 300
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
log_line_prefix = '%t [%p] '
default_transaction_isolation = 'serializable'
max_pred_locks_per_transaction = 1000

After running the test, I dropped the ball for a month.  But, picking
it back up again, here are the results.  I've taken the data that is
produced by LWLOCK_STATS, and I've filtered it down by consolidating
entries for the same lock across all PIDs that show up in the log
file.  Then, I've omitted all entries where blk == 0 and spindelay ==
0, because those locks were never contended, so they're boring; and
also because including them makes the results too long to make sense
of.  The results for the remaining locks are attached as three files,
based on the number of clients.  The really revealing entries, IMHO,
are these results from the 32-client test:

lwlock 28: shacq 86952211 exacq 257812441 blk 35212093 spindelay 40811
lwlock 29: shacq 0 exacq 87516792 blk 31177203 spindelay 10038
lwlock 30: shacq 227960353 exacq 0 blk 0 spindelay 10711

These locks are all SSI-related and they're all really hot.  Lock 28
is SerializableXactHashLock and lock 29 is
SerializableFinishedListLock; both are acquired an order of magnitude
more often than any non-SSI lock, and cause two orders of magnitude
more blocking than any other lock whatsoever.  Lock 30 is
SerializablePredicateLockListLock, which has no exclusive lock
acquisitions at all on this test, but the shared acquisitions result
in significant spinlock contention.  This latter problem could
probably be ameliorated with a reader/writer lock (a primitive we
don't currently have in Postgres, but you could build one up using
lwlocks), but it's unlikely to make much difference without doing
something about SerializableXactHashLock and
SerializableFinishedListLock first.

Once you get past these big three, there's also a ton of blocking on
the PredicateLockMgrLocks, which seem only ever to be acquired in
exclusive mode, but it's not nearly as bad due to the 16-way
partitioning.

[Obligatory disclaimer: This has nothing to do with 9.3 and is not
intended to distract attention therefrom.]

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company