Skip site navigation (1) Skip section navigation (2)

Re: postgres config

From: Shreya Bhargava <shreya_bhargav(at)yahoo(dot)com>
To: Kasia Tuszynska <ktuszynska(at)esri(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: postgres config
Date: 2007-07-12 20:36:18
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Thanks for the explanation kasia. I appreciate it!

Kasia Tuszynska <ktuszynska(at)esri(dot)com> wrote:        v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);}           Hi Shreya,
  I too am a novice, and have done a bit of forum surfing, below is some interesting information regarding configuration.
1.) shared_buffers I see lot of reference to making

this the size of available ram (for the DB).  However,

I also read to make it the size of pgdata directory.


You generally want shared_buffers to be no more than 10% of available

ram. Postgres expects the OS to do it's own caching. 


2.) effective_cache_size - from what I read this is

the 'total' allowed memory for postgresql to use

correct? So, if I am willing to allow 1GB of memory

should I make this 1GB?


This is the effective amount of caching between the actual postgres

buffers, and the OS buffers. If you are dedicating this machine to

postgres, I would set it to something like 3.5G. If it is a mixed

machine, then you have to think about it.


This does not change how postgres uses RAM, it changes how postgres

estimates whether an Index scan will be cheaper than a Sequential scan,

based on the likelihood that the data you want will already be cached in Ram.

3.) max_connections, been trying to figure 'how' to

determine this #.  I've read this is buffer_size+500k

per a connection. ie.  128mb(buffer) + 500kb = 128.5mb per connection?


Max connections is just how many concurrent connections you want to

allow. If you can get away with lower, do so.  Mostly this is to prevent connections * work_mem to get bigger than your real working memory and causing you to swap.


I was curious about 'sort_mem' I can't find reference

of it in the 8.0.3 documentation, has it been removed?



sort_mem changed to work_mem in 8.0, same thing with vacuum_mem ->



work_mem and max_stack_depth set to 4096

maintenance_work_mem set to 64mb



Depends how much space you want to give per connection. 4M is pretty

small for a machine with 4G of RAM, but if your DB is only 85M it might

be plenty. work_mem is how much memory a sort/hash/etc will use before it spills to disk. So look at your queries. If you tend to sort most of your 85M db in a single query, you might want to make it a little bit more. But if all of your queries are very selective, 4M could be plenty.


I would make maintenance_work_mem more like 512M. It is only used for

CREATE INDEX, VACUUM, etc. Things that are not generally done by more

than one process at a time. And it's nice for them to have plenty of

room to run fast.
 I am new to postgresql and trying to figure out postgres.config file. There are few variables that I need an understanding about. 
 Can anyone please explain when and how these are used? I am trying to understand the importance of these 4 while creating an index on a table. Any help will be appreciated.
  Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.

Yahoo! oneSearch: Finally,  mobile search that gives answers, not web links. 

In response to

pgsql-novice by date

Next:From: Tomeh, HusamDate: 2007-07-12 20:48:37
Subject: Re: postgres config
Previous:From: Kasia TuszynskaDate: 2007-07-12 20:16:21
Subject: Re: postgres config

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group