Re: postgres config - Mailing list pgsql-novice
From | Shreya Bhargava |
---|---|
Subject | Re: postgres config |
Date | |
Msg-id | 70374.13326.qm@web53403.mail.re2.yahoo.com Whole thread Raw |
In response to | Re: postgres config ("Kasia Tuszynska" <ktuszynska@esri.com>) |
List | pgsql-novice |
Thanks for the explanation kasia. I appreciate it!
-Shreya
Kasia Tuszynska <ktuszynska@esri.com> wrote:
Yahoo! oneSearch: Finally, mobile search that gives answers, not web links.
-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,Kasia1.) 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.
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,
ShreyaLooking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.
Yahoo! oneSearch: Finally, mobile search that gives answers, not web links.
pgsql-novice by date: