Re: Caching by Postgres

От: Bruno Wolff III
Тема: Re: Caching by Postgres
Дата: ,
Msg-id: 20050823174108.GD19586@wolff.to
(см: обсуждение, исходный текст)
Ответ на: Caching by Postgres  (gokulnathbabu manoharan)
Список: pgsql-performance

Скрыть дерево обсуждения

Caching by Postgres  (gokulnathbabu manoharan, )
 Re: Caching by Postgres  (John A Meinel, )
  Re: Caching by Postgres  (Josh Berkus, )
 Re: Caching by Postgres  (Bruno Wolff III, )
 Re: Caching by Postgres  (Frank Wiles, )
  Re: Caching by Postgres  (Donald Courtney, )
   Re: Caching by Postgres  (Tom Lane, )
   Re: Caching by Postgres  (Josh Berkus, )
    Re: Caching by Postgres  (Michael Stone, )
   Re: Caching by Postgres  (, )
   Re: Caching by Postgres  (William Yu, )
    Re: Caching by Postgres  (PFC, )
     Re: Caching by Postgres  (Josh Berkus, )
      Re: Caching by Postgres  (PFC, )
     Re: Caching by Postgres  (Gavin Sherry, )
      Re: Caching by Postgres  (Tom Lane, )
       Re: Caching by Postgres  (Gavin Sherry, )
    Re: Caching by Postgres  (Donald Courtney, )
     Re: Caching by Postgres  (Stephen Frost, )
     Re: Caching by Postgres  (, )
      Re: Caching by Postgres  (Alan Stange, )
       Re: Caching by Postgres  (, )
        Re: Caching by Postgres  (Alan Stange, )
         Re: Caching by Postgres  (, )
        Re: Caching by Postgres  (Michael Stone, )
         Re: Caching by Postgres  (, )
       Re: Caching by Postgres  (PFC, )
      Re: Caching by Postgres  (Thomas Ganss, )
     Re: Caching by Postgres  (William Yu, )
 Re: Caching by Postgres  (Chris Browne, )
 Re: Caching by Postgres  (Chris Browne, )

On Tue, Aug 23, 2005 at 10:10:45 -0700,
  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

The main policy is to let the OS do most of the caching.

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

Using extermely large values for shared buffers is known to be a performance
loss for Postgres. Some improvements were made for 8.0 and more for 8.1.

The OS will cache frequently used data from files for you. So if you are using
that table a lot and the rows aren't too wide, it should mostly be cached
for you by the OS.

> 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 used for sorts and some other things.

> 3. effective_cache_size - The parameter used by the
> query planner and has nothing to do with the actual
> caching.

You are supposed to use this to give the planner an idea about how much
space the OS will using for caching on behalf of Posgres.

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

You probably want to read the following:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html


В списке pgsql-performance по дате сообщения:

От: Steve Poe
Дата:
Сообщение: Re: Read/Write block sizes
От: "Jeffrey W. Baker"
Дата:
Сообщение: Re: Read/Write block sizes (Was: Caching by Postgres)