LWLOCK_STATS

It's been a while since I did any testing with LWLOCK_STATS defined,
so I thought it might be about time to do that again and see how
things look. Here's how they looked back in July:

http://archives.postgresql.org/pgsql-hackers/2011-07/msg01373.php

Here are the results from a test I ran today on latest sources, again
on Nate Boley's machine. Five-minute pgbench run, scale factor 100,
permanent tables, my usual config settings. Somewhat depressingly,
virtually all of the interesting activity still centers around the
same three locks that were problematic back then, which means that -
although overall performance has improved quite a bit - we've not
really delivered any knockout punches. Here are the perpetrators:

lwlock 4: shacq 26160717 exacq 2690379 blk 1129763
lwlock 11: shacq 97074202 exacq 2699639 blk 1482737
lwlock 7: shacq 0 exacq 16522284 blk 2926957
grand total: shacq 225919534 exacq 77179954 blk 6218570

There is some change in how the contention is distributed. Taking the
number of times a request for each lock blocked as a percentage of the
total number of lock acquisitions that blocked, we get this:

WALInsertLock - July 2011: 35%, January 2012: 47%
CLogControLock - July 2011: 23%, January 2012: 24%
ProcArrayLock - July 2011: 32%, January 2012: 18%

Since there's been some change to the test configuration over the last
six months, this has to be taken with a grain of salt, but in broad
strokes it makes sense given what's been done - ProcArrayLock
contention is down significantly (due to Pavan's patch, and followup
tweaks), and the other locks are under correspondingly more pressure.
We've done enough work on CLogControlLock (today's change, and Simon's
prior patch to wake up the WAL writer more aggressively and thus allow
hint bits to be set sooner) to allow it to keep pace, so it's only up
slightly, but we haven't done anything about WALInsertLock and it's
therefore grown from just over a third of the blocks to almost half.

The top locks in terms of number of shared acquisitions are
CLogControlLock, which accounts for 47% of the shared lock
acquisitions in the system all by itself, followed by ProcArrayLock,
which accounts for another 12%. The buffer mapping locks make up
another 23% in total, with the busiest one having about 3.5x the
traffic of the least busy one. Even when these shared acquisitions are
mostly uncontended at the lwlock level, the spinlock can still be a
contention point, and thus these are possible future targets for
further reducing our synchronization overhead despite the fact that
(on this test) there's not much blocking on, say, the buffer mapping
locks.

Note that this fits in shared buffers; on a larger test case, there
would be much more blocking on the buffer mapping locks (and
presumably BufFreelistLock would be a big problem, too).

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