Re: again on index usage

Ross J. Reedstrom wrote:

> Hmm, on Linux this sort of behavior (skip the pg buffers for sequential
> scans) would have interesting load senstive behavior: since Linux uses
> all not-otherwise in use RAM as buffer cache, if you've got a low-load
> system, even very large tables will be cached. Once other processes start
> competing for RAM, your buffers go away. Bruce, what does xBSD do?

For people who run dedicated database services simply not using pg
buffers for sequential scans is probably too simplistic. Assuming one
allocates a very large pg buffer space, as I tend to do. Remember that
shuffling data between a pg buffer and OS cache buffer takes cycles, and
modern machines tend to be starved for memory bandwidth - perhaps
another reason why Oracle requested and got writes that bypass the OS
cache entirely? This bypasses the byte-shuffling.

Of course, Oracle's preferred approach is to have you set up your OS
environment so that Oracle pretty much eats the machine. They tell you
to set SHMAX to 4GB in the installation docs, for instance, then the
installer by default will configure Oracle to use about 1/3 of your
available RAM for its buffer cache. Books on tuning generally tell you
that's far too low.

Anyway, I've been told that Oracle's approach is more along the lines of
"don't cache sequential scans that eat up more than N% of our own cache
space".

Then shorter tables still get fully cached when sequentially scanned,
while humongous tables don't wipe out the cache causing dirty pages to
be flushed to the platter and other concurrent processes to do file I/O
reads because everything but the huge table's disappeared.

Someone in an earlier post mentioned "thrashing" and that's what
dragging a table bigger than cache causes on busy systems.

>
> I like it, since anything that needs to be sensitive to system wide
> information, like the total load on the machine, should be handled by
> the system, not a particular app.
>
> Ross
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
> .
>
>

--
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org