Thread: postgres config

postgres config

From

Shreya Bhargava

Date:

12 July 2007, 20:08:02

Hi,

I am new to postgresql and trying to figure out postgres.config file. There are few variables that I need an understanding about.

shared_buffers,
temp_buffers,
work_mem
maintenance_work_mem

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.

Thanks,
Shreya

Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.

Re: postgres config

From

"Kasia Tuszynska"

Date:

12 July 2007, 20:26:06

Hi Shreya,

I too am a novice, and have done a bit of forum surfing, below is some interesting information regarding configuration.

Sincerely,

Kasia

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

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

Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.

Re: postgres config

From

Shreya Bhargava

Date:

12 July 2007, 20:36:31

Thanks for the explanation kasia. I appreciate it!
-Shreya

Kasia Tuszynska <ktuszynska@esri.com> wrote:

Hi Shreya,

I too am a novice, and have done a bit of forum surfing, below is some interesting information regarding configuration.

Sincerely,

Kasia

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

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

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

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.

Re: postgres config

From

"Tomeh, Husam"

Date:

12 July 2007, 20:58:17

The two key parameters that can help speed up your index creation time are shared_buffers and most importantly maintenance_work_mem. Also, maintaining a lean, non-bloated table helps speeding up your index creation time as well since it'll take less time to scan through a table.

For more info on the parameters I think the documentation is pretty clear which can be found at: http://www.postgresql.org/docs/8.2/static/runtime-config-resource.html There's also a pretty nice doc by Josh Berkus that can be found at http://firstdba.googlepages.com/PostgreSQL.conf_80.pdf

Husam

From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Shreya Bhargava
Sent: Thursday, July 12, 2007 1:08 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] postgres config

Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.

**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter.

Thank you.

FADLD Tag
**********************************************************************