From mark.lonsdale@wysdm.com Mon Dec 4 17:23:36 2006 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-- From pgsql@j-davis.com Mon Dec 4 17:43:10 2006 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id BB81F9FA192 for ; Mon, 4 Dec 2006 13:43:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 35360-06 for ; Mon, 4 Dec 2006 13:43:01 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from servata.com (ip-216-152-249-241.servata.com [216.152.249.241]) by postgresql.org (Postfix) with ESMTP id 8CECF9FA0EE for ; Mon, 4 Dec 2006 13:43:01 -0400 (AST) Received: from [209.162.219.253] (helo=dogma.v10.wvs) by servata.com with esmtpa (Exim 4.50) id 1GrHqD-0006fC-6I; Mon, 04 Dec 2006 09:42:57 -0800 Subject: Re: Configuration settings for 32GB RAM server From: Jeff Davis To: Mark Lonsdale Cc: pgsql-performance@postgresql.org In-Reply-To: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3> References: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3> Content-Type: text/plain; charset=utf-8 Date: Mon, 04 Dec 2006 09:42:57 -0800 Message-Id: <1165254177.25371.33.camel@dogma.v10.wvs> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-7) Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/51 X-Sequence-Number: 22107 On Mon, 2006-12-04 at 12:10 -0500, Mark Lonsdale wrote: > - 4 physical CPUs (hyperthreaded to 8) >=20 > - 32 GB RAM >=20 > - x86_64 architecture >=20 > - RedHat AS 4 >=20 > - postgres 8.1.5 >=20 > =20 >=20 > Ive been taking a look at the various postgres tuning parameters, and > have come up with the following settings. =20 >=20 > =20 >=20 > shared_buffers =E2=80=93 50,000 - >From what Id read, increasing thi= s > number higher than this wont have any advantages ? >=20 Where did you read that? You should do some tests. Generally 25% of physical memory on a dedicated box is a good point of reference (on 8.1, anyway). I've heard as high as 50% can give you a benefit, but I haven't seen that myself. > fsm_pages =3D 200,000 =E2=80=93 Based this on some statistics about the n= umber > 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 Might as well make it a higher number because you have a lot of RAM anyway. It's better than running out of space in the FSM, because to increase that setting you need to restart the daemon. Increasing this by 1 only uses 6 bytes. That means you could set it to 10 times the number you currently have, and it would still be insignificant. Regards, Jeff Davis From vivek@khera.org Mon Dec 4 18:45:12 2006 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 6669F9FA19A for ; Mon, 4 Dec 2006 14:45:12 -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 56308-02 for ; Mon, 4 Dec 2006 14:45:01 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from yertle.kcilink.com (yertle.kcilink.com [74.92.149.58]) by postgresql.org (Postfix) with ESMTP id E8FD19FA182 for ; Mon, 4 Dec 2006 14:45:01 -0400 (AST) Received: from [192.168.7.103] (host-103.int.kcilink.com [192.168.7.103]) by yertle.kcilink.com (Postfix) with ESMTP id 77CA1B80F for ; Mon, 4 Dec 2006 13:45:00 -0500 (EST) Mime-Version: 1.0 (Apple Message framework v752.2) In-Reply-To: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3> References: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3> Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-14-119622599; protocol="application/pkcs7-signature" Message-Id: <320E1EF2-FC6F-428E-88CB-DB0455CE2134@khera.org> From: Vivek Khera Subject: Re: Configuration settings for 32GB RAM server Date: Mon, 4 Dec 2006 13:44:59 -0500 To: "Pgsql-Performance ((((E-mail))))" X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/58 X-Sequence-Number: 22114 --Apple-Mail-14-119622599 Content-Type: multipart/alternative; boundary=Apple-Mail-13-119622473 --Apple-Mail-13-119622473 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed On Dec 4, 2006, at 12:10 PM, Mark Lonsdale wrote: > - 4 physical CPUs (hyperthreaded to 8) i'd tend to disable hyperthreading on Xeons... > shared_buffers =96 50,000 - >=46rom what Id read, increasing this = =20 > number higher than this wont have any advantages ? > > if you can, increase it until your performance no longer increases. =20 i run with about 70k on a server with 8Gb of RAM. > 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 number is advisory to Pg. it doesn't allocate resources, rather =20= it tells Pg how much disk cache your OS will provide. > 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? you need to evaluate how much memory you need for your queries and =20 then decide if increasing this will help. benchmarking your own use =20 patterns is the only way to do this. > Maintenance_work_mem =3D 1048576 =96 Figured Id allocate 1GB for this. > > I usually do this, too. > 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. i usually never need to go more than the default. > 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? On my big DB server, this sits at 1.2 million pages. You have to =20 check the output of vacuum verbose from time to time to ensure it is =20 not getting out of bounds; if so, you need to either vacuum more =20 often or you need to pack your tables, or increase this parameter. > Do these numbers look reasonable given the machine above? Any =20 > other settings that I should be paying particular consideration too? They're a good starting point. --Apple-Mail-13-119622473 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=WINDOWS-1252
On Dec 4, 2006, at = 12:10 PM, Mark Lonsdale wrote:


- 4 physical CPUs = (hyperthreaded to = 8)


i'd tend to disable = hyperthreading on Xeons...

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 number is advisory to Pg.=A0 = it doesn't allocate resources, rather it tells Pg how much disk cache = your OS will provide.

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?

you need to = evaluate how much memory you need for your queries and then decide if = increasing this will help.=A0 benchmarking your own use patterns is the = only way to do this.


I usually do this, too.

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.


i usually never need to go more = than the default.

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?

On my big DB = server, this sits at 1.2 million pages.=A0 =A0You have to check the = output of vacuum verbose from time to time to ensure it is not getting = out of bounds; if so, you need to either vacuum more often or you need = to pack your tables, or increase this parameter.

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

They're a good = starting point.


= --Apple-Mail-13-119622473-- --Apple-Mail-14-119622599 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGlDCCAz8w ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0 ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/ p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A 9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNNMIICtqADAgECAhA6sDoA4m3lcimf yUtGSQgmMA0GCSqGSIb3DQEBBAUAMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29u c3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNz dWluZyBDQTAeFw0wNjA1MDIxNDEyNDdaFw0wNzA1MDIxNDEyNDdaMIGKMR8wHQYDVQQDExZUaGF3 dGUgRnJlZW1haWwgTWVtYmVyMR4wHAYJKoZIhvcNAQkBFg92aXZla0BraGVyYS5vcmcxIDAeBgkq hkiG9w0BCQEWEWtoZXJhQGtjaWxpbmsuY29tMSUwIwYJKoZIhvcNAQkBFhZ2aXZla0BtYWlsZXJt YWlsZXIuY29tMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAq+zHKYq9vZ4Dq1itNqT/ OeJvWDvQ5JdQlRzwsJlvtJBknXsnOaJNabmjnu2s6XFMcP2srBZQ/WPvhsClajLQOcxnarrfB66N DpMzXTxfYzX6m9TA/fEn64sNn+YnBZL6Dvid9kiAgP9LmqcTOIFdWUya3ZmQS5YTuLB+tkFSL8/h +inDPN6dcsna8TcM1SAk+3upxOR7kyFM9T3vy25w62Nh1zK7Stp0vUZLU6GzzC1VvHGZHKGticD6 o3uHaMr2LCFjptoIcfZL75LO/UaR9o7smgboW4yJyW5g9ZasyYQUXyvkitfyZuVWATb8ZSHOkjWJ 872Pyf5+HOevVUI0aQIDAQABo1cwVTBFBgNVHREEPjA8gQ92aXZla0BraGVyYS5vcmeBEWtoZXJh QGtjaWxpbmsuY29tgRZ2aXZla0BtYWlsZXJtYWlsZXIuY29tMAwGA1UdEwEB/wQCMAAwDQYJKoZI hvcNAQEEBQADgYEAYpaFKDj47pTQIUQi28MgtjKophopv4QJjvspXmy0qxd8t/M/zc7HuBy3i/9a PrXDgKyNBzzlFTasTazAY53ntVpqw9k1NOeHmH6o3j/DBVa49bC6bbWfp9UGOwYChlDR0tngQZyC MDMZEdYv4zpGfBTku5m1jb8Yz/qYqV4FWB4xggMQMIIDDAIBATB2MGIxCzAJBgNVBAYTAlpBMSUw IwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVy c29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIQOrA6AOJt5XIpn8lLRkkIJjAJBgUrDgMCGgUAoIIB bzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNjEyMDQxODQ0NjBa MCMGCSqGSIb3DQEJBDEWBBS/0aRDDsnkdrSPV03+FOm96RBmvjCBhQYJKwYBBAGCNxAEMXgwdjBi MQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoG A1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEDqwOgDibeVyKZ/JS0ZJ CCYwgYcGCyqGSIb3DQEJEAILMXigdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENv bnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElz c3VpbmcgQ0ECEDqwOgDibeVyKZ/JS0ZJCCYwDQYJKoZIhvcNAQEBBQAEggEAbQdMbWBQlJuZ05l0 fiFnm6Iig5VfivhG1GkfbiPfHSYrlseXrcgsNxO0LxuowI6wLsH19eeVuYXGGm6gYkRMTRSzHCXo aawd622bVHli++GqChitLuaK08SJduFT6sPCAhNOH4OOkV29fE+p5fJV+b3cFs2vKvlU9Hx7FoEC K09D8PkQjNaAp/+U4pNzJNkxwwezG24K6b/Sd06YpZSQrKa9ChCF7HybVU1QZW3vb3unO8j6scvg XslE0i3D+9B5etQx93khpW4N+EJT8v/8IdApFRdB9KILhBthqG3hV1ydvr9jz+sr93p2Oz43MTUl 8C342mQwCf2sW29DSsmp1gAAAAAAAA== --Apple-Mail-14-119622599-- From icub3d@gmail.com Mon Dec 4 18:57:14 2006 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9DC749FA1DE for ; Mon, 4 Dec 2006 14:57:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 55146-10 for ; Mon, 4 Dec 2006 14:57:07 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id B36F79FA182 for ; Mon, 4 Dec 2006 14:57:06 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so4594092nfb for ; Mon, 04 Dec 2006 10:57:05 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Yqtpn8EkSZyNKzpH15iOvqm0JXTm8oUmQNLsGRatYeqNTJ9wQwAtquuVV8SfToIF8E0fesKCSNcrpDNVJO8EbXMNYyKMxm5+Ls+7wTVpZ5oGTId/w65hSU+Azoifg+bv3LSW6Dv0nwO0p6Hoqk1zumRNMFsB8UM4aWg+Bn5dHQc= Received: by 10.48.162.15 with SMTP id k15mr13494466nfe.1165258624800; Mon, 04 Dec 2006 10:57:04 -0800 (PST) Received: by 10.49.41.6 with HTTP; Mon, 4 Dec 2006 10:57:04 -0800 (PST) Message-ID: <38242de90612041057j63672f7qafa6c6059e9901dc@mail.gmail.com> Date: Mon, 4 Dec 2006 11:57:04 -0700 From: "Joshua Marsh" To: "Mark Lonsdale" Subject: Re: Configuration settings for 32GB RAM server Cc: pgsql-performance@postgresql.org In-Reply-To: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_52193_21423956.1165258624639" References: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/59 X-Sequence-Number: 22115 ------=_Part_52193_21423956.1165258624639 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 12/4/06, Mark Lonsdale wrote: > > > Maintenance_work_mem =3D 1048576 =96 Figured Id allocate 1GB for this. > Do you know how often and when you will be creating indexes or clustering? We set ours to 2GB because of the performance gains. We've also thought about testing it at 4GB. We can do this because we know during the middle of the night our server load drops to nearly zero. If you know you have windows like that, then I would definitely suggest increasing your maintenance_work_mem. It's halved the time for io intesive tasks like cluster. ------=_Part_52193_21423956.1165258624639 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On 12/4/06, Mark Lonsdale <ma= rk.lonsdale@wysdm.com> wrote:

Maintenance_work_mem =3D 1048576 =96 Figured Id alloc= ate 1GB for this.


Do you kno= w how often and when you will be creating indexes or clustering?  We s= et ours to 2GB because of the performance gains.  We've also thought a= bout testing it at 4GB.  We can do this because we know during the mid= dle of the night our server load drops to nearly zero.  If you know yo= u have windows like that, then I would definitely suggest increasing your m= aintenance_work_mem.  It's halved the time for io intesive tasks like = cluster.
------=_Part_52193_21423956.1165258624639-- From smarlowe@g2switchworks.com Mon Dec 4 19:23:07 2006 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1B6809FA435 for ; Mon, 4 Dec 2006 15:23:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 61578-03 for ; Mon, 4 Dec 2006 15:22:59 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 5793F9FA192 for ; Mon, 4 Dec 2006 15:22:59 -0400 (AST) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Mon, 4 Dec 2006 19:22:56 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 04 Dec 2006 13:23:04 -0600 Subject: Re: Configuration settings for 32GB RAM server From: Scott Marlowe To: Joshua Marsh Cc: Mark Lonsdale , pgsql-performance@postgresql.org In-Reply-To: <38242de90612041057j63672f7qafa6c6059e9901dc@mail.gmail.com> References: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3> <38242de90612041057j63672f7qafa6c6059e9901dc@mail.gmail.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Message-Id: <1165260184.14565.349.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Mon, 04 Dec 2006 13:23:04 -0600 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/61 X-Sequence-Number: 22117 On Mon, 2006-12-04 at 12:57, Joshua Marsh wrote: >=20 > On 12/4/06, Mark Lonsdale wrote: > Maintenance_work_mem =3D 1048576 =E2=80=93 Figured Id allocate 1G= B for > this. > =20 > =20 >=20 > Do you know how often and when you will be creating indexes or > clustering? We set ours to 2GB because of the performance gains.=20 > We've also thought about testing it at 4GB. We can do this because we > know during the middle of the night our server load drops to nearly > zero. If you know you have windows like that, then I would definitely > suggest increasing your maintenance_work_mem. It's halved the time > for io intesive tasks like cluster.=20 >=20 Also, remember that most of those settings (work_mem, maintenance_work_mem) can be changed for an individual session. So, you can leave work_mem at something conservative, like 8 meg, and for a session that is going to run at 2am and iterate over billions of rows, you can throw several gigabytes at it and not worry about that one setting blowing out all the other processes on the machine. From pg@fastcrypt.com Mon Dec 4 23:29:09 2006 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-- From mark.lonsdale@wysdm.com Tue Dec 5 11:14:58 2006 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-- From jim@nasby.net Thu Dec 7 06:03:30 2006 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id BB9C49F9FE1 for ; Thu, 7 Dec 2006 02:03:30 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 78973-05 for ; Thu, 7 Dec 2006 02:03:23 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id B476E9FA162 for ; Thu, 7 Dec 2006 02:03:23 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id EEAF256435; Thu, 7 Dec 2006 06:03:22 +0000 (UTC) Received: (hashcash-sendmail, from uid 1001); Thu, 7 Dec 2006 00:03:20 -0600 Date: Thu, 7 Dec 2006 00:03:20 -0600 From: "Jim C. Nasby" To: Jeff Davis Cc: Mark Lonsdale , pgsql-performance@postgresql.org Subject: Re: Configuration settings for 32GB RAM server Message-ID: <20061207060320.GV44124@nasby.net> References: <673ECBB77DA91F47AD4902A5FD73BCE4D5ED15@mail3> <1165254177.25371.33.camel@dogma.v10.wvs> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1165254177.25371.33.camel@dogma.v10.wvs> X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:061207:pgsql@j-davis.com::c6KT7QOw2KcMQ0uI:00000000000000000000 0000000000000000000000002cre X-Hashcash: 1:20:061207:mark.lonsdale@wysdm.com::DTM86wzM+WbSNfMS:00000000000000 0000000000000000000000001sFb X-Hashcash: 1:20:061207:pgsql-performance@postgresql.org::WYH3SZU/wFr83n2h:00000 0000000000000000000000003Etg X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/163 X-Sequence-Number: 22219 On Mon, Dec 04, 2006 at 09:42:57AM -0800, Jeff Davis wrote: > > 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? > > > > Might as well make it a higher number because you have a lot of RAM > anyway. It's better than running out of space in the FSM, because to > increase that setting you need to restart the daemon. Increasing this by > 1 only uses 6 bytes. That means you could set it to 10 times the number > you currently have, and it would still be insignificant. You can also run vacuumdb -av and look at the last few lines to see what it says you need. Or you can get that info out of contrib/pg_freespacemap. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)