Re: User concurrency thresholding: where do I look? - Mailing list pgsql-performance

From Tom Lane
Subject Re: User concurrency thresholding: where do I look?
Date
Msg-id 13162.1185405865@sss.pgh.pa.us
Whole thread Raw
In response to Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah" <J.K.Shah@Sun.COM>)
Responses Re: User concurrency thresholding: where do I look?
List pgsql-performance
"Jignesh K. Shah" <J.K.Shah@Sun.COM> writes:
> Here is how I got the numbers..
> I had about 1600 users login into postgresql. Then started the run with
> 500 users and using DTrace I started tracking Postgresql  Locking "as
> viewed from one user/connection". Echo statements indicate how many
> users were active at that point and how was throughput performing. All
> IO is done on /tmp which means on a RAM disk.

> bash-3.00# echo 500 users - baseline number
> 500 users
> bash-3.00# ./3_lwlock_acquires.d 19178

>              Lock Id            Mode           Count
>     FirstLockMgrLock       Exclusive               1
>     RelCacheInitLock       Exclusive               2
>           SInvalLock       Exclusive               2
>        WALInsertLock       Exclusive              10
>       BufMappingLock       Exclusive              12
>       CheckpointLock          Shared              29
>  CheckpointStartLock          Shared              29
>           OidGenLock       Exclusive              29
>           XidGenLock       Exclusive              29
>     FirstLockMgrLock          Shared              33
>  CheckpointStartLock       Exclusive              78
>        FreeSpaceLock       Exclusive             114
>           OidGenLock          Shared             126
>           XidGenLock          Shared             152
>        ProcArrayLock          Shared             482

I don't think I believe these numbers.  For one thing, CheckpointLock
is simply not ever taken in shared mode.  The ratios of counts for
different locks seems pretty improbable too, eg there is no way on
earth that the LockMgrLocks are taken more often shared than
exclusive (I would expect no shared acquires at all in the sort of
test you are running).  Not to mention that the absolute number of
counts seems way too low.  So I think the counting tool is broken.

>              Lock Id   Combined Time (ns)
>           SInvalLock                29800
>     RelCacheInitLock                30300
>       BufMappingLock               168800
>     FirstLockMgrLock               414300
>        FreeSpaceLock              1281700
>        ProcArrayLock              7869900
>        WALInsertLock             11113200
>  CheckpointStartLock             13494700
>           OidGenLock             25719100
>           XidGenLock             26443300
>       CheckpointLock            194267800

Combined time of what exactly?  It looks like this must be the total
duration the lock is held, at least assuming that the time for
CheckpointLock is correctly reported.  It'd be much more useful to see
the total time spent waiting to acquire the lock.

            regards, tom lane

pgsql-performance by date:

Previous
From: "Jignesh K. Shah"
Date:
Subject: Re: User concurrency thresholding: where do I look?
Next
From: angga erwina
Date:
Subject: performance of postgresql in replication using slony