Re: User concurrency thresholding: where do I look?

Yes I can try to breakup the Shared and exclusive time..

Also yes I use commit delays =10, it helps out a lot in reducing IO load..

I will try out the patch soon.

-Jignesh

Simon Riggs wrote:
> On Thu, 2007-07-26 at 17:17 -0400, Jignesh K. Shah wrote:
>
>> Lock Id Combined Time (ns)
>> XidGenLock 194966200
>> WALInsertLock 517955000
>> CLogControlLock 679665100
>> WALWriteLock 2838716200
>> ProcArrayLock 44181002600
>>
>
> Is this the time the lock is held for or the time that we wait for that
> lock? It would be good to see the break down of time separately for
> shared and exclusive.
>
> Can we have a table like this:
> LockId,LockMode,SumTimeLockHeld,SumTimeLockWait
>
>
>> Top Wait time seems to come from the following code path for
>> ProcArrayLock:
>>
>> Lock Id Mode Count
>> ProcArrayLock Exclusive 21
>>
>> Lock Id Combined Time (ns)
>> ProcArrayLock 5255937500
>>
>> Lock Id Combined Time (ns)
>>
>>
>> postgres`LWLockAcquire+0x1f0
>> postgres`CommitTransaction+0x104
>> postgres`CommitTransactionCommand+0xbc
>> postgres`finish_xact_command+0x78
>>
>
> Well thats pretty weird. That code path clearly only happens once per
> transaction and ought to be fast. The other code paths that take
> ProcArrayLock like TransactionIdIsInProgress() and GetSnapshotData()
> ought to spend more time holding the lock. Presumably you are running
> with a fair number of SERIALIZABLE transactions?
>
> Are you running with commit_delay > 0? Its possible that the call to
> CountActiveBackends() is causing pinging of the procarray by other
> backends while we're trying to read it during CommitTransaction(). If
> so, try the attached patch.
>
>