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
- 32 GB RAM
- 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
Mark
