Hi hackers, I tackled the performance problem on SUBTRANS module with Okano. He and I reach a conclusion that SubTrans log is heavily read on a specific access pattern in my TPC-W implementation. There seems to be awful traffic on SUBTRANS to check visivility of tuples in HeapTupleSatisfiesSnapshot(). I'll report more details later.
BTW, I wrote a patch to collect statistics of Light-weight locks for analysis. We have already had Trace_lwlocks option, but it can collect statistics with less impact. The following is an output of the patch (on 8.1). Are you interested in the feature? and I'll port it to HEAD and post it. > # SELECT * FROM pg_stat_lwlocks; > kind | pg_stat_get_lwlock_name | sh_call | sh_wait | ex_call | > ex_wait | > ------+----------------------------+------------+-----------+-----------+-----------+- > 0 | BufMappingLock | 559375542 | 33542 | 320092 | > 24025 | > 1 | BufFreelistLock | 0 | 0 | 370709 | > 47 | > 2 | LockMgrLock | 0 | 0 | 41718885 | > 734502 | > 3 | OidGenLock | 33 | 0 | 0 | > 0 | > 4 | XidGenLock | 12572279 | 10095 | 11299469 | > 20089 | > 5 | ProcArrayLock | 8371330 | 72052 | 16965667 | > 603294 | > 6 | SInvalLock | 38822428 | 435 | 25917 | > 128 | > 7 | FreeSpaceLock | 0 | 0 | 16787 | > 4 | > 8 | WALInsertLock | 0 | 0 | 1239911 | > 885 | > 9 | WALWriteLock | 0 | 0 | 69907 | > 5589 | > 10 | ControlFileLock | 0 | 0 | 16686 | > 1 | > 11 | CheckpointLock | 0 | 0 | 34 | > 0 | > 12 | CheckpointStartLock | 69509 | 0 | 34 | > 1 | > 13 | CLogControlLock | 0 | 0 | 236763 | > 183 | > 14 | SubtransControlLock | 0 | 0 | 753773945 | > 205273395 | > 15 | MultiXactGenLock | 66 | 0 | 0 | > 0 | > 16 | MultiXactOffsetControlLock | 0 | 0 | 35 | > 0 | > 17 | MultiXactMemberControlLock | 0 | 0 | 34 | > 0 | > 18 | RelCacheInitLock | 0 | 0 | 0 | > 0 | > 19 | BgWriterCommLock | 0 | 0 | 61457 | > 1 | > 20 | TwoPhaseStateLock | 33 | 0 | 0 | > 0 | > 21 | TablespaceCreateLock | 0 | 0 | 0 | > 0 | > 22 | BufferIO | 0 | 0 | 695627 | > 16 | > 23 | BufferContent | 3568231805 | 1897 | 1361394 | > 829 | > 24 | CLog | 0 | 0 | 0 | > 0 | > 25 | SubTrans | 138571621 | 143208883 | 8122181 | > 8132646 | > 26 | MultiXactOffset | 0 | 0 | 0 | > 0 | > 27 | MultiXactMember | 0 | 0 | 0 | > 0 | > (28 rows) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
