Date: Wed, 24 Aug 2005 09:21:12 -0400
From: Donald Courtney <Donald.Courtney@Sun.COM>
Subject: Re: Caching by Postgres
In-reply-to: <deg68k$16gh$1@news.hub.org>
To: William Yu <wyu@talisys.com>
Cc: pgsql-performance@postgresql.org
Message-id: <430C7448.7030103@sun.com>
Organization: Sun Microsystems
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; en-US; rv:1.7) Gecko/20041221
References: <20050823171045.12112.qmail@web51309.mail.yahoo.com>
	<20050823124323.710a9dd3.frank@wiles.org> <430B6DE3.6040109@sun.com>
	<deg68k$16gh$1@news.hub.org>


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@postgresql.org so that your
>        message can get through to the mailing list cleanly