
> > Why not use the checkpointer itself inbetween checkpoints ?
> > use a min and a max dirty setting like Informix. Start writing
> > when more than max are dirty stop when at min. This avoids writing
> > single pages (which is slow, since it cannot be grouped together
> > by the OS).
> 
> Current approach is similar ... if I strech the IO and syncing over the 
> entire 150-300 second checkpoint interval, grouping in 50 pages then 
> sync()+nap, the system purr's pretty nice and without any peaks.

But how do you handle a write IO bound system then ? My thought was to 
let the checkpointer write dirty pages inbetween checkpoints with a min max,
but still try to do the checkpoint as fast as possible. I don't think
streching the checkpoint is good, since it needs to write hot pages, which the 
inbetween IO should avoid doing. The checkpointer would have two tasks,
that it handles alternately, checkpoint or flush LRU from max to min.

Andreas

Wed, 5 Nov 2003 20:37:50 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Experimental ARC implementation
Zeugswetter Andreas SB SD wrote:
>> > Why not use the checkpointer itself inbetween checkpoints ?
>> > use a min and a max dirty setting like Informix. Start writing
>> > when more than max are dirty stop when at min. This avoids writing
>> > single pages (which is slow, since it cannot be grouped together
>> > by the OS).
>> 
>> Current approach is similar ... if I strech the IO and syncing over the 
>> entire 150-300 second checkpoint interval, grouping in 50 pages then 
>> sync()+nap, the system purr's pretty nice and without any peaks.
> 
> But how do you handle a write IO bound system then ? My thought was to 
> let the checkpointer write dirty pages inbetween checkpoints with a min max,
> but still try to do the checkpoint as fast as possible. I don't think
> streching the checkpoint is good, since it needs to write hot pages, which the 
> inbetween IO should avoid doing. The checkpointer would have two tasks,
> that it handles alternately, checkpoint or flush LRU from max to min.
> 
> Andreas

By actually moving a lot of the IO work into the checkpointer. It asks 
the buffer strategy about the order in which dirty blocks would 
currently get evicted from the cache. The checkpointer now flushes them 
in that order. Your "hot pages" will be found at the end of that list 
and thus flushed last in the checkpoint, why it's good to keep them 
dirty longer.

The problem with the checkpointer flushing as fast as possible is, that 
the entire system literally freezes. In my tests I use something that 
resembles the transaction profile of a TPC-C including the thinking and 
keying times. Those are important as they are a very realistic thing. A 
stock 7.4.RC1 handles a right scaled DB with new_order response times of 
0.2 to 1.5 seconds, but when the checkpoint occurs, it can't keep up and 
the response times go up to anything between 20-60 seconds. What makes 
the situation worse is that in the meantime, all simulated terminals hit 
the "send" button again, which lead's to a transaction pileup right 
during the checkpoint. It takes a while until the system recovers from 
that.

If the system is write-bound, the checkpointer will find that many dirty 
blocks that he has no time to nap and will burst them out as fast as 
possible anyway. Well, at least that's the theory.

PostgreSQL with the non-overwriting storage concept can never have 
hot-written pages for a long time anyway, can it? They fill up and cool 
down until vacuum.


Jan

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


Wed, 05 Nov 2003 14:59:29 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: Experimental ARC implementation
Jan Wieck wrote:
> If the system is write-bound, the checkpointer will find that many dirty 
> blocks that he has no time to nap and will burst them out as fast as 
> possible anyway. Well, at least that's the theory.
> 
> PostgreSQL with the non-overwriting storage concept can never have 
> hot-written pages for a long time anyway, can it? They fill up and cool 
> down until vacuum.

Another idea on removing sync() --- if we are going to use fsync() on
each file during checkpoint (open, fsync, close), seems we could keep a
hash of written block dbid/relfilenode pairs and cycle through that on
checkpoint.  We could keep the hash in shared memory, and dump it to a
backing store when it gets full, or just have it exist in buffer writer
process memory (so it can grow) and have backends that do their own
buffer writes all open a single file in append mode and write the pairs
to the file, or something like that, and the checkpoint process can read
from there.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 6 Nov 2003 22:55:51 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Experimental ARC implementation
Hey - now that we have a branch, is Bruce going to start committed the 
pgpatches2 list?

Chris



Fri, 07 Nov 2003 12:06:37 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
CVS open for development?
Christopher Kings-Lynne wrote:
> Hey - now that we have a branch, is Bruce going to start committed the 
> pgpatches2 list?

Yes, once my email backlog is cleared --- probably early next week.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 6 Nov 2003 23:36:57 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: CVS open for development?
Bruce Momjian wrote:

> Jan Wieck wrote:
>> If the system is write-bound, the checkpointer will find that many dirty 
>> blocks that he has no time to nap and will burst them out as fast as 
>> possible anyway. Well, at least that's the theory.
>> 
>> PostgreSQL with the non-overwriting storage concept can never have 
>> hot-written pages for a long time anyway, can it? They fill up and cool 
>> down until vacuum.
> 
> Another idea on removing sync() --- if we are going to use fsync() on
> each file during checkpoint (open, fsync, close), seems we could keep a
> hash of written block dbid/relfilenode pairs and cycle through that on
> checkpoint.  We could keep the hash in shared memory, and dump it to a
> backing store when it gets full, or just have it exist in buffer writer
> process memory (so it can grow) and have backends that do their own
> buffer writes all open a single file in append mode and write the pairs
> to the file, or something like that, and the checkpoint process can read
> from there.
> 

I am not really aiming at removing sync() alltogether. We know already 
that open,fsync,close does not guarantee you flush dirty OS-buffers for 
which another process might so far only have done open,write. And you 
really don't want to remove all the vfd logic or fsync on every write 
done by a backend.

What doing frequent fdatasync/fsync during a constant ongoing checkpoint 
will cause is to significantly lower the physical write storm happening 
at the sync(), which is causing huge problems right now.

The reason why people blame vacuum that much is that not only does it 
replaces the buffer cache with useless garbage, it also leaves that 
garbage to be flushed by other backends or the checkpointer and it 
rapidly fills WAL, causing exactly that checkpoint we don't have the IO 
bandwidth for right now! They only see that vacuum is running, and if 
they kill it the system returns to a healty state after a while ... easy 
enought but only half the story.


Jan

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


Fri, 07 Nov 2003 10:45:50 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: Experimental ARC implementation
Jan Wieck wrote:
> Bruce Momjian wrote:
> 
> > Jan Wieck wrote:
> >> If the system is write-bound, the checkpointer will find that many dirty 
> >> blocks that he has no time to nap and will burst them out as fast as 
> >> possible anyway. Well, at least that's the theory.
> >> 
> >> PostgreSQL with the non-overwriting storage concept can never have 
> >> hot-written pages for a long time anyway, can it? They fill up and cool 
> >> down until vacuum.
> > 
> > Another idea on removing sync() --- if we are going to use fsync() on
> > each file during checkpoint (open, fsync, close), seems we could keep a
> > hash of written block dbid/relfilenode pairs and cycle through that on
> > checkpoint.  We could keep the hash in shared memory, and dump it to a
> > backing store when it gets full, or just have it exist in buffer writer
> > process memory (so it can grow) and have backends that do their own
> > buffer writes all open a single file in append mode and write the pairs
> > to the file, or something like that, and the checkpoint process can read
> > from there.
> > 
> 
> I am not really aiming at removing sync() alltogether. We know already 
> that open,fsync,close does not guarantee you flush dirty OS-buffers for 
> which another process might so far only have done open,write. And you 

We do know this?  How?  I thought someone listed the standard saying it
should work.  I need this for Win32 anyway.

> really don't want to remove all the vfd logic or fsync on every write 
> done by a backend.

No, certainly not --- that is a big loser.

> What doing frequent fdatasync/fsync during a constant ongoing checkpoint 
> will cause is to significantly lower the physical write storm happening 
> at the sync(), which is causing huge problems right now.

I don't see that frankly because sync() is syncing everying on that
machine, including other file systems.  Reducing our own load from sync
will not help with other applications writing to drives.

> The reason why people blame vacuum that much is that not only does it 
> replaces the buffer cache with useless garbage, it also leaves that 
> garbage to be flushed by other backends or the checkpointer and it 
> rapidly fills WAL, causing exactly that checkpoint we don't have the IO 
> bandwidth for right now! They only see that vacuum is running, and if 
> they kill it the system returns to a healty state after a while ... easy 
> enought but only half the story.

Right, vacuum triggers WAL/checkpoint.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 7 Nov 2003 10:59:26 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Experimental ARC implementation
Bruce Momjian wrote:

> Jan Wieck wrote:

>> What doing frequent fdatasync/fsync during a constant ongoing checkpoint 
>> will cause is to significantly lower the physical write storm happening 
>> at the sync(), which is causing huge problems right now.
> 
> I don't see that frankly because sync() is syncing everying on that
> machine, including other file systems.  Reducing our own load from sync
> will not help with other applications writing to drives.

You have 4 kids, Bruce. If you buy only two lollypops, how many of them 
can share the room unattended?

What I described is absolutely sufficient for a dedicated DB server. We 
will be able to coordinate the resources between the various components 
of PostgreSQL, no doubt. Everyone who has significant performance 
problems because of I/O saturation, and is still keeping other I/O heavy 
applications on the same box instead of separating the things, is either 
not serious or dumb ... or both.


Jan

PS: I know your kids can, but it serves too well ... ;-)

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


Fri, 07 Nov 2003 11:14:35 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: Experimental ARC implementation
Jan Wieck <JanWieck@Yahoo.com> writes:
> I am not really aiming at removing sync() alltogether.
> ...
> What doing frequent fdatasync/fsync during a constant ongoing checkpoint 
> will cause is to significantly lower the physical write storm happening 
> at the sync(), which is causing huge problems right now.

That's fair as far as solving the performance issue is concerned.  But
I think if we are going to muck about in this area, we ought to see
whether we can't remove the use of sync() altogether, because of the
issue of reliability.  When you depend on sync() for a checkpoint, you
really can't be sure that you are preserving the fundamental WAL
invariant --- you don't *know* that all the data writes have been done
before you write the checkpoint record to WAL.

I realize that reducing the amount of I/O that sync() triggers
would make this a lot less of a risk in practice than it is now, but
I'd still be happier if we could eliminate the risk rather than just
reduce it.

			regards, tom lane

Fri, 07 Nov 2003 11:36:55 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Experimental ARC implementation 
Jan Wieck wrote:
> Bruce Momjian wrote:
> 
> > Jan Wieck wrote:
> 
> >> What doing frequent fdatasync/fsync during a constant ongoing checkpoint 
> >> will cause is to significantly lower the physical write storm happening 
> >> at the sync(), which is causing huge problems right now.
> > 
> > I don't see that frankly because sync() is syncing everying on that
> > machine, including other file systems.  Reducing our own load from sync
> > will not help with other applications writing to drives.
> 
> You have 4 kids, Bruce. If you buy only two lollypops, how many of them 
> can share the room unattended?
> 
> What I described is absolutely sufficient for a dedicated DB server. We 
> will be able to coordinate the resources between the various components 
> of PostgreSQL, no doubt. Everyone who has significant performance 
> problems because of I/O saturation, and is still keeping other I/O heavy 
> applications on the same box instead of separating the things, is either 
> not serious or dumb ... or both.

Yes, but my point is two-fold  --- first, someone reported we can do
open, fsync, close reliably, second, I need that for Win32 (no sync),
and third, if we can handle the case for servers with other applications
on the box, why not do that?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 7 Nov 2003 11:53:21 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Experimental ARC implementation
Bruce Momjian <pgman@candle.pha.pa.us> writes:

> > I am not really aiming at removing sync() alltogether. We know already 
> > that open,fsync,close does not guarantee you flush dirty OS-buffers for 
> > which another process might so far only have done open,write. And you 

So for what it's worth, though the spec seems to indicate fsync is only
guaranteed to sync writes to that file descriptor, in reality all known VFS's
do not associated dirty buffers with particular file descriptors. 

At least I checked with people that NetBSD and Solaris do not. Both developers
said they were unaware of any OS that kept dirty buffers per-fd and couldn't
imagine anyone wanting to do that. It would be fairly easy to check Linux. All
the others out there are fairly closely related to either NetBSD or Solaris.

-- 
greg


07 Nov 2003 15:29:11 -0500
Greg Stark <gsstark@mit.edu>
Re: Experimental ARC implementation
Greg Stark wrote:

>Bruce Momjian <pgman@candle.pha.pa.us> writes:
>
>  
>
>>>I am not really aiming at removing sync() alltogether. We know already 
>>>that open,fsync,close does not guarantee you flush dirty OS-buffers for 
>>>which another process might so far only have done open,write. And you 
>>>      
>>>
>
>So for what it's worth, though the spec seems to indicate fsync is only
>guaranteed to sync writes to that file descriptor, in reality all known VFS's
>do not associated dirty buffers with particular file descriptors. 
>
>At least I checked with people that NetBSD and Solaris do not. Both developers
>said they were unaware of any OS that kept dirty buffers per-fd and couldn't
>imagine anyone wanting to do that. It would be fairly easy to check Linux. All
>the others out there are fairly closely related to either NetBSD or Solaris.
>  
>

The Linux man page for fsync says this:

       fsync copies all in-core parts of a file to disk, and waits  
until  the
       device  reports  that all parts are on stable storage.  It also 
updates
       metadata stat information.

which seems to imply similar behaviour on Linux as for other OSs. What I 
could understand of the source code in mm/filemap.c and fs/buffer.c 
appeared to confirm that.

cheers

andrew


Fri, 07 Nov 2003 15:53:26 -0500
Andrew Dunstan <andrew@dunslane.net>
Re: Experimental ARC implementation