Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 4DEFD6323C2 for ; Thu, 4 Feb 2010 06:08:29 -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 38440-07 for ; Thu, 4 Feb 2010 10:08:13 +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 7CA05633516 for ; Thu, 4 Feb 2010 06:08:18 -0400 (AST) Received: by qw-out-2122.google.com with SMTP id 5so157473qwd.61 for ; Thu, 04 Feb 2010 02:08:17 -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=EBu0T7K8F7+0YAnGtIhRv+D82lJnarPjAp90FTCtSiQ=; b=N52XecgkdH3J5iO4HlkVExOQzULfMrLA7YmJokCxscgovc3d/fHwdfOZFh7l6GT2wH uWAPMjN3DZmuKxuCjXaCaLveoTvdZbHKgyHXYi4bzbgnSCtP1x+LdF1JldA5biaY64k6 wDKgj8lCFliolPnRpaN6iEXwewhPyJHr/l3R4= 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=GkFWBamRJJbMr1nkWAkmCgxzKQ01V5PLQPq9azU+V5xHwNPyBzvgh4OTF2+vKXfctp bI7p9DwNLq39zs/yR3F7t8I4OOVukvfKPHjSgu+AXgRXfjrDA77kwm0+os25BXQxF+bT +kAO36RqYGsa4mgWx5Y2MFyF+WrxHBst3f56M= MIME-Version: 1.0 Received: by 10.229.107.29 with SMTP id z29mr461773qco.42.1265278097109; Thu, 04 Feb 2010 02:08:17 -0800 (PST) In-Reply-To: <4B69F1C3.6060302@2ndquadrant.com> References: <84b68b3d1002030710x76c31adft58c56624409011fd@mail.gmail.com> <603c8f071002031243m4e0b3d95n8d810c6f40d274fa@mail.gmail.com> <4B69F1C3.6060302@2ndquadrant.com> From: Amitabh Kant Date: Thu, 4 Feb 2010 15:37:57 +0530 Message-ID: <84b68b3d1002040207n14c217aam7e9c8349dcc3de9c@mail.gmail.com> Subject: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes To: Greg Smith Cc: Robert Haas , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0023544714ac85340b047ec38337 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.763 tagged_above=-10 required=5 tests=AWL=0.835, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/85 X-Sequence-Number: 37506 --0023544714ac85340b047ec38337 Content-Type: text/plain; charset=ISO-8859-1 On Thu, Feb 4, 2010 at 3:29 AM, Greg Smith wrote: > Robert Haas wrote: > > On Wed, Feb 3, 2010 at 10:10 AM, Amitabh Kant wrote: > > > work_mem = 160MB # pg_generate_conf wizard 2010-02-03 > > > Overall these settings look sane, but this one looks like an > exception. That is an enormous value for that parameter... > > > > Yeah, I think I need to retune the suggestions for that parameter. The > idea behind the tuning profile used in the "web" and "OLTP" setups is that > you're unlikely to have all the available connections doing something > involving sorting at the same time with those workloads, and when it does > happen you want it to use the fastest approach possible even if that takes > more RAM so the client waiting for a response is more likely to get one on > time. That's why the work_mem figure in those situations is set very > aggressively: total_mem / connections, so on a 16GB server that comes out > to the 160MB seen here. I'm going to adjust that so that it's capped a > little below (total_mem - shared_buffers) / connections instead. > Thanks Robert & Greg. From what others have suggested, I am going in for the following changes: /boot/loader.conf: kern.ipc.semmni=512 kern.ipc.semmns=1024 kern.ipc.semmnu=512 /etc/sysctl.conf: kern.ipc.shm_use_phys=1 kern.ipc.shmmax=4089446400 kern.ipc.shmall=1050000 kern.maxfiles=16384 kern.ipc.semmsl=1024 kern.ipc.semmap=512 vfs.ufs.dirhash_maxmem=4194304 vfs.read_max=32 /usr/local/pgsql/data/postgresql.conf: 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 = 110MB # pg_generate_conf wizard 2010-02-03 Reduced as per Robert/Greg suggestions 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 Hope this works out good in my case. With regards Amitabh Kant --0023544714ac85340b047ec38337 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
On Thu, Feb 4, 2010 at 3:29 AM, Greg Smith <= greg@2ndquadrant.com> wrote:
=20
Robert Haas wrote: 
On Wed, Feb 3, 2010 at 10:10 AM, Amitabh Kant <amitabhkant@gmail.com> wrot=
e:
  
work_mem =3D 160MB # pg_generate_conf wizard 2010-02-03
    
Overall these settings look sane, but this one looks like an
exception.  That is an enormous value for that parameter...

Yeah, I think I need to retune the suggestions for that parameter.=A0 The idea behind the tuning profile used in the "web" and "OLTP&q= uot; setups is that you're unlikely to have all the available connections doing something involving sorting at the same time with those workloads, and when it does happen you want it to use the fastest approach possible even if that takes more RAM so the client waiting for a response is more likely to get one on time.=A0 That's why the work_mem figure in those situations is set very aggressively:=A0 total_mem / connections, so on a 16GB server that comes out to the 160MB seen here.=A0 I'm going to adjust that so that it's capped a little below (total_mem - shared_buffers) / connections instead.

Than= ks Robert & Greg.=A0 From what others have suggested,=A0 I am going in = for the following changes:
/boot/loader.conf:

kern.ipc.semmni=3D5= 12
kern.ipc.semmns=3D1024
kern.ipc.semmnu=3D512



/etc/sysctl.= conf:

kern.ipc.shm_use_phys=3D1
kern.ipc.shmmax=3D4089446400
k= ern.ipc.shmall=3D1050000
kern.maxfiles=3D16384
kern.ipc.semmsl=3D1024=
kern.ipc.semmap=3D512
vfs.ufs.dirhash_maxmem=3D4194304
vfs.read_max=3D32



/usr/l= ocal/pgsql/data/postgresql.conf:

maintenance_work_mem=A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 =3D 960MB=A0=A0=A0 =A0=A0=A0 # pg_generate_conf wizard 201= 0-02-03
checkpoint_completion_target=A0=A0=A0 =3D 0.9=A0=A0=A0 =A0=A0=A0= # pg_generate_conf wizard 2010-02-03
effective_cache_size=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =3D 11GB=A0=A0=A0 =A0=A0= =A0 # pg_generate_conf wizard 2010-02-03
work_mem=A0=A0=A0 =A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =3D 110MB=A0=A0=A0 =A0=A0=A0 # pg_gene= rate_conf wizard 2010-02-03 Reduced as per Robert/Greg suggestions
wal_b= uffers=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =3D 8MB= =A0=A0=A0 =A0=A0=A0 # pg_generate_conf wizard 2010-02-03
checkpoint_segments=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =3D 16=A0=A0=A0 = =A0=A0=A0 # pg_generate_conf wizard 2010-02-03
shared_buffers=A0=A0=A0 = =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =3D 3840MB=A0=A0=A0 # pg_generate_c= onf wizard 2010-02-03
max_connections=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 =3D 100=A0=A0=A0 =A0=A0=A0 # pg_generate_conf wizard 2010-= 02-03


Hope this works out good in my case.

With regards

Ami= tabh Kant


--0023544714ac85340b047ec38337--