Re: Caching by Postgres

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(at)yahoo(dot)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(at)wiles(dot)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
>
>