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(at)familyhealth(dot)com(dot)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(at)nasby(dot)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?"