Re: Working on huge RAM based datasets

> What is it about the buffer cache that makes it so unhappy being able
to
> hold everything? I don't want to be seen as a cache hit fascist, but
isn't
> it just better if the data is just *there*, available in the
postmaster's
> address space ready for each backend process to access it, rather than
> expecting the Linux cache mechanism, optimised as it may be, to have
to do
> the caching?

The disk cache on most operating systems is optimized. Plus, keeping
shared buffers low gives you more room to bump up the sort memory, which
will make your big queries run faster.

Merlin

>The disk cache on most operating systems is optimized. Plus, keeping
shared buffers low gives you more room to bump up the sort memory, which
will make your big queries run faster.

Thanks merlin,

Whether the OS caches the data or PG does, you still want it cached. If your
sorting backends gobble up the pages that otherwise would be filled with the
database buffers, then your postmaster will crawl, as it'll *really* have to
wait for stuff from disk. In my scenario, you'd spec the machine so that
there would be plenty of memory for *everything*.

On your OS optimisation point, OS caches are, of course, optimised. But
people have told me that PG's caching strategy is simply less well
optimised, and *that* is the reason for keeping the shared buffer cache down
in my scenario. That's a shame in a way, but I understand why it is the way
it is - other things have been addressed which speed up operations in
different ways. My 'all in RAM' scenario is very rare at the moment, so why
waste valuable development resources on developing optimised RAM based data
structures to hold the data for quicker query execution when hardly anyone
will see the benefit?

However - it won't be so rare for too much longer... If I gave you a half a
terabyte of RAM and a 4 processor 64 bit machine, I'm sure you could imagine
how much quicker databases could run if they were optimised for this sort of
platform.

Anyway, I'm looking forward to experimenting with stuff the way it works at
the moment.

Many thanks,
Andy

On 7/9/2004 10:16 AM, Merlin Moncure wrote:

>> What is it about the buffer cache that makes it so unhappy being able
> to
>> hold everything? I don't want to be seen as a cache hit fascist, but
> isn't
>> it just better if the data is just *there*, available in the
> postmaster's
>> address space ready for each backend process to access it, rather than
>> expecting the Linux cache mechanism, optimised as it may be, to have
> to do
>> the caching?
>
> The disk cache on most operating systems is optimized. Plus, keeping
> shared buffers low gives you more room to bump up the sort memory, which
> will make your big queries run faster.

Plus, the situation will change dramatically with 7.5 where the disk
cache will have less information than the PG shared buffers, which will
become sequential scan resistant and will know that a block was pulled
in on behalf of vacuum and not because the regular database access
pattern required it.

Jan

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

Martha Stewart called it a Good Thing when JanWieck(at)Yahoo(dot)com (Jan Wieck) wrote:
> On 7/9/2004 10:16 AM, Merlin Moncure wrote:
>>> What is it about the buffer cache that makes it so unhappy being
>>> able to hold everything? I don't want to be seen as a cache hit
>>> fascist, but isn't it just better if the data is just *there*,
>>> available in the postmaster's address space ready for each backend
>>> process to access it, rather than expecting the Linux cache
>>> mechanism, optimised as it may be, to have to do the caching?

>> The disk cache on most operating systems is optimized. Plus,
>> keeping shared buffers low gives you more room to bump up the sort
>> memory, which will make your big queries run faster.

> Plus, the situation will change dramatically with 7.5 where the disk
> cache will have less information than the PG shared buffers, which
> will become sequential scan resistant and will know that a block was
> pulled in on behalf of vacuum and not because the regular database
> access pattern required it.

It'll be very curious how this changes things.

I _think_ it means that shared buffer usage becomes more efficient
both for small and large buffers, since vacuums and seq scans
shouldn't "eviscerate" the shared buffers the way they can in earlier
versions.

What would be most interesting to see is whether this makes it wise to
increase shared buffer size. It may be more effective to bump down
the cache a little, and bump up sort memory; hard to tell.
--
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/spreadsheets.html
"But life wasn't yes-no, on-off. Life was shades of gray, and
rainbows not in the order of the spectrum."
-- L. E. Modesitt, Jr., _Adiamante_

> What would be most interesting to see is whether this makes it wise to
> increase shared buffer size. It may be more effective to bump down
> the cache a little, and bump up sort memory; hard to tell.

How do we go about scheduling tests with the OSDL folks? If they could
do 10 runs with buffers between 1k and 500k it would help us get a broad
view of the situation.

Rond, Chris,

> > What would be most interesting to see is whether this makes it wise to
> > increase shared buffer size. It may be more effective to bump down
> > the cache a little, and bump up sort memory; hard to tell.
>
> How do we go about scheduling tests with the OSDL folks? If they could
> do 10 runs with buffers between 1k and 500k it would help us get a broad
> view of the situation.

Yes. We'll need to. However, I'd like to wait until we're officially in
Beta. I'll be seeing the OSDL folks in person (PostgreSQL+OSDL BOF at Linux
World Expo!!) in a couple of weeks.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On 7/12/2004 12:38 PM, Josh Berkus wrote:

> Rond, Chris,
>
>> > What would be most interesting to see is whether this makes it wise to
>> > increase shared buffer size. It may be more effective to bump down
>> > the cache a little, and bump up sort memory; hard to tell.
>>
>> How do we go about scheduling tests with the OSDL folks? If they could
>> do 10 runs with buffers between 1k and 500k it would help us get a broad
>> view of the situation.
>
> Yes. We'll need to. However, I'd like to wait until we're officially in
> Beta. I'll be seeing the OSDL folks in person (PostgreSQL+OSDL BOF at Linux
> World Expo!!) in a couple of weeks.
>

Don't forget to add that ARC needs some time actually to let the
algorithm adjust the queue sizes and populate the cache according to the
access pattern. You can't start a virgin postmaster and then slam on the
accellerator of your test application by launching 500 concurrent
clients out of the blue and expect that it starts off airborne.

Jan

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