Re: Postgres 7.3.1 poor insert/update/search performance

On Fri, 30 Jan 2003, Neil Conway wrote:

> If we had a good buffer replacement algorithm (which we currently do
> not), ISTM that hot pages retained in PostgreSQL's buffer cache would
> never get loaded from the OS's IO cache, thus causing those pages to
> eventually be evicted from the OS's cache. So the "cache the data twice"
> problem doesn't apply in all circumstances.

No, but it does apply to every block at some point, since during the
initial load it's present in both caches, and it has to be flushed from
the OS's cache at some point.

> > Some systems, like Solaris, allow you to turn off the
> > disk cache, so the problem may not be one you face.)
>
> I think it would be interesting to investigate disabling the OS' cache
> for all relation I/O (i.e. heap files, index files). That way we could
> both improve performance (by moving all the caching into PostgreSQL's
> domain, where there is more room for optimization)...

I'm not so sure that there is that all much more room for optimization.
But take a look at what Solaris and FFS do now, and consider how much
work it would be to rewrite it, and then see if you even want to do that
before adding stuff to improve performance.

> , as well as make configuration simpler: in an ideal world, it would
> remove the need to consider the OS' caching when configuring the
> amount of shared memory to allocate to PostgreSQL.

We could do that much more simply by using mmap.

> Can this be done using O_DIRECT?

It can, but you're doing to lose some of the advantages that you'd get
from using raw devices instead. In particular, you have no way to know
the physical location of blocks on the disk, because those locations are
often different from the location in the file.

> If so, is it portable?

O_DIRECT is not all that portable, I don't think. Certainly not as
portable as mmap.

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