Skip site navigation (1) Skip section navigation (2)

Re: Caching by Postgres

From: Donald Courtney <Donald(dot)Courtney(at)Sun(dot)COM>
To: pgsql-performance(at)postgresql(dot)org
Cc: Frank Wiles <frank(at)wiles(dot)org>,gokulnathbabu manoharan <gokulnathbabu(at)yahoo(dot)com>
Subject: Re: Caching by Postgres
Date: 2005-08-23 18:41:39
Message-ID: 430B6DE3.6040109@sun.com (view raw or flat)
Thread:
Lists: 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(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
>  
>


In response to

Responses

pgsql-performance by date

Next:From: John MendenhallDate: 2005-08-23 19:05:25
Subject: Re: complex query performance assistance request
Previous:From: Josh BerkusDate: 2005-08-23 17:57:09
Subject: Re: Caching by Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group