LWLock statistics collector (was: CSStorm occurred again by postgreSQL8.2) - Mailing list pgsql-hackers

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