Re: Reducing contention for the LockMgrLock

Simon Riggs <simon@2ndquadrant.com> writes:
> The output you gave wasn't anything I recognize in the code. Assuming
> its not already there, please can you share code you are using to find
> the evidence, even if its just privately in some form?

See below.  Also, the message I previously mentioned shows a different
tack on the same theme:
http://archives.postgresql.org/pgsql-patches/2003-12/msg00365.php
although in the light of later events I think that keeping the counts
in shared memory like that is a bad idea --- too likely to skew the
results.

> You're looking at the number of spins to acquire each lock?

Number of semop waits.

> Manfred's earlier patch provides very clear output for observing
> contention, including full summaries. Could we commit that, so we can
> all use this for analysis? Updated with the wait info.

What patch would that be?
        regards, tom lane

*** src/backend/storage/ipc/ipc.c.orig    Tue Nov 22 16:06:33 2005
--- src/backend/storage/ipc/ipc.c    Tue Nov 29 12:27:13 2005
***************
*** 125,130 ****
--- 125,132 ---- {     elog(DEBUG3, "shmem_exit(%d)", code); 
+     LWLockStats();
+      /*      * call all the registered callbacks.      *
*** src/backend/storage/lmgr/lwlock.c.orig    Tue Dec  6 18:08:33 2005
--- src/backend/storage/lmgr/lwlock.c    Tue Dec  6 18:16:05 2005
***************
*** 21,26 ****
--- 21,28 ----  */ #include "postgres.h" 
+ #include <unistd.h>
+  #include "access/clog.h" #include "access/multixact.h" #include "access/subtrans.h"
***************
*** 32,37 ****
--- 34,43 ---- /* We use the ShmemLock spinlock to protect LWLockAssign */ extern slock_t *ShmemLock; 
+ static int num_counts;
+ static int *sh_acquire_counts;
+ static int *ex_acquire_counts;
+ static int *block_counts;  typedef struct LWLock {
***************
*** 209,214 ****
--- 215,226 ----     LWLockCounter = (int *) ((char *) LWLockArray - 2 * sizeof(int));     LWLockCounter[0] = (int)
NumFixedLWLocks;    LWLockCounter[1] = numLocks;
 
+ 
+     /* local counter space */
+     num_counts = numLocks;
+     sh_acquire_counts = calloc(numLocks, sizeof(int));
+     ex_acquire_counts = calloc(numLocks, sizeof(int));
+     block_counts = calloc(numLocks, sizeof(int)); }  
***************
*** 257,262 ****
--- 269,278 ----     int            extraWaits = 0;      PRINT_LWDEBUG("LWLockAcquire", lockid, lock);
+     if (mode == LW_EXCLUSIVE)
+         ex_acquire_counts[lockid]++;
+     else
+         sh_acquire_counts[lockid]++;      /*      * We can't wait if we haven't got a PGPROC.  This should only
occur
***************
*** 328,333 ****
--- 344,351 ----         if (!mustwait)             break;                /* got the lock */ 
+         block_counts[lockid]++;
+          /*          * Add myself to wait queue.          *
***************
*** 598,601 ****
--- 616,640 ----             return true;     }     return false;
+ }
+ 
+ void
+ LWLockStats(void)
+ {
+     int pid = getpid();
+     int i;
+ 
+     LWLockAcquire(0, LW_EXCLUSIVE);
+ 
+     for (i = 0; i < num_counts; i++)
+     {
+         if (sh_acquire_counts[i] || ex_acquire_counts[i] || block_counts[i])
+         {
+             fprintf(stderr, "PID %d lwlock %d: shacq %u exacq %u blk %u\n",
+                     pid, i, sh_acquire_counts[i], ex_acquire_counts[i],
+                     block_counts[i]);
+         }
+     }
+ 
+     LWLockRelease(0); }