Re: Caching by Postgres

Great discussion and illuminating for those of us who are still
learning the subtleties of postGres.

William

To be clear -
I built postgreSQL 8.1 64K bit on solaris 10 a few months ago
and side by side with the 32 bit postgreSQL build saw no improvement.
In fact the 64 bit result was slightly lower.

I used the *same 64 bit S10 OS* for both versions. I think your
experience makes sense since your change was from 32 to 64 bit Linux.
From my experiment I am surmising that there will not be any
file/os/buffer-cache
scale up effect on the same OS with postgreSQL 64.

I was testing on a 4 core system in both cases.

William Yu wrote:

> Donald Courtney wrote:
>
>> in that even if you ran postgreSQL on a 64 bit address space
>> with larger number of CPUs you won't see much of a scale up
>> and possibly even a drop. I am not alone in having the *expectation*
>
>
> What's your basis for believing this is the case? Why would
> PostgreSQL's dependence on the OS's caching/filesystem limit
> scalability? I know when I went from 32bit to 64bit Linux, I got
> *HUGE* increases in performance using the same amount of memory. And
> when I went from 2x1P to 2xDC, my average cpu usage % dropped almost
> in half.
>
>> that a database should have some cache size parameter and
>> the option to skip the file system. If I use oracle, sybase, mysql
>> and maxdb they all have the ability to size a data cache and move
>> to 64 bits.
>
>
> Josh Berkus has already mentioned this as conventional wisdom as
> written by Oracle. This may also be legacy wisdom. Oracle/Sybase/etc
> has been around for a long time; it was probably a clear performance
> win way back when. Nowadays with how far open-source OS's have
> advanced, I'd take it with a grain of salt and do my own performance
> analysis. I suspect the big vendors wouldn't change their stance even
> if they knew it was no longer true due to the support hassles.
>
> My personal experience with PostgreSQL. Dropping shared buffers from
> 2GB to 750MB improved performance on my OLTP DB a good 25%. Going down
> from 750MB to 150MB was another +10%.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly