While poking at Peter Schmidt's comments about pgbench showing worse
performance than for 7.0 (using -F in both cases), I noticed that given
enough buffer space, FileWrite never seemed to get called at all.  A
little bit of sleuthing revealed the following:

1. Under WAL, we don't write dirty buffers out of the shared memory at
every transaction commit.  Instead, as long as a dirty buffer's slot
isn't needed for something else, it just sits there until the next
checkpoint or shutdown.  CreateCheckpoint calls FlushBufferPool which
writes out all the dirty buffers in one go.  This is a Good Thing; it
lets us consolidate multiple updates of a single datafile page by
successive transactions into one disk write.  We need this to buy back
some of the extra I/O required to write the WAL logfile.

2. However, this means that a lot of the dirty-buffer writes get done by
the periodic checkpoint process, not by the backends that originally
dirtied the buffers.  And that means that every last one gets done by
blind write, because the checkpoint process isn't going to have opened
any relation cache entries --- maybe a couple of system catalog
relations, but for sure it won't have any for user relations.  If you
look at BufferSync, any page that the current process doesn't have an
already-open relcache entry for is sent to smgrblindwrt not smgrwrite.

3. Blind write is gratuitously inefficient: it does separate open,
seek, write, close kernel calls for every request.  This was the right
thing in 7.0.*, because backends relatively seldom did blind writes and
even less often needed to blindwrite multiple pages of a single relation
in succession.  But the typical usage has changed a lot.


I am thinking it'd be a good idea if blind write went through fd.c and
thus was able to re-use open file descriptors, just like normal writes.
This should improve the efficiency of dumping dirty buffers during
checkpoint by a noticeable amount.

Comments?

			regards, tom lane

Fri, 16 Feb 2001 21:31:48 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Performance lossage in checkpoint dumping
> 3. Blind write is gratuitously inefficient: it does separate open,
> seek, write, close kernel calls for every request.  This was the right
> thing in 7.0.*, because backends relatively seldom did blind writes and
> even less often needed to blindwrite multiple pages of a single relation
> in succession.  But the typical usage has changed a lot.
> 
> 
> I am thinking it'd be a good idea if blind write went through fd.c and
> thus was able to re-use open file descriptors, just like normal writes.
> This should improve the efficiency of dumping dirty buffers during
> checkpoint by a noticeable amount.

I totally agree the current code is broken.  I am reading what you say
and am thinking, "Oh well, we lose there, but at least we only open a
relation once and do them in one shot."  Now I am hearing that is not
true, and it is a performance problem.

This is not a total surprise.  We have that stuff pretty well
streamlined for the old behavour.  Now that things have changed, I can
see the need to reevaluate stuff.

Not sure how to handle the beta issue though.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 16 Feb 2001 21:47:25 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance lossage in checkpoint dumping
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> I am thinking it'd be a good idea if blind write went through fd.c and
>> thus was able to re-use open file descriptors, just like normal writes.
>> This should improve the efficiency of dumping dirty buffers during
>> checkpoint by a noticeable amount.

> Not sure how to handle the beta issue though.

After looking a little more, I think this is too big a change to risk
making for beta.  I was thinking it might be an easy change, but it's
not; there's noplace to store the open-relation reference if we don't
have a relcache entry.  But we don't want to pay the price of opening a
relcache entry just to dump some buffers.

I recall Vadim speculating about decoupling the storage manager's notion
of open files from the relcache, and having a much more lightweight
open-relation mechanism at the smgr level.  That might be a good way
to tackle this.  But I'm not going to touch it for 7.1...

			regards, tom lane

Fri, 16 Feb 2001 22:06:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance lossage in checkpoint dumping 
> After looking a little more, I think this is too big a change to risk
> making for beta.  I was thinking it might be an easy change, but it's
> not; there's noplace to store the open-relation reference if we don't
> have a relcache entry.  But we don't want to pay the price of opening a
> relcache entry just to dump some buffers.
> 
> I recall Vadim speculating about decoupling the storage manager's notion
> of open files from the relcache, and having a much more lightweight
> open-relation mechanism at the smgr level.  That might be a good way
> to tackle this.  But I'm not going to touch it for 7.1...

No way to group the writes to you can keep the most recent one open?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 16 Feb 2001 22:33:03 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance lossage in checkpoint dumping
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> But I'm not going to touch it for 7.1...

> No way to group the writes to you can keep the most recent one open?

Don't see an easy way, do you?

			regards, tom lane

Fri, 16 Feb 2001 22:42:59 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance lossage in checkpoint dumping 

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> But I'm not going to touch it for 7.1...
> 
> > No way to group the writes to you can keep the most recent one open?
> 
> Don't see an easy way, do you?
> 

No, but I haven't looked at it.  I am now much more concerned with the
delay, and am wondering if I should start thinking about trying my idea
of looking for near-committers and post the patch to the list to see if
anyone likes it for 7.1 final.  Vadim will not be back in enough time to
write any new code in this area, I am afraid.

We could look to fix this in 7.1.1.  Let's see what the pgbench tester
comes back with when he sets the delay to zero.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 16 Feb 2001 22:46:05 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance lossage in checkpoint dumping
On Fri, 16 Feb 2001, Bruce Momjian wrote:

>
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > >> But I'm not going to touch it for 7.1...
> >
> > > No way to group the writes to you can keep the most recent one open?
> >
> > Don't see an easy way, do you?
> >
>
> No, but I haven't looked at it.  I am now much more concerned with the
> delay, and am wondering if I should start thinking about trying my idea
> of looking for near-committers and post the patch to the list to see if
> anyone likes it for 7.1 final.  Vadim will not be back in enough time to
> write any new code in this area, I am afraid.

Near committers? *puzzled look*



Sat, 17 Feb 2001 00:58:05 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: Performance lossage in checkpoint dumping
The Hermit Hacker <scrappy@hub.org> writes:
> No way to group the writes to you can keep the most recent one open?
> Don't see an easy way, do you?
>> 
>> No, but I haven't looked at it.  I am now much more concerned with the
>> delay,

I concur.  The blind write business is not important enough to hold up
the release for --- for one thing, it has nothing to do with the pgbench
results we're seeing, because these tests don't run long enough to
include any checkpoint cycles.  The commit delay, on the other hand,
is a big problem.

>> and am wondering if I should start thinking about trying my idea
>> of looking for near-committers and post the patch to the list to see if
>> anyone likes it for 7.1 final.  Vadim will not be back in enough time to
>> write any new code in this area, I am afraid.

> Near committers? *puzzled look*

Processes nearly ready to commit.  I'm thinking that any mechanism for
detecting that might be overkill, however, especially compared to just
setting commit_delay to zero by default.

I've been sitting here running pgbench under various scenarios, and so
far I can't find any condition where commit_delay>0 is materially better
than commit_delay=0, even under heavy load.  It's either the same or
much worse.  Numbers to follow...

			regards, tom lane

Sat, 17 Feb 2001 00:24:25 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance lossage in checkpoint dumping 
> > No, but I haven't looked at it.  I am now much more concerned with the
> > delay, and am wondering if I should start thinking about trying my idea
> > of looking for near-committers and post the patch to the list to see if
> > anyone likes it for 7.1 final.  Vadim will not be back in enough time to
> > write any new code in this area, I am afraid.
> 
> Near committers? *puzzled look*

Umm, uh, it means backends that have entered COMMIT and will be issuing
an fsync() of their own very soon.  It took me a while to remember what
I mean too because I was thinking of CVS committers.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 17 Feb 2001 00:34:43 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance lossage in checkpoint dumping
On Sat, 17 Feb 2001, Bruce Momjian wrote:

> > > No, but I haven't looked at it.  I am now much more concerned with the
> > > delay, and am wondering if I should start thinking about trying my idea
> > > of looking for near-committers and post the patch to the list to see if
> > > anyone likes it for 7.1 final.  Vadim will not be back in enough time to
> > > write any new code in this area, I am afraid.
> >
> > Near committers? *puzzled look*
>
> Umm, uh, it means backends that have entered COMMIT and will be issuing
> an fsync() of their own very soon.  It took me a while to remember what
> I mean too because I was thinking of CVS committers.

That's what I was thinking to, which was what was confusing the hell out
of me ... like, a near committer ... is that the guy sitting beside you
while you commit? :)



Sat, 17 Feb 2001 16:01:45 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: Performance lossage in checkpoint dumping
On Sat, 17 Feb 2001, Tom Lane wrote:

> The Hermit Hacker <scrappy@hub.org> writes:
> > No way to group the writes to you can keep the most recent one open?
> > Don't see an easy way, do you?
> >>
> >> No, but I haven't looked at it.  I am now much more concerned with the
> >> delay,
>
> I concur.  The blind write business is not important enough to hold up
> the release for --- for one thing, it has nothing to do with the pgbench
> results we're seeing, because these tests don't run long enough to
> include any checkpoint cycles.  The commit delay, on the other hand,
> is a big problem.
>
> >> and am wondering if I should start thinking about trying my idea
> >> of looking for near-committers and post the patch to the list to see if
> >> anyone likes it for 7.1 final.  Vadim will not be back in enough time to
> >> write any new code in this area, I am afraid.
>
> > Near committers? *puzzled look*
>
> Processes nearly ready to commit.  I'm thinking that any mechanism for
> detecting that might be overkill, however, especially compared to just
> setting commit_delay to zero by default.
>
> I've been sitting here running pgbench under various scenarios, and so
> far I can't find any condition where commit_delay>0 is materially better
> than commit_delay=0, even under heavy load.  It's either the same or
> much worse.  Numbers to follow...

Okay, if the whole commit_delay is purely means as a performance thing,
I'd say go with lowering the default to zero for v7.1, and once Vadim gets
back, we can properly determine why it appears to improve performance in
his case ... since I believe his OS of choice is FreeBSD, and you
mentioned doing tests on it, I can't see how he'd have a more fine
grain'd select() then you have for testing ...



Sat, 17 Feb 2001 16:06:00 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: Performance lossage in checkpoint dumping 