pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Simon Riggs <simon@2ndquadrant.com>
To: Jignesh K. Shah <J.K.Shah@Sun.COM>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Subject: Re: User concurrency thresholding: where do I look?
Date: Thu, 26 Jul 2007 16:51:16 +0100
Message-ID: <1185465076.4190.87.camel@ebony.site> (raw )
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 >
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
Attachments:
clog_buffers.v1.test.patch (text/x-patch, 701B) download
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);
view thread (58+ messages) latest in thread
Message-ID: <1185465076.4190.87.camel@ebony.site>
Permalink: ../1185465076.4190.87.camel@ebony.site/
Also on: postgresql.org/message-id/1185465076.4190.87.camel@ebony.site
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, J.K.Shah@Sun.COM, tgl@sss.pgh.pa.us, josh@agliodbs.com, alvherre@commandprompt.com
Subject: Re: User concurrency thresholding: where do I look?
In-Reply-To: <1185465076.4190.87.camel@ebony.site>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox