I noticed this in the FreeBSD 5.1 release notes:

"A new DIRECTIO kernel option enables support for read operations that
bypass the buffer cache and put data directly into a userland buffer. This
feature requires that the O_DIRECT flag is set on the file descriptor and
that both the offset and length for the read operation are multiples of the
physical media sector size. [MERGED]"

MERGED means that it should also appear in FreeBSD 4.9.

Will PostgreSQL pick this up automatically, or do we need to add extra
checks?

Chris




Tue, 17 Jun 2003 14:21:11 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
O_DIRECT in freebsd
> I noticed this in the FreeBSD 5.1 release notes:
> 
> "A new DIRECTIO kernel option enables support for read operations
> that bypass the buffer cache and put data directly into a userland
> buffer. This feature requires that the O_DIRECT flag is set on the
> file descriptor and that both the offset and length for the read
> operation are multiples of the physical media sector size. [MERGED]"
> 
> MERGED means that it should also appear in FreeBSD 4.9.
> 
> Will PostgreSQL pick this up automatically, or do we need to add
> extra checks?

Extra checks, though I'm not sure why you'd want this.  This is the
equiv of a nice way of handling raw IO for read only
operations... which would be bad.  Call me crazy, but unless you're on
an embedded device with a non-existent FS cache or are Oracle and are
handling the IO buffer in user space, the buffer cache is what helps
speed up PostgreSQL since PostgreSQL leaves all of the caching
operations and optimization/alignment of pages up to the OS (much to
the behest of madvise() which could potentially speed up access of the
VM, but I won't get into that... see TODO.mmap).

-sc

-- 
Sean Chittenden

Tue, 17 Jun 2003 00:11:21 -0700
Sean Chittenden <sean@chittenden.org>
Re: O_DIRECT in freebsd
> > Will PostgreSQL pick this up automatically, or do we need to add
> > extra checks?
>
> Extra checks, though I'm not sure why you'd want this.  This is the
> equiv of a nice way of handling raw IO for read only
> operations... which would be bad.  Call me crazy, but unless you're on

The reason I mention it is that Postgres already supports O_DIRECT I think
on some other platforms (for whatever reason).

Chris


Tue, 17 Jun 2003 15:21:01 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: O_DIRECT in freebsd
On Tue, 17 Jun 2003, Christopher Kings-Lynne wrote:

> "A new DIRECTIO kernel option enables support for read operations that
> bypass the buffer cache and put data directly into a userland buffer....
>
> Will PostgreSQL pick this up automatically, or do we need to add extra
> checks?

You don't want it to. It's more efficent just to use mmap, because then
all the paging and caching issues are taken care of for you.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Tue, 17 Jun 2003 19:34:50 +0900 (JST)
Curt Sampson <cjs@cynic.net>
Re: O_DIRECT in freebsd
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> The reason I mention it is that Postgres already supports O_DIRECT I think
> on some other platforms (for whatever reason).

[ sounds of grepping... ]  No.  The only occurrence of O_DIRECT in the
source tree is in TODO:

* Consider use of open/fcntl(O_DIRECT) to minimize OS caching

I personally disagree with this TODO item for the same reason Sean
cited: Postgres is designed and tuned to rely on OS-level disk caching,
and bypassing that cache is far more likely to hurt our performance than
help it.

However, if someone wants to do some experimentation with O_DIRECT, I'd
be as interested as anyone to find out what happens...

			regards, tom lane

Tue, 17 Jun 2003 10:17:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: O_DIRECT in freebsd 
On Tue, 17 Jun 2003, Tom Lane wrote:

> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > The reason I mention it is that Postgres already supports O_DIRECT I think
> > on some other platforms (for whatever reason).
> 
> [ sounds of grepping... ]  No.  The only occurrence of O_DIRECT in the
> source tree is in TODO:
> 
> * Consider use of open/fcntl(O_DIRECT) to minimize OS caching
> 
> I personally disagree with this TODO item for the same reason Sean
> cited: Postgres is designed and tuned to rely on OS-level disk caching,
> and bypassing that cache is far more likely to hurt our performance than
> help it.

DB2 and Oracle, from memory, allow users to pass hints to the planner to
use/not use file system caching. This could be useful if you had an
application retrieving a large amount of data on an adhoc basis. The large
retrieval would empty out the disk cache there by negatively impacting
upon other applications operating on data which should be cached.

Gavin


Wed, 18 Jun 2003 10:01:37 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: O_DIRECT in freebsd 
On Wed, Jun 18, 2003 at 10:01:37AM +1000, Gavin Sherry wrote:
> On Tue, 17 Jun 2003, Tom Lane wrote:
> > * Consider use of open/fcntl(O_DIRECT) to minimize OS caching
> > 
> > I personally disagree with this TODO item for the same reason Sean
> > cited: Postgres is designed and tuned to rely on OS-level disk caching,
> > and bypassing that cache is far more likely to hurt our performance than
> > help it.
> 
> DB2 and Oracle, from memory, allow users to pass hints to the planner to
> use/not use file system caching. This could be useful if you had an
> application retrieving a large amount of data on an adhoc basis. The large
> retrieval would empty out the disk cache there by negatively impacting
> upon other applications operating on data which should be cached.
 
Might it make sense to do this for on-disk sorts, since sort_mem is
essentially being used as a disk cache (at least for reads)?
-- 
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Wed, 18 Jun 2003 00:07:36 -0500
"Jim C. Nasby" <jim@nasby.net>
Re: O_DIRECT in freebsd
"Jim C. Nasby" <jim@nasby.net> writes:
>> DB2 and Oracle, from memory, allow users to pass hints to the planner to
>> use/not use file system caching.
 
> Might it make sense to do this for on-disk sorts, since sort_mem is
> essentially being used as a disk cache (at least for reads)?

If sort_mem were actually being used that way, it might be ... but it
isn't, and so I doubt O_DIRECT would be an improvement.  It seems more
likely to force disk I/O that otherwise might not happen at all, if the
kernel happens to have sufficient buffer space on hand.

I'll concede though that a large sort would probably have the effect of
blowing out the kernel's disk cache.  So while O_DIRECT might be a net
pessimization as far as the sort itself is concerned, it would probably
be more friendly to the rest of the system, by leaving disk buffers free
for more productive uses.  It'd all depend on your workload ...

			regards, tom lane

Wed, 18 Jun 2003 10:00:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: O_DIRECT in freebsd 

Also, keep in mind writes to O_DIRECT devices have to wait for the data
to get on the platters rather than into the kernel cache.

---------------------------------------------------------------------------

Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.net> writes:
> >> DB2 and Oracle, from memory, allow users to pass hints to the planner to
> >> use/not use file system caching.
>  
> > Might it make sense to do this for on-disk sorts, since sort_mem is
> > essentially being used as a disk cache (at least for reads)?
> 
> If sort_mem were actually being used that way, it might be ... but it
> isn't, and so I doubt O_DIRECT would be an improvement.  It seems more
> likely to force disk I/O that otherwise might not happen at all, if the
> kernel happens to have sufficient buffer space on hand.
> 
> I'll concede though that a large sort would probably have the effect of
> blowing out the kernel's disk cache.  So while O_DIRECT might be a net
> pessimization as far as the sort itself is concerned, it would probably
> be more friendly to the rest of the system, by leaving disk buffers free
> for more productive uses.  It'd all depend on your workload ...
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
  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

Wed, 18 Jun 2003 10:05:34 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: O_DIRECT in freebsd
On Wed, Jun 18, 2003 at 10:01:37AM +1000, Gavin Sherry wrote:
> On Tue, 17 Jun 2003, Tom Lane wrote:
> 
> > "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > > The reason I mention it is that Postgres already supports O_DIRECT I think
> > > on some other platforms (for whatever reason).
> > 
> > [ sounds of grepping... ]  No.  The only occurrence of O_DIRECT in the
> > source tree is in TODO:
> > 
> > * Consider use of open/fcntl(O_DIRECT) to minimize OS caching
> > 
> > I personally disagree with this TODO item for the same reason Sean
> > cited: Postgres is designed and tuned to rely on OS-level disk caching,
> > and bypassing that cache is far more likely to hurt our performance than
> > help it.
> 
> DB2 and Oracle, from memory, allow users to pass hints to the planner to
> use/not use file system caching. This could be useful if you had an
> application retrieving a large amount of data on an adhoc basis. The large
> retrieval would empty out the disk cache there by negatively impacting
> upon other applications operating on data which should be cached.
 
I've recently been bitten by this. On DB2, I could change what
bufferpool the large tables were using and set it fairly small, but
obviously not an option with PGSQL. But, if pgsql could stop caching
from occuring on user-specified queries, large table or index scans,
etc., it would be very helpful.
-- 
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Fri, 20 Jun 2003 16:58:56 -0500
"Jim C. Nasby" <jim@nasby.net>
Re: O_DIRECT in freebsd
> > > > The reason I mention it is that Postgres already supports
> > > > O_DIRECT I think on some other platforms (for whatever
> > > > reason).
> > > 
> > > [ sounds of grepping... ]  No.  The only occurrence of O_DIRECT in the
> > > source tree is in TODO:
> > > 
> > > * Consider use of open/fcntl(O_DIRECT) to minimize OS caching
> > > 
> > > I personally disagree with this TODO item for the same reason
> > > Sean cited: Postgres is designed and tuned to rely on OS-level
> > > disk caching, and bypassing that cache is far more likely to
> > > hurt our performance than help it.
> > 
> > DB2 and Oracle, from memory, allow users to pass hints to the
> > planner to use/not use file system caching. This could be useful
> > if you had an application retrieving a large amount of data on an
> > adhoc basis. The large retrieval would empty out the disk cache
> > there by negatively impacting upon other applications operating on
> > data which should be cached.
>  
> I've recently been bitten by this. On DB2, I could change what
> bufferpool the large tables were using and set it fairly small, but
> obviously not an option with PGSQL. But, if pgsql could stop caching
> from occuring on user-specified queries, large table or index scans,
> etc., it would be very helpful.

Actually, now that I think about this, if the planner is going to read
more than X number of bytes as specified in a GUC, it would be useful
to have the fd marked as O_DIRECT to avoid polluting the disk
cache... I have a few tables with about 300M rows (~9GB on disk) that
I have to perform nightly seq scans over for reports and it does wipe
out some of the other fast movers that come through and depend on the
disk cache to be there for their speed.  Because they're performed in
the middle of the night, I don't care that much, but my avg query
times during that period of time are slower... whether it's load or
the disk buffer being emptied and having to be refilled, I'm not sure,
but thinking about it, use of a GUC threshold to have an FD marked as
O_DIRECT does make sense (0 == disabled and the default, but tunable
in Kbytes as an admin sees fit) and could be nice for big queries that
have lots of smaller queries running around at the same time.

-sc

-- 
Sean Chittenden

Sun, 22 Jun 2003 09:21:19 -0700
Sean Chittenden <sean@chittenden.org>
Re: O_DIRECT in freebsd

What you really want is Solaris's free-behind, where it detects if a
scan is exceeding a certain percentage of the OS cache and moves the
pages to the _front_ of the to-be-reused list.  I am not sure what other
OS's support this, but we need this on our own buffer manager code as
well.

Our TODO already has:

	* Add free-behind capability for large sequential scans (Bruce)

Basically, I think we need free-behind rather than O_DIRECT.

---------------------------------------------------------------------------

Sean Chittenden wrote:
> > > > > The reason I mention it is that Postgres already supports
> > > > > O_DIRECT I think on some other platforms (for whatever
> > > > > reason).
> > > > 
> > > > [ sounds of grepping... ]  No.  The only occurrence of O_DIRECT in the
> > > > source tree is in TODO:
> > > > 
> > > > * Consider use of open/fcntl(O_DIRECT) to minimize OS caching
> > > > 
> > > > I personally disagree with this TODO item for the same reason
> > > > Sean cited: Postgres is designed and tuned to rely on OS-level
> > > > disk caching, and bypassing that cache is far more likely to
> > > > hurt our performance than help it.
> > > 
> > > DB2 and Oracle, from memory, allow users to pass hints to the
> > > planner to use/not use file system caching. This could be useful
> > > if you had an application retrieving a large amount of data on an
> > > adhoc basis. The large retrieval would empty out the disk cache
> > > there by negatively impacting upon other applications operating on
> > > data which should be cached.
> >  
> > I've recently been bitten by this. On DB2, I could change what
> > bufferpool the large tables were using and set it fairly small, but
> > obviously not an option with PGSQL. But, if pgsql could stop caching
> > from occuring on user-specified queries, large table or index scans,
> > etc., it would be very helpful.
> 
> Actually, now that I think about this, if the planner is going to read
> more than X number of bytes as specified in a GUC, it would be useful
> to have the fd marked as O_DIRECT to avoid polluting the disk
> cache... I have a few tables with about 300M rows (~9GB on disk) that
> I have to perform nightly seq scans over for reports and it does wipe
> out some of the other fast movers that come through and depend on the
> disk cache to be there for their speed.  Because they're performed in
> the middle of the night, I don't care that much, but my avg query
> times during that period of time are slower... whether it's load or
> the disk buffer being emptied and having to be refilled, I'm not sure,
> but thinking about it, use of a GUC threshold to have an FD marked as
> O_DIRECT does make sense (0 == disabled and the default, but tunable
> in Kbytes as an admin sees fit) and could be nice for big queries that
> have lots of smaller queries running around at the same time.
> 
> -sc
> 
> -- 
> Sean Chittenden
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

-- 
  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

Sun, 22 Jun 2003 18:15:52 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: O_DIRECT in freebsd
> What you really want is Solaris's free-behind, where it detects if a
> scan is exceeding a certain percentage of the OS cache and moves the
> pages to the _front_ of the to-be-reused list.  I am not sure what
> other OS's support this, but we need this on our own buffer manager
> code as well.
> 
> Our TODO already has:
> 
> 	* Add free-behind capability for large sequential scans (Bruce)
> 
> Basically, I think we need free-behind rather than O_DIRECT.

I suppose, but you've already polluted the cache by the time the above
mentioned mechanism kicks in and takes effect.  Given that the planner
has an idea of how much data it's going to read in in order to
complete the query, seems easier/better to mark the fd O_DIRECT.
*shrug*

-sc

-- 
Sean Chittenden

Sun, 22 Jun 2003 15:18:30 -0700
Sean Chittenden <sean@chittenden.org>
Re: O_DIRECT in freebsd
Sean Chittenden wrote:
> > What you really want is Solaris's free-behind, where it detects if a
> > scan is exceeding a certain percentage of the OS cache and moves the
> > pages to the _front_ of the to-be-reused list.  I am not sure what
> > other OS's support this, but we need this on our own buffer manager
> > code as well.
> > 
> > Our TODO already has:
> > 
> > 	* Add free-behind capability for large sequential scans (Bruce)
> > 
> > Basically, I think we need free-behind rather than O_DIRECT.
> 
> I suppose, but you've already polluted the cache by the time the above
> mentioned mechanism kicks in and takes effect.  Given that the planner
> has an idea of how much data it's going to read in in order to
> complete the query, seems easier/better to mark the fd O_DIRECT.
> *shrug*

_That_ is an excellent point.  However, do we know at the time we open
the file descriptor if we will be doing this?  What about cache
coherency problems with other backends not opening with O_DIRECT?  And
finally, how do we deal with the fact that writes to O_DIRECT files will
wait until the data hits the disk because there is no kernel buffer cache?

-- 
  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

Sun, 22 Jun 2003 18:28:15 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: O_DIRECT in freebsd
> > > What you really want is Solaris's free-behind, where it detects
> > > if a scan is exceeding a certain percentage of the OS cache and
> > > moves the pages to the _front_ of the to-be-reused list.  I am
> > > not sure what other OS's support this, but we need this on our
> > > own buffer manager code as well.
> > > 
> > > Our TODO already has:
> > > 
> > > 	* Add free-behind capability for large sequential scans (Bruce)
> > > 
> > > Basically, I think we need free-behind rather than O_DIRECT.
> > 
> > I suppose, but you've already polluted the cache by the time the
> > above mentioned mechanism kicks in and takes effect.  Given that
> > the planner has an idea of how much data it's going to read in in
> > order to complete the query, seems easier/better to mark the fd
> > O_DIRECT.  *shrug*
> 
> _That_ is an excellent point.  However, do we know at the time we
> open the file descriptor if we will be doing this?

Doesn't matter, it's an option to fcntl().

> What about cache coherency problems with other backends not opening
> with O_DIRECT?

That's a problem for the kernel VM, if you mean cache coherency in the
VM.  If you mean inside of the backend, that could be a stickier
issue, I think.  I don't know enough of the internals yet to know if
this is a problem or not, but you're right, it's certainly something
to consider.  Is the cache a write behind cache or is it a read
through cache?  If it's a read through cache, which I think it is,
then the backend would have to dirty all cache entries pertaining to
the relations being opened with O_DIRECT.  The use case for that
being:

1) a transaction begins
2) a few rows out of the huge table are read
3) a huge query is performed that triggers the use of O_DIRECT
4) the rows selected in step 2 are updated (this step should poison or
   update the cache, actually, and act as a write through cache if the
   data is in the cache)
5) the same few rows are read in again
6) transaction is committed

Provided the cache is poisoned or updated in step 4, I can't see how
or where this would be a problem.  Please enlighten if there's a
different case that would need to be taken into account.  I can't
imagine ever wanting to write out data using O_DIRECT and think that
it's a read only optimization in an attempt to minimize the turn over
in the OS's cache.  From fcntl(2):

     O_DIRECT     Minimize or eliminate the cache effects of reading and writ-
                  ing.  The system will attempt to avoid caching the data you
                  read or write.  If it cannot avoid caching the data, it will
                  minimize the impact the data has on the cache.  Use of this
                  flag can drastically reduce performance if not used with
                  care.


> And finally, how do we deal with the fact that writes to O_DIRECT
> files will wait until the data hits the disk because there is no
> kernel buffer cache?

Well, two things.

1) O_DIRECT should never be used on writes... I can't think of a case
   where you'd want it off, even when COPY'ing data and restoring a
   DB, it just doesn't make sense to use it.  The write buffer is
   emptied as soon as the pages hit the disk unless something is
   reading those bits, but I'd imagine the write buffer would be used
   to make sure that as much writing is done to the platter in a
   single write by the OS as possible, circumventing that would be
   insane (though useful possibly for embedded devices with low RAM,
   solid state drives, or some super nice EMC fiber channel storage
   device that basically has its own huge disk cache).

2) Last I checked PostgreSQL wasn't a threaded app and doesn't use
   non-blocking IO.  The backend would block until the call returns,
   where's the problem?  :)

If anything O_DIRECT would shake out any bugs in PostgreSQL's caching
code, if there are any.  -sc

-- 
Sean Chittenden

Sun, 22 Jun 2003 15:49:43 -0700
Sean Chittenden <sean@chittenden.org>
Re: O_DIRECT in freebsd

Basically, we don't know when we read a buffer whether this is a
read-only or read/write.  In fact, we could read it in, and another
backend could write it for us.

The big issue is that when we do a write, we don't wait for it to get to
disk.

It seems to use O_DIRECT, we would have to read the buffer in a special
way to mark it as read-only, which seems kind of strange.  I see no
reason we can't use free-behind in the PostgreSQL buffer cache to handle
most of the benefits of O_DIRECT, without the read-only buffer restriction.

---------------------------------------------------------------------------

Sean Chittenden wrote:
> > _That_ is an excellent point.  However, do we know at the time we
> > open the file descriptor if we will be doing this?
> 
> Doesn't matter, it's an option to fcntl().
> 
> > What about cache coherency problems with other backends not opening
> > with O_DIRECT?
> 
> That's a problem for the kernel VM, if you mean cache coherency in the
> VM.  If you mean inside of the backend, that could be a stickier
> issue, I think.  I don't know enough of the internals yet to know if
> this is a problem or not, but you're right, it's certainly something
> to consider.  Is the cache a write behind cache or is it a read
> through cache?  If it's a read through cache, which I think it is,
> then the backend would have to dirty all cache entries pertaining to
> the relations being opened with O_DIRECT.  The use case for that
> being:
> 
> 1) a transaction begins
> 2) a few rows out of the huge table are read
> 3) a huge query is performed that triggers the use of O_DIRECT
> 4) the rows selected in step 2 are updated (this step should poison or
>    update the cache, actually, and act as a write through cache if the
>    data is in the cache)
> 5) the same few rows are read in again
> 6) transaction is committed
> 
> Provided the cache is poisoned or updated in step 4, I can't see how
> or where this would be a problem.  Please enlighten if there's a
> different case that would need to be taken into account.  I can't
> imagine ever wanting to write out data using O_DIRECT and think that
> it's a read only optimization in an attempt to minimize the turn over
> in the OS's cache.  From fcntl(2):
> 
>      O_DIRECT     Minimize or eliminate the cache effects of reading and writ-
>                   ing.  The system will attempt to avoid caching the data you
>                   read or write.  If it cannot avoid caching the data, it will
>                   minimize the impact the data has on the cache.  Use of this
>                   flag can drastically reduce performance if not used with
>                   care.
> 
> 
> > And finally, how do we deal with the fact that writes to O_DIRECT
> > files will wait until the data hits the disk because there is no
> > kernel buffer cache?
> 
> Well, two things.
> 
> 1) O_DIRECT should never be used on writes... I can't think of a case
>    where you'd want it off, even when COPY'ing data and restoring a
>    DB, it just doesn't make sense to use it.  The write buffer is
>    emptied as soon as the pages hit the disk unless something is
>    reading those bits, but I'd imagine the write buffer would be used
>    to make sure that as much writing is done to the platter in a
>    single write by the OS as possible, circumventing that would be
>    insane (though useful possibly for embedded devices with low RAM,
>    solid state drives, or some super nice EMC fiber channel storage
>    device that basically has its own huge disk cache).
> 
> 2) Last I checked PostgreSQL wasn't a threaded app and doesn't use
>    non-blocking IO.  The backend would block until the call returns,
>    where's the problem?  :)
> 
> If anything O_DIRECT would shake out any bugs in PostgreSQL's caching
> code, if there are any.  -sc
> 
> -- 
> Sean Chittenden
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
  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

Sun, 22 Jun 2003 19:50:48 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: O_DIRECT in freebsd
> Basically, we don't know when we read a buffer whether this is a
> read-only or read/write.  In fact, we could read it in, and another
> backend could write it for us.

Um, wait.  The cache is shared between backends?  I don't think so,
but it shouldn't matter because there has to be a semaphore locking
the cache to prevent the coherency issue you describe.  If PostgreSQL
didn't, it'd be having problems with this now.  I'd also think that
MVCC would handle the case of updated data in the cache as that has to
be a common case.  At what point is the cached result invalidated and
fetched from the OS?

> The big issue is that when we do a write, we don't wait for it to
> get to disk.

Only in the case when fsync() is turned off, but again, that's up to
the OS to manage that can of worms, which I think BSD takes care of
that.  From conf/NOTES:

# Attempt to bypass the buffer cache and put data directly into the
# userland buffer for read operation when O_DIRECT flag is set on the
# file.  Both offset and length of the read operation must be
# multiples of the physical media sector size.
#
#options                DIRECTIO

The offsets and length bit kinda bothers me though, but I thin that's
stuff that the ernel has to take into account, not the userland calls,
I wonder if that's actually accurate any more or affects userland
calls...  seems like that'd be a bit too much work to have the user
do, esp given the lack of documentation on the flag... should be just
drop in additional flag, afaict.

> It seems to use O_DIRECT, we would have to read the buffer in a
> special way to mark it as read-only, which seems kind of strange.  I
> see no reason we can't use free-behind in the PostgreSQL buffer
> cache to handle most of the benefits of O_DIRECT, without the
> read-only buffer restriction.

I don't see how this'd be an issue as buffers populated via a read(),
that are updated, and then written out, would occupy a new chunk of
disk to satisfy MVCC.  Why would we need to mark a buffer as read only
and carry around/check its state?

-sc

-- 
Sean Chittenden

Sun, 22 Jun 2003 17:31:29 -0700
Sean Chittenden <sean@chittenden.org>
Re: O_DIRECT in freebsd
Sean Chittenden wrote:
> > Basically, we don't know when we read a buffer whether this is a
> > read-only or read/write.  In fact, we could read it in, and another
> > backend could write it for us.
> 
> Um, wait.  The cache is shared between backends?  I don't think so,
> but it shouldn't matter because there has to be a semaphore locking
> the cache to prevent the coherency issue you describe.  If PostgreSQL
> didn't, it'd be having problems with this now.  I'd also think that
> MVCC would handle the case of updated data in the cache as that has to
> be a common case.  At what point is the cached result invalidated and
> fetched from the OS?

Uh, it's called the _shared_ buffer cache in postgresql.conf, and we
lock pages only while we are reading/writing them, not for the duration
they are in the cache.

> > The big issue is that when we do a write, we don't wait for it to
> > get to disk.
> 
> Only in the case when fsync() is turned off, but again, that's up to
> the OS to manage that can of worms, which I think BSD takes care of
> that.  From conf/NOTES:

Nope.  When you don't have a kernel buffer cache, and you do a write,
where do you expect it to go?  I assume it goes to the drive, and you
have to wait for that.

> 
> # Attempt to bypass the buffer cache and put data directly into the
> # userland buffer for read operation when O_DIRECT flag is set on the
> # file.  Both offset and length of the read operation must be
> # multiples of the physical media sector size.
> #
> #options                DIRECTIO
> 
> The offsets and length bit kinda bothers me though, but I thin that's
> stuff that the ernel has to take into account, not the userland calls,
> I wonder if that's actually accurate any more or affects userland
> calls...  seems like that'd be a bit too much work to have the user
> do, esp given the lack of documentation on the flag... should be just
> drop in additional flag, afaict.
> 
> > It seems to use O_DIRECT, we would have to read the buffer in a
> > special way to mark it as read-only, which seems kind of strange.  I
> > see no reason we can't use free-behind in the PostgreSQL buffer
> > cache to handle most of the benefits of O_DIRECT, without the
> > read-only buffer restriction.
> 
> I don't see how this'd be an issue as buffers populated via a read(),
> that are updated, and then written out, would occupy a new chunk of
> disk to satisfy MVCC.  Why would we need to mark a buffer as read only
> and carry around/check its state?

We update the expired flags on the tuple during update/delete.

-- 
  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

Sun, 22 Jun 2003 20:42:45 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: O_DIRECT in freebsd
> > > Basically, we don't know when we read a buffer whether this is a
> > > read-only or read/write.  In fact, we could read it in, and
> > > another backend could write it for us.
> > 
> > Um, wait.  The cache is shared between backends?  I don't think
> > so, but it shouldn't matter because there has to be a semaphore
> > locking the cache to prevent the coherency issue you describe.  If
> > PostgreSQL didn't, it'd be having problems with this now.  I'd
> > also think that MVCC would handle the case of updated data in the
> > cache as that has to be a common case.  At what point is the
> > cached result invalidated and fetched from the OS?
> 
> Uh, it's called the _shared_ buffer cache in postgresql.conf, and we
> lock pages only while we are reading/writing them, not for the duration
> they are in the cache.

*smacks forhead* Duh, you're right.  I always just turn up the FS
cache in the OS instead.

The shared buffer cache has got to have enormous churn though if
everything ends up in the userland cache.  Is it really an exhaustive
cache?  I thought the bulk of the caching happened in the kernel and
not in the userland.  Is the userland cache just for the SysCache and
friends, or does it cache everything that moves through PostgreSQL?

> > > The big issue is that when we do a write, we don't wait for it
> > > to get to disk.
> > 
> > Only in the case when fsync() is turned off, but again, that's up to
> > the OS to manage that can of worms, which I think BSD takes care of
> > that.  From conf/NOTES:
> 
> Nope.  When you don't have a kernel buffer cache, and you do a
> write, where do you expect it to go?  I assume it goes to the drive,
> and you have to wait for that.

Correct, a write call blocks until the bits hit the disk in the
absence of lack of enough buffer space.  In the event of enough
buffer, however, the buffer houses the bits until written to disk and
the kernel returns control to the userland app.

Consencus is that FreeBSD does the right thing and hands back data
from the FS buffer even though the fd was marked O_DIRECT (see
bottom).

> > I don't see how this'd be an issue as buffers populated via a
> > read(), that are updated, and then written out, would occupy a new
> > chunk of disk to satisfy MVCC.  Why would we need to mark a buffer
> > as read only and carry around/check its state?
> 
> We update the expired flags on the tuple during update/delete.

*nods* Okay, I don't see where the problem would be then with
O_DIRECT.  I'm going to ask Dillion about O_DIRECT since he
implemented it, likely for the backplane database that he's writing.
I'll let 'ya know what he says.

-sc


Here's a snip from the conv I had with someone that has mega vfs foo
in FreeBSD:

17:58  * seanc has a question about O_DIRECT
17:58 <@zb^3> ask
17:59 <@seanc> assume two procs have a file open, one proc writes using
               buffered IO, the other uses O_DIRECT to read from the file, is
               read() smart enough to hand back the data in the buffer that
               hasn't hit the disk yet or will there be syncing issues?
18:00 <@zb^3> O_DIRECT in the incarnation from matt dillon will break shit
18:00 <@zb^3> basically, any data read will be set non-cacheable
18:01 <@zb^3> and you'll experience writes earlier than you should
18:01 <@seanc> zb^3: hrm, I don't want to write to the fd + O_DIRECT though
18:02 <@seanc> zb^3: basically you're saying an O_DIRECT fd doesn't consult the
               FS cache before reading from disk?
18:03 <@zb^3> no, it does
18:03 <@zb^3> but it immediately puts any read blocks on the ass end of the LRU
18:03 <@zb^3> so if you write a block, then read it with O_DIRECT it will get
              written out early :(
18:04 <@seanc> zb^3: ah, got it...  it's not a data coherency issue, it's a
               priority issue and O_DIRECT makes writes jump the gun
18:04 <@seanc> got it
18:05 <@seanc> zb^3: is that required in the implementation or is it a bug?
18:06  * seanc is wondering whether or not he should bug dillion about this to
          get things working correctly
18:07 <@zb^3> it's a bug in the implementation
18:08 <@zb^3> to fix it you have to pass flags all the way down into the
              getblk-like layer
18:08 <@zb^3> and dillon was opposed to that
18:09 <@seanc> zb^3: hrm, thx... I'll go bug him about it now and see what's up
               in backplane land

-- 
Sean Chittenden

Sun, 22 Jun 2003 18:12:47 -0700
Sean Chittenden <sean@chittenden.org>
Re: O_DIRECT in freebsd
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Basically, I think we need free-behind rather than O_DIRECT.

There are two separate issues here --- one is what's happening in our
own cache, and one is what's happening in the kernel disk cache.
Implementing our own free-behind code would help in our own cache but
does nothing for the kernel cache.

My thought on this is that for large seqscans we could think about
doing reads through a file descriptor that's opened with O_DIRECT.
But writes should never go through O_DIRECT.  In some scenarios this
would mean having two FDs open for the same relation file.  This'd
require moderately extensive changes to the smgr-related APIs, but
it doesn't seem totally out of the question.  I'd kinda like to see
some experimental evidence that it's worth doing though.  Anyone
care to make a quick-hack prototype and do some measurements?

			regards, tom lane

Sun, 22 Jun 2003 22:25:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: O_DIRECT in freebsd 
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> _That_ is an excellent point.  However, do we know at the time we open
> the file descriptor if we will be doing this?

We'd have to say on a per-read basis whether we want O_DIRECT or not,
and fd.c would need to provide a suitable file descriptor.

> What about cache
> coherency problems with other backends not opening with O_DIRECT?

If O_DIRECT introduces cache coherency problems against other processes
not using O_DIRECT then the whole idea is a nonstarter, but I can't
imagine any kernel hackers would have been stupid enough to allow that
to happen ...

			regards, tom lane

Sun, 22 Jun 2003 22:28:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: O_DIRECT in freebsd 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Basically, I think we need free-behind rather than O_DIRECT.
> 
> There are two separate issues here --- one is what's happening in our
> own cache, and one is what's happening in the kernel disk cache.
> Implementing our own free-behind code would help in our own cache but
> does nothing for the kernel cache.

Right.

> My thought on this is that for large seqscans we could think about
> doing reads through a file descriptor that's opened with O_DIRECT.
> But writes should never go through O_DIRECT.  In some scenarios this
> would mean having two FDs open for the same relation file.  This'd
> require moderately extensive changes to the smgr-related APIs, but
> it doesn't seem totally out of the question.  I'd kinda like to see
> some experimental evidence that it's worth doing though.  Anyone
> care to make a quick-hack prototype and do some measurements?

True, it is a cost/benefit issue.  My assumption was that once we have
free-behind in the PostgreSQL shared buffer cache, the kernel cache
issues would be minimal, but I am willing to be found wrong.

-- 
  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

Sun, 22 Jun 2003 22:43:36 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: O_DIRECT in freebsd
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > _That_ is an excellent point.  However, do we know at the time we open
> > the file descriptor if we will be doing this?
> 
> We'd have to say on a per-read basis whether we want O_DIRECT or not,
> and fd.c would need to provide a suitable file descriptor.

OK

> > What about cache
> > coherency problems with other backends not opening with O_DIRECT?
> 
> If O_DIRECT introduces cache coherency problems against other processes
> not using O_DIRECT then the whole idea is a nonstarter, but I can't
> imagine any kernel hackers would have been stupid enough to allow that
> to happen ...

Seeing how the buffer exists in user space, I would be interested how
they prevent coherency problems with good performance --- maybe they map
the same page into multiple processes --- that would be interesting,
though it would require some locking.

-- 
  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

Sun, 22 Jun 2003 22:45:06 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: O_DIRECT in freebsd
> > Basically, I think we need free-behind rather than O_DIRECT.
> 
> There are two separate issues here --- one is what's happening in
> our own cache, and one is what's happening in the kernel disk cache.
> Implementing our own free-behind code would help in our own cache
> but does nothing for the kernel cache.
> 
> My thought on this is that for large seqscans we could think about
> doing reads through a file descriptor that's opened with O_DIRECT.
> But writes should never go through O_DIRECT.  In some scenarios this
> would mean having two FDs open for the same relation file.  This'd
> require moderately extensive changes to the smgr-related APIs, but
> it doesn't seem totally out of the question.  I'd kinda like to see
> some experimental evidence that it's worth doing though.  Anyone
> care to make a quick-hack prototype and do some measurements?

What would you like to measure?  Overall system performance when a
query is using O_DIRECT or are you looking for negative/postitve
impact of read() not using the FS cache?  The latter is much easier to
do than the former...  recreating a valid load environment that'd let
any O_DIRECT benchmark be useful isn't trivial.

-sc

-- 
Sean Chittenden

Sun, 22 Jun 2003 19:46:56 -0700
Sean Chittenden <sean@chittenden.org>
Re: O_DIRECT in freebsd
> > > What about cache coherency problems with other backends not
> > > opening with O_DIRECT?
> > 
> > If O_DIRECT introduces cache coherency problems against other
> > processes not using O_DIRECT then the whole idea is a nonstarter,
> > but I can't imagine any kernel hackers would have been stupid
> > enough to allow that to happen ...

Cache coherency isn't an issue, read() called on a fd that has
O_DIRECT set on it will pull data from the FS cache if a write has
occurred to the file pointed to by the reading/writing fd (though the
write of that page gets done immediately and isn't postponed,
rationale for that is still pending).

> Seeing how the buffer exists in user space, I would be interested
> how they prevent coherency problems with good performance --- maybe
> they map the same page into multiple processes --- that would be
> interesting, though it would require some locking.

It's in src/kern/vfs_bio.c, src/ufs/ffs_vnops.c, src/ufs/ffs_rawread.c

http://lists.freebsd.org/pipermail/cvs-all/2003-March/000226.html

phk's likely to revamp some of the FS buffer cache in FreeBSD 6, but
the userland API will remain stable (it'll just get more efficient in
6, not that it's not fast as is).

http://lists.freebsd.org/pipermail/cvs-all/2003-March/000261.html

-sc

-- 
Sean Chittenden

Sun, 22 Jun 2003 20:00:39 -0700
Sean Chittenden <sean@chittenden.org>
Re: O_DIRECT in freebsd
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> True, it is a cost/benefit issue.  My assumption was that once we have
> free-behind in the PostgreSQL shared buffer cache, the kernel cache
> issues would be minimal, but I am willing to be found wrong.

If you are running on the small-shared-buffers-and-large-kernel-cache
theory, then getting the kernel cache to behave right is much more
important than making the PG cache behave right.  If you favor the
other theory then free-behind in the PG cache is the important thing.
However, I've not yet seen any convincing evidence that large PG
cache with small kernel cache is the way to go.

The scenario that I'm envisioning would require passing a this-is-a-
seqscan-read flag down through bufmgr to smgr to fd.c.  So the info
would be available to bufmgr to implement a free-behind policy.  But
I suspect that's not really where any big win would come from.

			regards, tom lane

Sun, 22 Jun 2003 23:05:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: O_DIRECT in freebsd 
Sean Chittenden <sean@chittenden.org> writes:
>> it doesn't seem totally out of the question.  I'd kinda like to see
>> some experimental evidence that it's worth doing though.  Anyone
>> care to make a quick-hack prototype and do some measurements?

> What would you like to measure?  Overall system performance when a
> query is using O_DIRECT or are you looking for negative/postitve
> impact of read() not using the FS cache?  The latter is much easier to
> do than the former...  recreating a valid load environment that'd let
> any O_DIRECT benchmark be useful isn't trivial.

If this stuff were easy, we'd have done it already ;-).  The first
problem is to figure out what makes sense to measure.

Given that the request is for a quick-and-dirty test, I'd be willing to
cut you some slack on the measurement process.  That is, it's okay to
pick something easier to measure over something harder to measure, as
long as you can make a fair argument that what you're measuring is of
any interest at all...

			regards, tom lane

Sun, 22 Jun 2003 23:13:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: O_DIRECT in freebsd 
> > True, it is a cost/benefit issue.  My assumption was that once we have
> > free-behind in the PostgreSQL shared buffer cache, the kernel cache
> > issues would be minimal, but I am willing to be found wrong.
> 
> If you are running on the
> small-shared-buffers-and-large-kernel-cache theory, then getting the
> kernel cache to behave right is much more important than making the
> PG cache behave right.  If you favor the other theory then
> free-behind in the PG cache is the important thing.  However, I've
> not yet seen any convincing evidence that large PG cache with small
> kernel cache is the way to go.

Nor could it ever be a win unless the cache was populated via
O_DIRECT, actually.  Big PG cache == 2 extra copies of data, once in
the kernel and once in PG.  Doing caching at the kernel level, however
means only one copy of data (for the most part).  Only problem with
this being that it's not always that easy or an option to reconfig a
kernel to have a bigger FS cache.  That said, tripple copying a chunk
of mem is generally faster than even a single disk read.  If
PostgreSQL ever wanted to have a platform agnostic way of doing
efficient caching, it'd likely have to be in the userland and would
require the use of O_DIRECT.

-sc


PS Tripple copy == disk buffer into kernel (data is normally DMA'ed,
   not technically a copy), fs cache into user land, userland into PG
   cache, pg cache into application.  O_DIRECT eliminates one of these
   copies: nevermind the doubling up of data in RAM.

-- 
Sean Chittenden

Sun, 22 Jun 2003 20:20:43 -0700
Sean Chittenden <sean@chittenden.org>
Re: O_DIRECT in freebsd
Sean Chittenden wrote:
> Nor could it ever be a win unless the cache was populated via
> O_DIRECT, actually.  Big PG cache == 2 extra copies of data, once in
> the kernel and once in PG.  Doing caching at the kernel level, however
> means only one copy of data (for the most part).  Only problem with
> this being that it's not always that easy or an option to reconfig a
> kernel to have a bigger FS cache.  That said, tripple copying a chunk
> of mem is generally faster than even a single disk read.  If
> PostgreSQL ever wanted to have a platform agnostic way of doing
> efficient caching, it'd likely have to be in the userland and would
> require the use of O_DIRECT.

Actually, I think of O_DIRECT as platform-dependent.

-- 
  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

Sun, 22 Jun 2003 23:26:36 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: O_DIRECT in freebsd
> >> it doesn't seem totally out of the question.  I'd kinda like to
> >> see some experimental evidence that it's worth doing though.
> >> Anyone care to make a quick-hack prototype and do some
> >> measurements?
> 
> > What would you like to measure?  Overall system performance when a
> > query is using O_DIRECT or are you looking for negative/postitve
> > impact of read() not using the FS cache?  The latter is much
> > easier to do than the former...  recreating a valid load
> > environment that'd let any O_DIRECT benchmark be useful isn't
> > trivial.
> 
> If this stuff were easy, we'd have done it already ;-).

What do you mean?  Bits don't just hit the tree randomly because of a
possible speed improvement hinted at by a man page reference?  :-]

> The first problem is to figure out what makes sense to measure.

Egh, yeah, and this isn't trivial either.... benchmarking around vfs
caching makes it hard to get good results (been down that prim rose
path before with sendfile() happiness).

> Given that the request is for a quick-and-dirty test, I'd be willing
> to cut you some slack on the measurement process.  That is, it's
> okay to pick something easier to measure over something harder to
> measure, as long as you can make a fair argument that what you're
> measuring is of any interest at all...

hrm, well, given the easy part is thumping out the code, how's the
following sound as a test procedure:

1) Write out several files at varying sizes using O_DIRECT (512KB,
   1MB, 5MB, 10MB, 50MB, 100MB, 512MB, 1GB) to avoid having the FS
   cache polluted by the writes.

2) Open two new procs that read the above created files with and
   without O_DIRECT (each test iteration must rewrite the files
   above).

3) Before each read() call (does PostgreSQL use fread(3) or read(2)?),
   use gettimeofday(2) to get high resolution timing of time required
   to perform each system call.

4) Perform each of the tests above 4 times, averaging the last three
   and throwing out the 1st case (though reporting its value may be of
   interest).


I'm not that wild about writing anything threaded unless there's
strong enough interest in a write() to an O_DIRECT'ed fd to see what
happens.  I'm not convinced we'll see anything worth while unless I
setup an example that's doing a ton of write disk io.

As things stand, because O_DIRECT is an execution fast path through
the vfs subsystem, I expect the speed difference to be greater on
faster HDDs with high RPMs than on slower IDE machines at only
5400RPM... thus trivializing any benchmark I'll do on my laptop.  And
actually, if the app can't keep up with the disk, I bet the fs cache
case will be faster.  If the read()'s are able to keep up at the rate
of the HDD, however, this could be a big win in the speed dept, but if
things lag for an instant, the platter will have to make another
rotation before the call comes back to the userland.

Now that I think about it, the optimal case would be to anonymously
mmap() a private buffer that does the read() writes into that way the
HDD could just DMA the data into the mmap()'ed buffer making it a
zero-copy read operation.... though stirring any interest with my
mmap() benchmarks from a while back seems to me have been lost in the
fray.  :)

-sc

-- 
Sean Chittenden

Sun, 22 Jun 2003 21:01:35 -0700
Sean Chittenden <sean@chittenden.org>
Re: O_DIRECT in freebsd
> > Nor could it ever be a win unless the cache was populated via
> > O_DIRECT, actually.  Big PG cache == 2 extra copies of data, once
> > in the kernel and once in PG.  Doing caching at the kernel level,
> > however means only one copy of data (for the most part).  Only
> > problem with this being that it's not always that easy or an
> > option to reconfig a kernel to have a bigger FS cache.  That said,
> > tripple copying a chunk of mem is generally faster than even a
> > single disk read.  If PostgreSQL ever wanted to have a platform
> > agnostic way of doing efficient caching, it'd likely have to be in
> > the userland and would require the use of O_DIRECT.
> 
> Actually, I think of O_DIRECT as platform-dependent.

FreeBSD, IRIX, and AIX, implement it, and ... *smiles with pride*
looks like Linux does too given the number of security vulnerabilities
associated with the call.  :-]

-sc

-- 
Sean Chittenden

Sun, 22 Jun 2003 21:13:37 -0700
Sean Chittenden <sean@chittenden.org>
Re: O_DIRECT in freebsd
Sean Chittenden wrote:
> > > Nor could it ever be a win unless the cache was populated via
> > > O_DIRECT, actually.  Big PG cache == 2 extra copies of data, once
> > > in the kernel and once in PG.  Doing caching at the kernel level,
> > > however means only one copy of data (for the most part).  Only
> > > problem with this being that it's not always that easy or an
> > > option to reconfig a kernel to have a bigger FS cache.  That said,
> > > tripple copying a chunk of mem is generally faster than even a
> > > single disk read.  If PostgreSQL ever wanted to have a platform
> > > agnostic way of doing efficient caching, it'd likely have to be in
> > > the userland and would require the use of O_DIRECT.
> > 
> > Actually, I think of O_DIRECT as platform-dependent.
> 
> FreeBSD, IRIX, and AIX, implement it, and ... *smiles with pride*
> looks like Linux does too given the number of security vulnerabilities
> associated with the call.  :-]

OK, that's 4 of 15 platforms.

-- 
  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

Mon, 23 Jun 2003 00:18:56 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: O_DIRECT in freebsd
On Sun, Jun 22, 2003 at 09:01:35PM -0700, Sean Chittenden wrote:
> As things stand, because O_DIRECT is an execution fast path through
> the vfs subsystem, I expect the speed difference to be greater on
> faster HDDs with high RPMs than on slower IDE machines at only
> 5400RPM... thus trivializing any benchmark I'll do on my laptop.  And
> actually, if the app can't keep up with the disk, I bet the fs cache
> case will be faster.  If the read()'s are able to keep up at the rate
> of the HDD, however, this could be a big win in the speed dept, but if
> things lag for an instant, the platter will have to make another
> rotation before the call comes back to the userland.
 
If it would help, I have a quad xeon-550 with a 4 drive raid5 and 2
drive mirror (all SCSI, 7200RPM I think) available.
-- 
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Sun, 29 Jun 2003 18:11:53 -0500
"Jim C. Nasby" <jim@nasby.net>
pgsql-hackers@postgresql.org