Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 554229FA354 for ; Mon, 4 Dec 2006 19:29:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 10328-02 for ; Mon, 4 Dec 2006 19:28:55 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by postgresql.org (Postfix) with SMTP id 3B8FF9FA18C for ; Mon, 4 Dec 2006 19:28:58 -0400 (AST) Received: (qmail 26917 invoked from network); 4 Dec 2006 23:28:56 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.14?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 4 Dec 2006 23:28:56 -0000 In-Reply-To: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3> References: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: multipart/alternative; boundary=Apple-Mail-5-136660484 Message-Id: Cc: From: Dave Cramer Subject: Re: Configuration settings for 32GB RAM server Date: Mon, 4 Dec 2006 18:28:57 -0500 To: Mark Lonsdale X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/63 X-Sequence-Number: 22119 --Apple-Mail-5-136660484 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed 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 =20 > and also moving it to a server with a much larger hardware =20 > configuration as well. The server will have the following =20 > specification. > > > > - 4 physical CPUs (hyperthreaded to 8) Try both hyperthreaded and not, there's been some evidence that HT =20 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, =20 > and have come up with the following settings. > > > > shared_buffers =96 50,000 - =46rom what Id read, increasing this =20= > number higher than this wont have any advantages ? This is no longer true, 25% of available memory is a good starting =20 place, and go up from there > > > effective_cache_size =3D 524288 - My logic was I thought Id give =20 > the DB 16GB of the 32, and based this number on 25% of that number, =20= > sound okay? > > this should be around 3/4 of available memory or 24G > work_mem =96 32768 - I only have up to 30 connections in parallel, =20= > and more likely less than =BD that number. My sql is relatively =20 > simple, so figured even if there was 5 sorts per query and 30 =20 > queries in parallel, 32768 would use up 4GB of memory.. Does this =20= > number sound too high? > > > > Maintenance_work_mem =3D 1048576 =96 Figured Id allocate 1GB for this. > > > > fsm_relations =3D 2000 - I have about 200 tables plus maybe 4 or 5 =20= > indexes on each, and didn=92t want to have to worry about this number =20= > in future so doubled it. > > > > fsm_pages =3D 200,000 =96 Based this on some statistics about the =20 > number of pages freed from a vacuum on older server. Not sure if =20 > 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 =20 > other settings that I should be paying particular consideration too? autovacuum settings. > > > Thanks > > > Mark > > > > --Apple-Mail-5-136660484 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=WINDOWS-1252

On 4-Dec-06, at = 12:10 PM, Mark Lonsdale wrote:

=A0
=A0
=A0
Hi
=A0
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. =A0=A0=A0The server will have the = following specification.
=A0
- 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
=A0
Ive been taking a look at the various postgres = tuning parameters, and have come up with the following = settings.=A0=A0
shared_buffers =96 50,000 =A0=A0=A0=A0-=A0 =46rom 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 =3D 524288=A0 -=A0 My logic was I = thought Id give the DB 16GB of the 32, and based this number on 25% of = that number, sound okay?
=A0

this = should be around 3/4 of available memory or 24G

work_mem =96 32768 = =A0- I only have up to 30 connections in parallel, and more likely less = than =BD that number.=A0 =A0My 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.. =A0=A0Does this number sound too high?=A0
Maintenance_work_mem =3D 1048576 =96 Figured Id = allocate 1GB for this.
=A0
fsm_relations =3D 2000=A0 - I have about 200 = tables plus maybe 4 or 5 indexes on each, and didn=92t want to have to = worry about this number in future so doubled it.=A0
fsm_pages =3D 200,000 =96 Based this on some = statistics about the number of pages freed from a vacuum on older = server. =A0=A0Not 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? = =A0=A0Any other settings that I should be paying particular = consideration = too?

autovacuum = settings.

Thanks

Mark=A0

<= /HTML>= --Apple-Mail-5-136660484--