pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Getting rid of AtEOXact_Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results, some wierdness ...)
9+ messages / 5 participants
[nested] [flat]

* Getting rid of AtEOXact_Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results, some wierdness ...)
@ 2004-10-16 16:54  Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2004-10-16 16:54 UTC (permalink / raw)
  To: pgsql-hackers@postgreSQL.org; pgsql-performance

I wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> First off, two test runs with OProfile are available at:
>> http://khack.osdl.org/stp/298124/
>> http://khack.osdl.org/stp/298121/

> Hmm.  The stuff above 1% in the first of these is

> Counted CPU_CLK_UNHALTED events (clocks processor is not halted) with a unit mask of 0x00 (No unit mask) count 100000
> samples  %        app name                 symbol name
> ...
> 920369    2.1332  postgres                 AtEOXact_Buffers
> ...

> In the second test AtEOXact_Buffers is much lower (down around 0.57
> percent) but the other suspects are similar.  Since the only difference
> in parameters is shared_buffers (36000 vs 9000), it does look like we
> are approaching the point where AtEOXact_Buffers is a problem, but so
> far it's only a 2% drag.

It occurs to me that given the 8.0 resource manager mechanism, we could
in fact dispense with AtEOXact_Buffers, or perhaps better turn it into a
no-op unless #ifdef USE_ASSERT_CHECKING.  We'd just get rid of the
special case for transaction termination in resowner.c and let the
resource owner be responsible for releasing locked buffers always.  The
OSDL results suggest that this won't matter much at the level of 10000
or so shared buffers, but for 100000 or more buffers the linear scan in
AtEOXact_Buffers is going to become a problem.

We could also get rid of the linear search in UnlockBuffers().  The only
thing it's for anymore is to release a BM_PIN_COUNT_WAITER flag, and
since a backend could not be doing more than one of those at a time,
we don't really need an array of flags for that, only a single variable.
This does not show in the OSDL results, which I presume means that their
test case is not exercising transaction aborts; but I think we need to
zap both routines to make the world safe for large shared_buffers
values.  (See also
http://archives.postgresql.org/pgsql-performance/2004-10/msg00218.php)

Any objection to doing this for 8.0?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Getting rid of AtEOXact_Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results, some wierdness ...)
@ 2004-10-16 21:18  Josh Berkus <josh@agliodbs.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2004-10-16 21:18 UTC (permalink / raw)
  To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgreSQL.org

Tom,

> We could also get rid of the linear search in UnlockBuffers().  The only
> thing it's for anymore is to release a BM_PIN_COUNT_WAITER flag, and
> since a backend could not be doing more than one of those at a time,
> we don't really need an array of flags for that, only a single variable.
> This does not show in the OSDL results, which I presume means that their
> test case is not exercising transaction aborts;

In the test, one out of every 100 new order transactions is aborted (about 1 
out of 150 transactions overall).

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Getting rid of AtEOXact_Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results, some wierdness ...)
@ 2004-10-16 21:19  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2004-10-16 21:19 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance; pgsql-hackers@postgresql.org

Josh Berkus <josh@agliodbs.com> writes:
>> This does not show in the OSDL results, which I presume means that their
>> test case is not exercising transaction aborts;

> In the test, one out of every 100 new order transactions is aborted (about 1 
> out of 150 transactions overall).

Okay, but that just ensures that any bottlenecks in xact abort will be
down in the noise in this test case ...

In any case, those changes are in CVS now if you want to try them.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Getting rid of AtEOXact_Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results, some wierdness ...)
@ 2004-10-16 21:36  Josh Berkus <josh@agliodbs.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Josh Berkus @ 2004-10-16 21:36 UTC (permalink / raw)
  To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org

Tom,

> In any case, those changes are in CVS now if you want to try them.

OK.  Will have to wait until OSDL gives me a dedicated testing machine 
sometime mon/tues/wed.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

*  Re:  Getting rid of AtEOXact Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results,  some wierdness ...)
@ 2004-10-17 19:40  simon@2ndquadrant.com
  0 siblings, 3 replies; 9+ messages in thread

From: simon@2ndquadrant.com @ 2004-10-17 19:40 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; josh@agliodbs.com; +Cc: pgsql-hackers@postgreSQL.org; pgsql-performance


Seeing as I've missed the last N messages... I'll just reply to this
one, rather than each of them in turn...

Tom Lane <tgl@sss.pgh.pa.us> wrote on 16.10.2004, 18:54:17:
> I wrote:
> > Josh Berkus  writes:
> >> First off, two test runs with OProfile are available at:
> >> http://khack.osdl.org/stp/298124/
> >> http://khack.osdl.org/stp/298121/
> 
> > Hmm.  The stuff above 1% in the first of these is
> 
> > Counted CPU_CLK_UNHALTED events (clocks processor is not halted) with a unit mask of 0x00 (No unit mask) count 100000
> > samples  %        app name                 symbol name
> > ...
> > 920369    2.1332  postgres                 AtEOXact_Buffers
> > ...
> 
> > In the second test AtEOXact_Buffers is much lower (down around 0.57
> > percent) but the other suspects are similar.  Since the only difference
> > in parameters is shared_buffers (36000 vs 9000), it does look like we
> > are approaching the point where AtEOXact_Buffers is a problem, but so
> > far it's only a 2% drag.

Yes... as soon as you first mentioned AtEOXact_Buffers, I realised I'd
seen it near the top of the oprofile results on previous tests.

Although you don't say this, I presume you're acting on the thought that
a 2% drag would soon become a much larger contention point with more
users and/or smaller transactions - since these things are highly
non-linear.

> 
> It occurs to me that given the 8.0 resource manager mechanism, we could
> in fact dispense with AtEOXact_Buffers, or perhaps better turn it into a
> no-op unless #ifdef USE_ASSERT_CHECKING.  We'd just get rid of the
> special case for transaction termination in resowner.c and let the
> resource owner be responsible for releasing locked buffers always.  The
> OSDL results suggest that this won't matter much at the level of 10000
> or so shared buffers, but for 100000 or more buffers the linear scan in
> AtEOXact_Buffers is going to become a problem.

If the resource owner is always responsible for releasing locked
buffers, who releases the locks if the backend crashes? Do we need some
additional code in bgwriter (or?) to clean up buffer locks?

> 
> We could also get rid of the linear search in UnlockBuffers().  The only
> thing it's for anymore is to release a BM_PIN_COUNT_WAITER flag, and
> since a backend could not be doing more than one of those at a time,
> we don't really need an array of flags for that, only a single variable.
> This does not show in the OSDL results, which I presume means that their
> test case is not exercising transaction aborts; but I think we need to
> zap both routines to make the world safe for large shared_buffers
> values.  (See also
> http://archives.postgresql.org/pgsql-performance/2004-10/msg00218.php)

Yes, that's important. 

> Any objection to doing this for 8.0?
> 

As you say, if these issues are definitely kicking in at 100000
shared_buffers - there's a good few people out there with 800Mb
shared_buffers already. 

Could I also suggest that we adopt your earlier suggestion of raising
the bgwriter parameters as a permanent measure - i.e. changing the
defaults in postgresql.conf. That way, StrategyDirtyBufferList won't
immediately show itself as a problem when using the default parameter
set. It would be a shame to remove one obstacle only to leave another
one following so close behind. [...and that also argues against an
earlier thought to introduce more fine grained values for the
bgwriter's parameters, ISTM]

Also, what will Vacuum delay do to the O(N) effect of
FlushRelationBuffers when called by VACUUM? Will the locks be held for
longer?

I think we should do some tests while running a VACUUM in the background
also, which isn't part of the DBT-2 set-up, but perhaps we might argue
*it should be for the PostgreSQL version*?

Dare we hope for a scalability increase in 8.0 after all.... 

Best Regards,

Simon Riggs



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Re:  Getting rid of AtEOXact Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results,  some wierdness ...)
@ 2004-10-17 20:12  Tom Lane <tgl@sss.pgh.pa.us>
  parent: simon@2ndquadrant.com
  2 siblings, 0 replies; 9+ messages in thread

From: Tom Lane @ 2004-10-17 20:12 UTC (permalink / raw)
  To: simon@2ndquadrant.com; +Cc: josh@agliodbs.com; pgsql-hackers@postgresql.org; pgsql-performance

<simon@2ndquadrant.com> writes:
> If the resource owner is always responsible for releasing locked
> buffers, who releases the locks if the backend crashes?

The ensuing system reset takes care of that.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [HACKERS] Getting rid of AtEOXact Buffers (was Re: [Testperf-general]
@ 2004-10-18 00:22  Gaetano Mendola <mendola@bigfoot.com>
  parent: simon@2ndquadrant.com
  2 siblings, 0 replies; 9+ messages in thread

From: Gaetano Mendola @ 2004-10-18 00:22 UTC (permalink / raw)
  To: simon@2ndquadrant.com

simon@2ndquadrant.com wrote:

> If the resource owner is always responsible for releasing locked
> buffers, who releases the locks if the backend crashes?

The semaphore "undo" I hope.


Regards
Gaetano Mendola




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Getting rid of AtEOXact Buffers (was Re: [Testperf-general]
@ 2004-10-18 20:36  Jan Wieck <JanWieck@Yahoo.com>
  parent: simon@2ndquadrant.com
  2 siblings, 1 reply; 9+ messages in thread

From: Jan Wieck @ 2004-10-18 20:36 UTC (permalink / raw)
  To: simon@2ndquadrant.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; josh@agliodbs.com; pgsql-hackers@postgresql.org; pgsql-performance

On 10/17/2004 3:40 PM, simon@2ndquadrant.com wrote:

> Seeing as I've missed the last N messages... I'll just reply to this
> one, rather than each of them in turn...
> 
> Tom Lane <tgl@sss.pgh.pa.us> wrote on 16.10.2004, 18:54:17:
>> I wrote:
>> > Josh Berkus  writes:
>> >> First off, two test runs with OProfile are available at:
>> >> http://khack.osdl.org/stp/298124/
>> >> http://khack.osdl.org/stp/298121/
>> 
>> > Hmm.  The stuff above 1% in the first of these is
>> 
>> > Counted CPU_CLK_UNHALTED events (clocks processor is not halted) with a unit mask of 0x00 (No unit mask) count 100000
>> > samples  %        app name                 symbol name
>> > ...
>> > 920369    2.1332  postgres                 AtEOXact_Buffers
>> > ...
>> 
>> > In the second test AtEOXact_Buffers is much lower (down around 0.57
>> > percent) but the other suspects are similar.  Since the only difference
>> > in parameters is shared_buffers (36000 vs 9000), it does look like we
>> > are approaching the point where AtEOXact_Buffers is a problem, but so
>> > far it's only a 2% drag.
> 
> Yes... as soon as you first mentioned AtEOXact_Buffers, I realised I'd
> seen it near the top of the oprofile results on previous tests.
> 
> Although you don't say this, I presume you're acting on the thought that
> a 2% drag would soon become a much larger contention point with more
> users and/or smaller transactions - since these things are highly
> non-linear.
> 
>> 
>> It occurs to me that given the 8.0 resource manager mechanism, we could
>> in fact dispense with AtEOXact_Buffers, or perhaps better turn it into a
>> no-op unless #ifdef USE_ASSERT_CHECKING.  We'd just get rid of the
>> special case for transaction termination in resowner.c and let the
>> resource owner be responsible for releasing locked buffers always.  The
>> OSDL results suggest that this won't matter much at the level of 10000
>> or so shared buffers, but for 100000 or more buffers the linear scan in
>> AtEOXact_Buffers is going to become a problem.
> 
> If the resource owner is always responsible for releasing locked
> buffers, who releases the locks if the backend crashes? Do we need some
> additional code in bgwriter (or?) to clean up buffer locks?

If the backend crashes, the postmaster (assuming a possibly corrupted 
shared memory) restarts the whole lot ... so why bother?

> 
>> 
>> We could also get rid of the linear search in UnlockBuffers().  The only
>> thing it's for anymore is to release a BM_PIN_COUNT_WAITER flag, and
>> since a backend could not be doing more than one of those at a time,
>> we don't really need an array of flags for that, only a single variable.
>> This does not show in the OSDL results, which I presume means that their
>> test case is not exercising transaction aborts; but I think we need to
>> zap both routines to make the world safe for large shared_buffers
>> values.  (See also
>> http://archives.postgresql.org/pgsql-performance/2004-10/msg00218.php)
> 
> Yes, that's important. 
> 
>> Any objection to doing this for 8.0?
>> 
> 
> As you say, if these issues are definitely kicking in at 100000
> shared_buffers - there's a good few people out there with 800Mb
> shared_buffers already. 
> 
> Could I also suggest that we adopt your earlier suggestion of raising
> the bgwriter parameters as a permanent measure - i.e. changing the
> defaults in postgresql.conf. That way, StrategyDirtyBufferList won't
> immediately show itself as a problem when using the default parameter
> set. It would be a shame to remove one obstacle only to leave another
> one following so close behind. [...and that also argues against an
> earlier thought to introduce more fine grained values for the
> bgwriter's parameters, ISTM]

I realized that StrategyDirtyBufferList currently wasts a lot of time by 
first scanning over all the buffers that haven't even been hit since 
it's last call and neither have been dirty last time (and thus, are at 
the beginning of the list and can't be dirty anyway). If we would have a 
way to give it a smart "point in the list to start scanning" ...


> 
> Also, what will Vacuum delay do to the O(N) effect of
> FlushRelationBuffers when called by VACUUM? Will the locks be held for
> longer?

Vacuum only naps at the points where it checks for interrupts, and at 
that time it isn't supposed to hold any critical locks.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: Getting rid of AtEOXact Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results, some wierdness ...)
@ 2004-10-18 21:01  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jan Wieck <JanWieck@Yahoo.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Tom Lane @ 2004-10-18 21:01 UTC (permalink / raw)
  To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: simon@2ndquadrant.com; josh@agliodbs.com; pgsql-hackers@postgresql.org; pgsql-performance

Jan Wieck <JanWieck@Yahoo.com> writes:
> I realized that StrategyDirtyBufferList currently wasts a lot of time by 
> first scanning over all the buffers that haven't even been hit since 
> it's last call and neither have been dirty last time (and thus, are at 
> the beginning of the list and can't be dirty anyway). If we would have a 
> way to give it a smart "point in the list to start scanning" ...

I don't think it's true that they *can't* be dirty.

(1) Buffers are marked dirty when released, whereas they are moved to
the fronts of the lists when acquired.

(2) the cntxDirty bit can be set asynchronously to any BufMgrLock'd
operation.

But it sure seems like we are doing more work than we really need to.

One idea I had was for the bgwriter to collect all the dirty pages up to
say halfway on the LRU lists, and then write *all* of these, not just
the first N, over as many rounds as are needed.  Then go back and call
StrategyDirtyBufferList again to get a new list.  (We don't want it to
write every dirty buffer this way, because the ones near the front of
the list are likely to be dirtied again right away.  But certainly we
could write more than 1% of the dirty buffers without getting into the
area of the recently-used buffers.)

There isn't any particularly good reason for this to share code with
checkpoint-style BufferSync, btw.  BufferSync could just as easily scan
the buffers linearly, since it doesn't matter what order it writes them
in.  So we could change StrategyDirtyBufferList to stop as soon as it's
halfway up the LRU lists, which would save at least a few cycles.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2004-10-18 21:01 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-16 16:54 Getting rid of AtEOXact_Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results, some wierdness ...) Tom Lane <tgl@sss.pgh.pa.us>
2004-10-16 21:18 ` Josh Berkus <josh@agliodbs.com>
2004-10-16 21:19   ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-16 21:36     ` Josh Berkus <josh@agliodbs.com>
2004-10-17 19:40  Re:  Getting rid of AtEOXact Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results,  some wierdness ...) simon@2ndquadrant.com
2004-10-17 20:12 ` Re: Re:  Getting rid of AtEOXact Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results,  some wierdness ...) Tom Lane <tgl@sss.pgh.pa.us>
2004-10-18 00:22 ` Re: [HACKERS] Getting rid of AtEOXact Buffers (was Re: [Testperf-general] Gaetano Mendola <mendola@bigfoot.com>
2004-10-18 20:36 ` Re: Getting rid of AtEOXact Buffers (was Re: [Testperf-general] Jan Wieck <JanWieck@Yahoo.com>
2004-10-18 21:01   ` Re: Getting rid of AtEOXact Buffers (was Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Memscalability results, some wierdness ...) Tom Lane <tgl@sss.pgh.pa.us>

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