pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Kailash Vyas <kailash.vyas@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: postgres optimization
Date: Fri, 29 Jul 2005 10:47:35 -0500
Message-ID: <1122652055.32465.56.camel@state.g2switchworks.com> (raw)
In-Reply-To: <a3cc7917050729062449b122da@mail.gmail.com>
References: <a3cc7917050729062449b122da@mail.gmail.com>

On Fri, 2005-07-29 at 08:24, Kailash Vyas wrote:
> hi
> 
> i am optmizing postgres database and need some help on it.
> I currently have a server with 2 Gb RAM.
> 
> I have setup the /proc/sys/kernel/shmall and /proc/sys/kernel/shmmax
> to 536870912 i.e 512 Mb.
> should i change it to 2 Gb and how will it affect the perfomance and
> will there be any downsides to it or is it better to keep it less.

Generally speaking, you're better off letting your kernel do the
majority of CACHING, and letting postgresql do BUFFERING.  

On a linux box, the kernel will use all spare memory to cache disk
accesses automagically.  So, assuming postgresql uses up a few hundred
megs, after the machine reaches a steady state, the kernel should be
using the 1.5 gig or so left over for caching.

While the latest versions of postgresql have much improved caching
algorithms for its buffers, it still pretty much dumps the buffers when
the last backend looking at them disconnects. I.e. it's not a persistent
caching system like the kernel cache is.

> I then changed the shared buffers in config file according to this
> expression specifies in postgres manual
> 
> max connections=128
> 250kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections or
> infinity
> 
> 
> shared buffer=65277

That's large, but not unreasonable for a machine handling a large
dataset.  I assume you do have a decent sized dataset (a gig or more) to
handle.

> what value should i specify for Effective Cache Size. I came across 
> this article but it is slightly confusing.

Effective cache size just tells the query planner about how much memory
the OS is using to cache your dataset.

Bring the machine up, run lots of queries, and check the cache and
buffers with top, and there's your amount.  divide by 8k to get the
setting for effective cache size.



view thread (3+ messages)

Message-ID: <1122652055.32465.56.camel@state.g2switchworks.com>
Permalink:  ../1122652055.32465.56.camel@state.g2switchworks.com/
Also on:    postgresql.org/message-id/1122652055.32465.56.camel@state.g2switchworks.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: smarlowe@g2switchworks.com, kailash.vyas@gmail.com
  Subject: Re: postgres optimization
  In-Reply-To: <1122652055.32465.56.camel@state.g2switchworks.com>
  References: <a3cc7917050729062449b122da@mail.gmail.com> <1122652055.32465.56.camel@state.g2switchworks.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