Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 4F61B633A66 for ; Wed, 3 Feb 2010 11:13:49 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 30413-05 for ; Wed, 3 Feb 2010 15:13:29 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.24]) by mail.postgresql.org (Postfix) with ESMTP id 966CE63290A for ; Wed, 3 Feb 2010 11:12:58 -0400 (AST) Received: by qw-out-2122.google.com with SMTP id 5so254957qwi.61 for ; Wed, 03 Feb 2010 07:12:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:content-type; bh=kn95YT4vuhTqgLykvO4twwgPeKbeIyeKw9POLqyI2M4=; b=DjrpAaLc2TstuobAjnsQeH86l3ZVrKA/Rlgjpqor9noRfSKV+Acn0OKfVtIEXbDsC3 TZwILbdcgOG0YixrUje/aL+eRTuNCVVvaVQaBZglZe8xUdcxEbIk/cwyVTtNmuUNzoD6 9gipFdG3s6zwZ8zk3TlEQyMwbTfZq0o/angJ8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :content-type; b=EZYiKJWt3w/exaAteWuSiPxuFbIWBCnC4/L4/pmXQOGJ1pb7RYplCLnmczKUqethpk qzb67u8QNV9dDjLplz+g3WRSnoPQYLn//zyOe31x2HGosAcQ9go1XGqcACtl1zLpgaDS /xL6enIP9+dYHJLtkl7803ukOK6CO9m4s/4vo= MIME-Version: 1.0 Received: by 10.229.33.15 with SMTP id f15mr4050289qcd.59.1265209976734; Wed, 03 Feb 2010 07:12:56 -0800 (PST) In-Reply-To: <84b68b3d1002030710x76c31adft58c56624409011fd@mail.gmail.com> References: <84b68b3d1002030710x76c31adft58c56624409011fd@mail.gmail.com> From: Amitabh Kant Date: Wed, 3 Feb 2010 20:42:24 +0530 Message-ID: <84b68b3d1002030712j6fab33b8xe1d0f1071d207bcb@mail.gmail.com> Subject: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016364ed9d43ac80b047eb3a7e0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.391 tagged_above=-10 required=5 tests=AWL=-1.208, BAYES_40=-0.185, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/57 X-Sequence-Number: 37478 --0016364ed9d43ac80b047eb3a7e0 Content-Type: text/plain; charset=ISO-8859-1 Forgot to add that I am using Postgres 8.4.2 from the default ports of FreeBSD. With regards Amitabh Kant On Wed, Feb 3, 2010 at 8:40 PM, Amitabh Kant wrote: > Hello > > I have a server dedicated for Postgres with the following specs: > > RAM 16GB, 146GB SAS (15K) x 4 - RAID 10 with BBU, Dual Xeon E5345 @ > 2.33GHz > OS: FreeBSD 8.0 > > It runs multiple (approx 10) databases ranging from 500MB to over 24 GB in > size. All of them are of the same structure, and almost all of them have > very heavy read and writes. > > pgtune (http://pgfoundry.org/projects/pgtune/) suggests the settings to be > changed as : > > maintenance_work_mem = 960MB # pg_generate_conf wizard 2010-02-03 > checkpoint_completion_target = 0.9 # pg_generate_conf wizard 2010-02-03 > effective_cache_size = 11GB # pg_generate_conf wizard 2010-02-03 > work_mem = 160MB # pg_generate_conf wizard 2010-02-03 > wal_buffers = 8MB # pg_generate_conf wizard 2010-02-03 > checkpoint_segments = 16 # pg_generate_conf wizard 2010-02-03 > shared_buffers = 3840MB # pg_generate_conf wizard 2010-02-03 > max_connections = 100 # pg_generate_conf wizard 2010-02-03 > > > While this gives me the changes for postgresql.conf, I am not sure of of > the chnages that I need to make in FreeBSD to support such large memory > allocations. The last time I tried, Postgres refused to start and I had to > fall back to the default settings. > > I would appreciate if somebody could point out the sysctl/loader.conf > settings that I need to have in FreeBSD. > > With regards > > Amitabh Kant > --0016364ed9d43ac80b047eb3a7e0 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Forgot to add that I am using Postgres 8.4.2 from the default ports of Free= BSD.

With regards

Amitabh Kant

On Wed, Feb 3, 2010 at 8:40 PM, Amitabh Kant <amitabhkant@gmail.com> w= rote:
Hello

I h= ave a server dedicated for Postgres with the following specs:

RAM 16= GB, 146GB SAS (15K) x 4 -=A0 RAID 10 with BBU, Dual Xeon=A0 E5345=A0 @ 2.33= GHz=A0
OS: FreeBSD 8.0

It runs multiple (approx 10) databases ranging from = 500MB to=20 over 24 GB in size. All of them are of the same structure, and almost=20 all of them have very heavy read and writes.

pgtune (http://pgfoundry.org/projects/pgtune/) suggests the settings to be= changed as :

maintenance_work_mem =3D 960MB # pg_generate_conf wiza= rd 2010-02-03
checkpoint_completion_target =3D 0.9 # pg_generate_conf wizard 2010-02-03effective_cache_size =3D 11GB # pg_generate_conf wizard 2010-02-03
wor= k_mem =3D 160MB # pg_generate_conf wizard 2010-02-03
wal_buffers =3D 8MB= # pg_generate_conf wizard 2010-02-03
checkpoint_segments =3D 16 # pg_generate_conf wizard 2010-02-03
shared_b= uffers =3D 3840MB # pg_generate_conf wizard 2010-02-03
max_connections = =3D 100 # pg_generate_conf wizard 2010-02-03


While this gives me= the changes for postgresql.conf, I am not sure of=A0 of the chnages that I= need to make in FreeBSD to support such large memory allocations. The last= time I tried, Postgres refused to start and I had to fall back to the defa= ult settings.

I would appreciate if somebody could point out the sysctl/loader.conf s= ettings that I need to have in FreeBSD.

With regards

Amitabh Kant

--0016364ed9d43ac80b047eb3a7e0--