Re: User concurrency thresholding: where do I look?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux