pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCLOG Patch
4+ messages / 2 participants
[nested] [flat]
* CLOG Patch
@ 2007-08-03 20:09 Jignesh K. Shah <J.K.Shah@Sun.COM>
2007-08-03 20:29 ` Re: CLOG Patch Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jignesh K. Shah @ 2007-08-03 20:09 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, Alvaro Herrera <alvherre@commandprompt.com>
Hi Simon,
This patch seems to work well (both with 32 and 64 value but not with 16
and the default 8). Is there a way we can integrate this in 8.3?
This will improve out of box performance quite a bit for high number of
users (atleat 30% in my OLTP test)
Regards,
Jignesh
Simon Riggs wrote:
> 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.
>
>
> ------------------------------------------------------------------------
>
> 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);
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CLOG Patch
2007-08-03 20:09 CLOG Patch Jignesh K. Shah <J.K.Shah@Sun.COM>
@ 2007-08-03 20:29 ` Simon Riggs <simon@2ndquadrant.com>
2007-08-10 17:54 ` Re: CLOG Patch Jignesh K. Shah <J.K.Shah@Sun.COM>
0 siblings, 1 reply; 4+ messages in thread
From: Simon Riggs @ 2007-08-03 20:29 UTC (permalink / raw)
To: Jignesh K. Shah <J.K.Shah@Sun.COM>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; pgsql-performance; Alvaro Herrera <alvherre@commandprompt.com>
On Fri, 2007-08-03 at 16:09 -0400, Jignesh K. Shah wrote:
> This patch seems to work well (both with 32 and 64 value but not with 16
> and the default 8).
Could you test at 24 please also? Tom has pointed out the additional
cost of setting this higher, even in workloads that don't benefit from
the I/O-induced contention reduction.
> Is there a way we can integrate this in 8.3?
I just replied to Josh's thread on -hackers about this.
> This will improve out of box performance quite a bit for high number of
> users (atleat 30% in my OLTP test)
Yes, thats good. Will this have a dramatic effect on a particular
benchmark, or for what reason might we need this? Tom has questioned the
use case here, so I think it would be good to explain a little more for
everyone. Thanks.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CLOG Patch
2007-08-03 20:09 CLOG Patch Jignesh K. Shah <J.K.Shah@Sun.COM>
2007-08-03 20:29 ` Re: CLOG Patch Simon Riggs <simon@2ndquadrant.com>
@ 2007-08-10 17:54 ` Jignesh K. Shah <J.K.Shah@Sun.COM>
2007-08-10 20:03 ` Re: CLOG Patch Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jignesh K. Shah @ 2007-08-10 17:54 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, Alvaro Herrera <alvherre@commandprompt.com>
I tried with CLOG 24 also and I got linear performance upto 1250 users
after which it started to tank. 32 got us to 1350 users before some
other bottleneck overtook it.
Based on what Tom said earlier, it might then make sense to make it a
tunable with the default of 8 but something one can change for high
number of users.
Thanks.
Regards,
Jignesh
Simon Riggs wrote:
> On Fri, 2007-08-03 at 16:09 -0400, Jignesh K. Shah wrote:
>
>
>> This patch seems to work well (both with 32 and 64 value but not with 16
>> and the default 8).
>>
>
> Could you test at 24 please also? Tom has pointed out the additional
> cost of setting this higher, even in workloads that don't benefit from
> the I/O-induced contention reduction.
>
>
>> Is there a way we can integrate this in 8.3?
>>
>
> I just replied to Josh's thread on -hackers about this.
>
>
>> This will improve out of box performance quite a bit for high number of
>> users (atleat 30% in my OLTP test)
>>
>
> Yes, thats good. Will this have a dramatic effect on a particular
> benchmark, or for what reason might we need this? Tom has questioned the
> use case here, so I think it would be good to explain a little more for
> everyone. Thanks.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CLOG Patch
2007-08-03 20:09 CLOG Patch Jignesh K. Shah <J.K.Shah@Sun.COM>
2007-08-03 20:29 ` Re: CLOG Patch Simon Riggs <simon@2ndquadrant.com>
2007-08-10 17:54 ` Re: CLOG Patch Jignesh K. Shah <J.K.Shah@Sun.COM>
@ 2007-08-10 20:03 ` Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 4+ messages in thread
From: Simon Riggs @ 2007-08-10 20:03 UTC (permalink / raw)
To: Jignesh K. Shah <J.K.Shah@Sun.COM>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Josh Berkus <josh@agliodbs.com>; pgsql-performance; Alvaro Herrera <alvherre@commandprompt.com>
On Fri, 2007-08-10 at 13:54 -0400, Jignesh K. Shah wrote:
> I tried with CLOG 24 also and I got linear performance upto 1250 users
> after which it started to tank. 32 got us to 1350 users before some
> other bottleneck overtook it.
Jignesh,
Thanks for testing that.
It's not very clear to everybody why an extra 100 users is useful and it
would certainly help your case if you can explain.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-08-10 20:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-03 20:09 CLOG Patch Jignesh K. Shah <J.K.Shah@Sun.COM>
2007-08-03 20:29 ` Simon Riggs <simon@2ndquadrant.com>
2007-08-10 17:54 ` Jignesh K. Shah <J.K.Shah@Sun.COM>
2007-08-10 20:03 ` Simon Riggs <simon@2ndquadrant.com>
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