thread-index: AcfPnLnOjSuu4v10SZm3EcsPFiFGHw==
Content-Transfer-Encoding: 7bit
Content-Class: urn:content-classes:message
Importance: normal
Subject: Re: User concurrency thresholding: where do I look?
From: "Simon Riggs" <simon@2ndquadrant.com>
To: "Jignesh K. Shah" <J.K.Shah@Sun.COM>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>, "Josh Berkus" <josh@agliodbs.com>,
	<pgsql-performance@postgresql.org>,
	"Alvaro Herrera" <alvherre@commandprompt.com>
In-Reply-To: <46A8BD62.2080904@sun.com>
References: <200707190828.59088.josh@agliodbs.com>
	<200707191819.13899.josh@agliodbs.com>
	<15685.1184895926@sss.pgh.pa.us>
	<200707192138.39679.josh@agliodbs.com>
	<46A0FA31.2080208@sun.com> <12002.1184955831@sss.pgh.pa.us>
	<46A1055F.9010304@sun.com> <12445.1184958793@sss.pgh.pa.us>
	<46A11116.5030905@sun.com> <14909.1184962561@sss.pgh.pa.us>
	<46A1205B.8010205@sun.com> <15343.1184965054@sss.pgh.pa.us>
	<1185180668.4284.149.camel@ebony.site>
	<23848.1185199901@sss.pgh.pa.us>
	<1185201556.4284.243.camel@ebony.site>
	<25095.1185202498@sss.pgh.pa.us>
	<1185205663.4284.249.camel@ebony.site>
	<1185212261.4284.270.camel@ebony.site>
	<1545.1185214760@sss.pgh.pa.us> <46A7D5AD.9000706@sun.com>
	<13162.1185405865@sss.pgh.pa.us> <46A8AFDF.3050804@sun.com>
	<26825.1185460816@sss.pgh.pa.us> <46A8BD62.2080904@sun.com>
Content-Type: multipart/mixed;
	boundary="=-+PIocVKTRrUxFcYVzwIg"
Date: Thu, 26 Jul 2007 16:51:16 +0100
Message-ID: <1185465076.4190.87.camel@ebony.site>
MIME-Version: 1.0

This is a multi-part message in MIME format.

--=-+PIocVKTRrUxFcYVzwIg
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: 7bit

On Thu, 2007-07-26 at 11:27 -0400, Jignesh K. Shah wrote:

> However at 900 Users where the big drop in throughput occurs:
> It gives a different top "consumer" of time:


                postgres`LWLockAcquire+0x1c8
>               postgres`SimpleLruReadPage+0x1ac
>               postgres`TransactionIdGetStatus+0x14
>               postgres`TransactionLogFetch+0x58

TransactionIdGetStatus doesn't directly call SimpleLruReadPage().
Presumably the compiler has been rearranging things??

Looks like you're out of clog buffers. It seems like the clog buffers
aren't big enough to hold clog pages for long enough and the SELECT FOR
SHARE processing is leaving lots of additional read locks that are
increasing the number of clog requests for older xids.

Try the enclosed patch.
 
-- 
  Simon Riggs
  EnterpriseDB  http://www.enterprisedb.com

--=-+PIocVKTRrUxFcYVzwIg
Content-Disposition: attachment;
	filename="clog_buffers.v1.test.patch"
Content-Type: text/x-patch; charset="utf-8"; name="clog_buffers.v1.test.patch"
Content-Transfer-Encoding: 7bit

Index: src/include/access/clog.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/access/clog.h,v
retrieving revision 1.19
diff -c -r1.19 clog.h
*** src/include/access/clog.h	5 Jan 2007 22:19:50 -0000	1.19
--- src/include/access/clog.h	26 Jul 2007 15:44:58 -0000
***************
*** 29,35 ****
  
  
  /* Number of SLRU buffers to use for clog */
! #define NUM_CLOG_BUFFERS	8
  
  
  extern void TransactionIdSetStatus(TransactionId xid, XidStatus status);
--- 29,35 ----
  
  
  /* Number of SLRU buffers to use for clog */
! #define NUM_CLOG_BUFFERS	64	
  
  
  extern void TransactionIdSetStatus(TransactionId xid, XidStatus status);

--=-+PIocVKTRrUxFcYVzwIg--