From: John A Meinel <john@arbash-meinel.com>
To: gokulnathbabu manoharan <gokulnathbabu@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Caching by Postgres
Date: Tue, 23 Aug 2005 12:25:59 -0500
Message-ID: <430B5C27.9030007@arbash-meinel.com> (raw)
In-Reply-To: <20050823171045.12112.qmail@web51309.mail.yahoo.com>
References: <20050823171045.12112.qmail@web51309.mail.yahoo.com>
gokulnathbabu manoharan 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
In general, you don't. The OS handles caching based on file usage.
So if you are using the files, the OS should cache them. Just like it
does with any other program.
>
> 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?
There is a portion of this which is used for caching. But I believe
before 8.1 there was code that went linearly through all of the
shared_buffers and checked for dirty/clean pages. So there was a
tradeoff that the bigger you make it, the longer that search goes. So
you got diminishing returns, generally around 10k shared buffers.
I think it is better in 8.1, but if the OS is going to cache it anyway
(since it does), then having a Postgres cache is just wasting memory,
and not letting cache as much.
So I'm guessing that with 8.1 there would be 2 sweet spots. Low
shared_buffers (<= 10k), and really high shared buffers (like all of
available ram).
But because postgres has been tuned for the former I would stick with it
(I don't think shared_buffers can go >2GB, but that might just be
work_mem/maintenance_work_mem).
>
> 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.
>
> 3. effective_cache_size - The parameter used by the
> query planner and has nothing to do with the actual
> caching.
This is important from a planner issue. Because the planner can then
expect that the OS is doing its job and caching the tables, so index
scans are cheaper than they would be otherwise.
John
=:->
>
> So kindly help me in pointing me to the correct
> parameter to set.
>
> It will be great if you can point me to the docs that
> explains the implementation of caching in Postgresql
> which will help me in understanding things much
> clearly.
>
> Thanks in advance.
> Gokul.
>
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, gokulnathbabu@yahoo.com
Subject: Re: Caching by Postgres
In-Reply-To: <430B5C27.9030007@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox