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. ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly