Re: Configuration settings for 32GB RAM server

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