Re: Caching by Postgres - Mailing list pgsql-performance

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
>
>