Re: Reducing contention for the LockMgrLock

Simon Riggs  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 
+  #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); }