Re: Setting Shared Buffers , Effective Cache, Sort Mem Parameters

On Fri, 23 Apr 2004 10:20:10 -0400, Pallav Kalva <pkalva(at)deg(dot)cc> wrote:
> the database sizes is around 2- 4 gig and
>there are 5 of them. this machine is
> mainly for the databases and nothing is running on them.

Did I understand correctly that you run (or plan to run) five
postmasters? Is there a special reason that you cannot put all your
tables into one database?

> setting shared buffers to 10000 allocates (81Mb) and effective
>cache to 400000 would be around (3gig)
> does this means that if all of the 81mb of the shared memory gets
>allocated it will use rest from the effective
> cache of (3g-81mb) ?

Simply said, if Postgres wants to access a block, it first looks whether
this block is already in shared buffers which should be the case, if the
block is one of the last 10000 blocks accessed. Otherwise the block has
to be read in. If the OS has the block in its cache, reading it is just
a (fast) memory operation, else it involves a (slow) physical disk read.

The number of database pages residing in the OS cache is totally out of
control of Postgres. Effective_cache_size tells the query planner how
many database pages can be *expected* to be present in the OS cache.

>increasing the shared buffers space to 2g

Setting shared_buffers to half your available memory is the worst thing
you can do. You would end up caching exactly the same set of blocks in
the internal buffers and in the OS cache, thus effectively making one of
the caches useless.

Better keep shared_buffers low and let the OS do its job.

Servus
Manfred