As part of our ongoing research into Postgres performance and
scalability, we recently downloaded version 8.2 from CVS and we wanted
to pass on some observations.
When comparing 8.2 against 8.1.4, we see that there is roughly a 20%
increase in throughput. We credit most of this improvement to the
modifications made to the way in which the BufMappingLock and
LockMgrLock locks are now handled. Locking sections (partitions) of the
Shared Buffer and Locking hash tables certainly seems to pay off.
We had also come to the same conclusion and added similar code into a
local copy of 8.1.4. Although, we used SpinLocks rather than LWLocks to
lock sections of the hash table and we used an LWLock to lock the hash
table for critical operations.
Against 8.1.4, we saw that LWLockAcquire was taking the majority of the
run time, so we added some monitoring code to track the amount of time
spent to acquire and release each lock. The BufMappingLock and
LockMgrLock were, of course, on the top of that list.
We moved some of our 8.1.4 lock monitoring code over to 8.2 to analyze
the lock distribution for the partitions. In doing so, we noted that
WALInsertLock had now become a bottleneck, absorbing most of the time
freed up by the BufMappingLock and LockMgrLock changes.
We took a bold move and made XLogInsert a NOP. The next lock to rise to
the top of the list was SInvalLock. However, the time increase in
SInvalLock did not seem as much as in WALInsertLock. Although we have
not taken steps to do so, we assume that removing SInvalLock may reveal
the next lock that might impede scalability.
Outside of LWLockAcquire (~10%), the next functions that really seem to
take most of the time are HeapTupleSatisfiesSnapshot (~21%) and
PinBuffer (~14%). We're currently profiling and monitoring those
functions.
