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

I tried CLOG Buffers 32 and the performance is as good as 64 bit.. (I
havent tried 16 yet though.. ) I am going to try your second patch now..

Also here is the breakup by Mode. The combined time is the total time it
waits for all counts.


             Lock Id            Mode           Count
       ProcArrayLock          Shared               1
     CLogControlLock       Exclusive               4
     CLogControlLock          Shared               4
          XidGenLock          Shared               4
          XidGenLock       Exclusive               7
       WALInsertLock       Exclusive              21
        WALWriteLock       Exclusive              62
       ProcArrayLock       Exclusive              79

             Lock Id            Mode    Combined Time (ns)
     CLogControlLock        Exclusive               325200
     CLogControlLock           Shared              4509200
          XidGenLock        Exclusive             11839600
       ProcArrayLock           Shared             40506600
          XidGenLock           Shared            119013700
       WALInsertLock        Exclusive            148063100
        WALWriteLock        Exclusive            347052100
       ProcArrayLock        Exclusive           1054780600

Here is another one at higher user count 1600:

bash-3.00# ./4_lwlock_waits.d 9208

             Lock Id            Mode           Count
     CLogControlLock       Exclusive               1
     CLogControlLock          Shared               2
          XidGenLock          Shared               7
       WALInsertLock       Exclusive              12
        WALWriteLock       Exclusive              50
       ProcArrayLock       Exclusive              82

             Lock Id            Mode   Combined Time (ns)
     CLogControlLock        Exclusive                27300
          XidGenLock           Shared             14689300
     CLogControlLock           Shared             72664900
       WALInsertLock        Exclusive            101431300
        WALWriteLock        Exclusive            534357400
       ProcArrayLock        Exclusive           4110350300

Now I will try with your second patch.

Regards,
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.
>
>
> ------------------------------------------------------------------------
>
> Index: src/backend/access/transam/xact.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xact.c,v
> retrieving revision 1.245
> diff -c -r1.245 xact.c
> *** src/backend/access/transam/xact.c    7 Jun 2007 21:45:58 -0000    1.245
> --- src/backend/access/transam/xact.c    27 Jul 2007 09:09:08 -0000
> ***************
> *** 820,827 ****
>                * are fewer than CommitSiblings other backends with active
>                * transactions.
>                */
> !             if (CommitDelay > 0 && enableFsync &&
> !                 CountActiveBackends() >= CommitSiblings)
>                   pg_usleep(CommitDelay);
>
>               XLogFlush(recptr);
> --- 820,826 ----
>                * are fewer than CommitSiblings other backends with active
>                * transactions.
>                */
> !             if (CommitDelay > 0 && enableFsync)
>                   pg_usleep(CommitDelay);
>
>               XLogFlush(recptr);
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>