Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B89D09FA373 for ; Mon, 4 Dec 2006 13:23:36 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 14808-03 for ; Mon, 4 Dec 2006 13:23:24 -0400 (AST) X-Greylist: delayed 00:12:25.844848 by SQLgrey-1.7.4 Received: from mail3.netvigour.com (mail.netvigour.com [208.229.144.195]) by postgresql.org (Postfix) with ESMTP id 9E13D9F9F7B for ; Mon, 4 Dec 2006 13:23:24 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C717C7.454AD3F1" Subject: Configuration settings for 32GB RAM server Date: Mon, 4 Dec 2006 12:10:59 -0500 Message-ID: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Configuration settings for 32GB RAM server Thread-Index: AccXxyt2cFuavOoUT9q+xTywaGaflQ== From: "Mark Lonsdale" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=BAYES_50, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200612/49 X-Sequence-Number: 22105 This is a multi-part message in MIME format. ------_=_NextPart_001_01C717C7.454AD3F1 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =20 =20 =20 Hi =20 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. =20 - 4 physical CPUs (hyperthreaded to 8) - 32 GB RAM - x86_64 architecture - RedHat AS 4 - postgres 8.1.5 =20 Ive been taking a look at the various postgres tuning parameters, and = have come up with the following settings. =20 =20 shared_buffers - 50,000 - From what Id read, increasing this number = higher than this wont have any advantages ? =20 effective_cache_size =3D 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? =20 work_mem - 32768 - I only have up to 30 connections in parallel, and = more likely less than =BD 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? =20 Maintenance_work_mem =3D 1048576 - Figured Id allocate 1GB for this. =20 fsm_relations =3D 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. =20 fsm_pages =3D 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? =20 Do these numbers look reasonable given the machine above? Any other = settings that I should be paying particular consideration too? =20 Thanks=20 Mark =20 ------_=_NextPart_001_01C717C7.454AD3F1 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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)

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

effective_cache_size =3D 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?

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 =3D 1048576 – Figured Id = allocate 1GB for this.

fsm_relations =3D 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 =3D 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?

Do these numbers look reasonable given the machine = above? Any other settings that I should be paying particular consideration = too?

Thanks

Mark

------_=_NextPart_001_01C717C7.454AD3F1--