From: Dave Cramer <pg@fastcrypt.com>
To: Mark Lonsdale <mark.lonsdale@wysdm.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Configuration settings for 32GB RAM server
Date: Mon, 4 Dec 2006 18:28:57 -0500
Message-ID: <A67425AC-8FF8-438B-9678-862D3854FBD3@fastcrypt.com> (raw)
In-Reply-To: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3>
References: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3>
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
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pg@fastcrypt.com, mark.lonsdale@wysdm.com
Subject: Re: Configuration settings for 32GB RAM server
In-Reply-To: <A67425AC-8FF8-438B-9678-862D3854FBD3@fastcrypt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox