Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 2BB8F9FA354 for ; Tue, 5 Dec 2006 07:14:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 21116-10 for ; Tue, 5 Dec 2006 07:14:45 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail3.netvigour.com (mail.netvigour.com [208.229.144.195]) by postgresql.org (Postfix) with ESMTP id CFECA9FA344 for ; Tue, 5 Dec 2006 07:14:48 -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_01C7185E.AB16DB4B" Subject: Re: Configuration settings for 32GB RAM server Date: Tue, 5 Dec 2006 06:14:45 -0500 Message-ID: <673ECBB77DA91F47AD4902A5FD73BCE4D5EF1C@mail3> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] Configuration settings for 32GB RAM server Thread-Index: AccX/BTA3qahCGfCR6Co9pLlibshpQAYczhA From: "Mark Lonsdale" To: "Dave Cramer" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/72 X-Sequence-Number: 22128 This is a multi-part message in MIME format. ------_=_NextPart_001_01C7185E.AB16DB4B Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =20 Thanks to all for the feedback on this issue.. After reviewing your = comments, Im thinking of changing to the following values =20 shared_buffers =3D 786432 - If Ive done my math right, then this is = 6GB which is 25% of 24GB ( I want to preserve the other 8GB for OS and = App ) =20 effective_cache_size =3D 2359296 - Equates to 18GB, which is 75% of = 24GB.. Using the feedback from Dave Cramer =20 work_mem =3D 32768 =20 maintenance_work_mem =3D 1048576 i.e. 1GB =20 max_fsm_relations =3D 10,000 - Given the small amount of memory this = will use, I figure go large and not worry about it in the future. =20 max_fsm_pages =3D 10,000,000 - Again, increasing this significantly to = cover my existing vacuuming numbers, and given I have a lot of memory, = it seems like its not going to hurt me at all. =20 =20 Sound good? =20 ________________________________ From: Dave Cramer [mailto:pg@fastcrypt.com]=20 Sent: 04 December 2006 23:29 To: Mark Lonsdale Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Configuration settings for 32GB RAM server =20 =20 On 4-Dec-06, at 12:10 PM, Mark Lonsdale wrote: =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) 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 =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 ? This is no longer true, 25% of available memory is a good starting = place, and go up from there =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 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 =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? this is dependent on your application =20 Do these numbers look reasonable given the machine above? Any other = settings that I should be paying particular consideration too? =20 autovacuum settings. =20 =20 Thanks Mark =20 =20 ------_=_NextPart_001_01C7185E.AB16DB4B Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

 

Thanks to all for the feedback on = this issue.. =A0After reviewing your comments, Im thinking of changing to the following values

 

shared_buffers =3D 786432=A0=A0 - = If Ive done my math right, then this is 6GB which is 25% of 24GB ( I want to preserve = the other 8GB for OS and App )

 

effective_cache_size =3D 2359296 = -=A0=A0 Equates to 18GB, which is 75% of 24GB.. Using the feedback from Dave = Cramer

 

work_mem =3D = 32768

 

maintenance_work_mem =3D 1048576 = i.e. 1GB

 

max_fsm_relations =3D 10,000 = – Given the small amount of memory this will use, I figure go large and not worry = about it in the future.

 

max_fsm_pages =3D 10,000,000 = – Again, increasing this significantly to cover my existing vacuuming numbers, = and given I have a lot of memory, it seems like its not going to hurt me at = all.

 

 

Sound = good?

 

From: Dave = Cramer [mailto:pg@fastcrypt.com]
Sent: 04 December 2006 = 23:29
To: Mark Lonsdale
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] 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

 

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

 

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?

this is dependent on your application

 

autovacuum settings.

 



 



 

------_=_NextPart_001_01C7185E.AB16DB4B--