Re: Caching by Postgres

От: Frank Wiles
Тема: Re: Caching by Postgres
Дата: ,
Msg-id: 20050823124323.710a9dd3.frank@wiles.org
(см: обсуждение, исходный текст)
Ответ на: Caching by Postgres  (gokulnathbabu manoharan)
Ответы: Re: Caching by Postgres  (Donald Courtney)
Список: 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, 23 Aug 2005 10:10:45 -0700 (PDT)
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
>
> 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 <>
   http://www.wiles.org
 ---------------------------------



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

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