Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 817C8634954 for ; Thu, 4 Feb 2010 05:17:03 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 08319-01 for ; Thu, 4 Feb 2010 09:16:52 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f188.google.com (mail-qy0-f188.google.com [209.85.221.188]) by mail.postgresql.org (Postfix) with ESMTP id 6FCB9634681 for ; Thu, 4 Feb 2010 05:16:52 -0400 (AST) Received: by qyk26 with SMTP id 26so1064267qyk.5 for ; Thu, 04 Feb 2010 01:16:51 -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:cc:content-type; bh=VCee+bxYSkU2yPttjlst9dbStvubRYatjBtYuGAUM0s=; b=mJPdLsBnOgiqzK1aPCUQRSntAeamSKzkVUU9nkk4Ow8A8k3eq3Erv0djlNumbOoRju alUiWWUB627OuPI/K0VP87Zf8AmXvUe62+dvn1/P0ohrMCrw3WMTDdoz9lcOmOE/rBhn TaAFa0jk877abhfG8HBrgs2Itywp006cLLs7s= 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 :cc:content-type; b=kjt3rh8FVqT2pWJ+wpay3t0RPWIRiRCK6TzB2BwuVsjCMsXrCKA4zL3ao0H5+gBPoH Vm9fSR/QJQQEbWnbfMB5irmVlCE430jhz4s49mf6xZ/oui2do8ehQkzRUjvS2OfKMFJj Wt1PIvcCa+iJ/e67AGFxdO6D1I1fqrS6anTeY= MIME-Version: 1.0 Received: by 10.229.12.27 with SMTP id v27mr436026qcv.45.1265275011119; Thu, 04 Feb 2010 01:16:51 -0800 (PST) In-Reply-To: <4B69C363.10506@squeakycode.net> References: <84b68b3d1002030710x76c31adft58c56624409011fd@mail.gmail.com> <4B69C363.10506@squeakycode.net> From: Amitabh Kant Date: Thu, 4 Feb 2010 14:46:31 +0530 Message-ID: <84b68b3d1002040116r2e82b280l39019bbcea6bd810@mail.gmail.com> Subject: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes To: Andy Colson Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=00151757470094bcc9047ec2cb09 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/82 X-Sequence-Number: 37503 --00151757470094bcc9047ec2cb09 Content-Type: text/plain; charset=ISO-8859-1 On Thu, Feb 4, 2010 at 12:11 AM, Andy Colson wrote: > On 2/3/2010 9:10 AM, 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. >> >> >> With regards >> >> Amitabh Kant >> > > What problems are you having? Is it slow? Is there something you are > trying to fix, or is this just the first tune up? > This is the first tune up. The system has worked pretty fine till now, but it does lag once in a while, and I would like to optimize it before it becomes a bigger issue. > > > memory allocations. The last time I tried, Postgres refused to start and > > I had to fall back to the default settings. > > Its probably upset about the amount of shared mem. There is probably a way > in bsd to set the max amount of shared memory available. A Quick google > turned up: > > kern.ipc.shmmax > > Dunno if thats right. When you try to start PG, if it cannot allocate > enough shared mem it'll spit out an error message into its log saying how > much it tried to allocate. > > Check: > http://archives.postgresql.org/pgsql-admin/2004-06/msg00155.php > > > > > > > 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 > > Some of these seem like too much. I'd recommend starting with one or two > and see how it runs. Then increase if you're still slow. > > Start with effective_cache_size, shared_buffers and checkpoint_segments. > > Wait until very last to play with work_mem and maintenance_work_mem. > > > -Andy > I would keep that in mind. Thanks Andy With regards Amitabh --00151757470094bcc9047ec2cb09 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
On Thu, Feb 4, 2010 at 12:11 AM, Andy Colson <andy@squeakycode= .net> wrote:
On 2/3/2010 9:10 AM, Amitabh Kant wrote:
Hello

I have a server dedicated for Postgres with the following specs:

RAM 16GB, 146GB SAS (15K) x 4 - =A0RAID 10 with BBU, Dual Xeon =A0E5345 =A0= @
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.


With regards

Amitabh Kant

What problems are you having? =A0Is it slow? =A0Is there something you are = trying to fix, or is this just the first tune up?

This is the first tune up. The system has worked pr= etty fine till now, but=A0 it does lag once in a while, and I would like to= optimize it before it becomes a bigger issue.



> memory allocations. The last time I tried, Postgres refused to start a= nd
> I had to fall back to the default settings.

Its probably upset about the amount of shared mem. =A0There is probably a w= ay in bsd to set the max amount of shared memory available. =A0A Quick goog= le turned up:

kern.ipc.shmmax

Dunno if thats right. =A0When you try to start PG, if it cannot allocate en= ough shared mem it'll spit out an error message into its log saying how= much it tried to allocate.

Check:
http://archives.postgresql.org/pgsql-admin/2004-06/msg00= 155.php





> maintenance_work_mem =3D 960MB # pg_generate_conf wizard 2010-02-03 > checkpoint_completion_target =3D 0.9 # pg_generate_conf wizard 2010-02= -03
> effective_cache_size =3D 11GB # pg_generate_conf wizard 2010-02-03
> work_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_buffers =3D 3840MB # pg_generate_conf wizard 2010-02-03
> max_connections =3D 100 # pg_generate_conf wizard 2010-02-03

Some of these seem like too much. =A0I'd recommend starting with one or= two and see how it runs. =A0Then increase if you're still slow.

Start with effective_cache_size, shared_buffers and checkpoint_segments.
Wait until very last to play with work_mem and maintenance_work_mem.


-Andy

I would keep that in mind. Thanks Andy
With regards

Amitabh
--00151757470094bcc9047ec2cb09--