|
Thanks to all for the feedback on this
issue.. After reviewing your comments, Im thinking of changing to the
following values shared_buffers = 786432 - If Ive done my
math right, then this is 6GB which is 25% of 24GB ( I want to preserve the
other 8GB for OS and App ) effective_cache_size = 2359296 - Equates
to 18GB, which is 75% of 24GB.. Using the feedback from Dave Cramer work_mem = 32768 maintenance_work_mem = 1048576 i.e. 1GB max_fsm_relations = 10,000 – Given the
small amount of memory this will use, I figure go large and not worry about it
in the future. max_fsm_pages = 10,000,000 – Again,
increasing this significantly to cover my existing vacuuming numbers, and given
I have a lot of memory, it seems like its not going to hurt me at all. Sound good? From: Dave Cramer
[mailto:pg@xxxxxxxxxxxxx] On 4-Dec-06, at 12:10 PM, Mark Lonsdale wrote:
Hi We are migrating our
Postgres 7.3.4 application to postgres 8.1.5 and also moving it to a server
with a much larger hardware configuration as well. The server
will have the following specification. - 4 physical CPUs
(hyperthreaded to 8) Try both hyperthreaded and not, there's been some evidence that HT
helps us now - x86_64 architecture - RedHat AS 4 - postgres 8.1.5 Ive been taking a look at
the various postgres tuning parameters, and have come up with the following
settings. shared_buffers – 50,000
- From what Id read, increasing this number
higher than this wont have any advantages ? This is no longer true, 25% of available memory is a good starting
place, and go up from there effective_cache_size =
524288 - My logic was I thought Id give the DB 16GB of the 32, and
based this number on 25% of that number, sound okay? this should be around 3/4 of available memory or 24G work_mem – 32768 - I only have up
to 30 connections in parallel, and more likely less than ½ that number.
My sql is relatively simple, so figured even if there was 5 sorts per
query and 30 queries in parallel, 32768 would use up 4GB of memory..
Does this number sound too high? Maintenance_work_mem =
1048576 – Figured Id allocate 1GB for this. fsm_relations =
2000 - I have about 200 tables plus maybe 4 or 5 indexes on each, and
didn’t want to have to worry about this number in future so doubled it. fsm_pages = 200,000 –
Based this on some statistics about the number of pages freed from a vacuum on
older server. Not sure if its fair to calculate this based on
vacuum stats of 7.3.4 server? this is dependent on your application Do these numbers look
reasonable given the machine above? Any other settings that I
should be paying particular consideration too? autovacuum settings.
Thanks
|
