Re: User concurrency thresholding: where do I look?

"Jignesh K. Shah" <J(dot)K(dot)Shah(at)Sun(dot)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