Date: Tue, 23 Aug 2005 14:41:39 -0400
From: Donald Courtney <Donald.Courtney@Sun.COM>
Subject: Re: Caching by Postgres
In-reply-to: <20050823124323.710a9dd3.frank@wiles.org>
To: pgsql-performance@postgresql.org
Cc: Frank Wiles <frank@wiles.org>,
	gokulnathbabu manoharan <gokulnathbabu@yahoo.com>
Message-id: <430B6DE3.6040109@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>


I mean well with this comment - 
This whole issue of data caching is  a troubling issue with postreSQL
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*
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.

Is this a crazy idea - that a project be started to get this adopted?  
Is it
too big and structural to contemplate? 

 From one who likes postgreSQL
dc

Frank Wiles wrote:

>On Tue, 23 Aug 2005 10:10:45 -0700 (PDT)
>gokulnathbabu manoharan <gokulnathbabu@yahoo.com> wrote:
>
>  
>
>>Hi all,
>>
>>I like to know the caching policies of Postgresql. 
>>What parameter in the postgresql.conf affects the
>>cache size used by the Postgresql?  As far as I have
>>searched my knowledge of the parameters are
>>
>>1. shared_buffers - Sets the limit on the amount of
>>shared memory used.  If I take this is as the cache
>>size then my performance should increase with the
>>increase in the size of shared_buffers.  But it seems
>>it is not the case and my performance actually
>>decreases with the increase in the shared_buffers.  I
>>have a RAM size of 32 GB.  The table which I use more
>>frequently has around 68 million rows.  Can I cache
>>this entire table in RAM?
>>    
>>
>
>  increasing shared_buffers to a point helps, but after
>  a certain threshold it can actually degree performance. 
> 
>  
>
>>2. work_mem - It is the amount of memory used by an
>>operation.  My guess is once the operation is complete
>>this is freed and hence has nothing to do with the
>>caching.
>>    
>>
>
>  This is the amount of memory used for things like sorts and
>  order bys on a per backend process basis. 
> 
>  
>
>>3. effective_cache_size - The parameter used by the
>>query planner and has nothing to do with the actual
>>caching.
>>    
>>
>
>  The instructs the query planner on how large the operating
>  system's disk cache is.  There isn't a built in cache, PostgreSQL
>  relies on the operating system to cache the on disk information
>  based on how often it is used.  In most cases this is probably
>  more accurate anyway. 
>
>  I wrote an article on PostgreSQL performance tuning that has
>  links to several other related sites, you can find it here: 
>
>  http://www.revsys.com/writings/postgresql-performance.html
>
> ---------------------------------
>   Frank Wiles <frank@wiles.org>
>   http://www.wiles.org
> ---------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>  
>