Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 992CAB5DBF0 for ; Sun, 21 Aug 2011 02:45:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 51183-06 for ; Sun, 21 Aug 2011 05:44:58 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm20.access.bullet.mail.sp2.yahoo.com (nm20.access.bullet.mail.sp2.yahoo.com [98.139.44.147]) by mail.postgresql.org (Postfix) with SMTP id 23520B5DBCB for ; Sun, 21 Aug 2011 02:44:57 -0300 (ADT) Received: from [98.139.44.103] by nm20.access.bullet.mail.sp2.yahoo.com with NNFMP; 21 Aug 2011 05:44:56 -0000 Received: from [98.139.44.70] by tm8.access.bullet.mail.sp2.yahoo.com with NNFMP; 21 Aug 2011 05:44:56 -0000 Received: from [127.0.0.1] by omp1007.access.mail.sp2.yahoo.com with NNFMP; 21 Aug 2011 05:44:56 -0000 X-Yahoo-Newman-Id: 443237.46016.bm@omp1007.access.mail.sp2.yahoo.com Received: (qmail 40541 invoked from network); 21 Aug 2011 05:44:56 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=sbcglobal.net; h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:Message-ID:From:To:Cc:References:Subject:Date:MIME-Version:Content-Type:X-Priority:X-MSMail-Priority:X-Mailer:X-MimeOLE; b=ZWV5ANMxqU0sJxWACV0x4/XrFHucsLSuz4sGTimnKQpX01AXRBcczBN/IaVM44W7Gsy5UoaYQpMVszgsX2TrSHIWPwyLU/Nx/jq5TpsrtBOsNTMHEh3jDWyhh6926Jlnr59ibdTnv/GwZw2Am1EDsDHv2R/UI7lwkzzIgSE3xoI= ; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sbcglobal.net; s=s1024; t=1313905496; bh=QQkM5JoNxxhahktCaPgUiKnRdd+2yzA0MQTOWpTDlXU=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:Message-ID:From:To:Cc:References:Subject:Date:MIME-Version:Content-Type:X-Priority:X-MSMail-Priority:X-Mailer:X-MimeOLE; b=KtTdLMK9XKt/DCynjCPvqXO4K1Y/boiGDOVs9tuN+tgXNeTFcOxiiYc0twNCanSQ3ACNSNoPEN+Xe2iAwHMoTqEtxdKmScLxTxJlHao0EaGmB1WuxOFFW6KSiByg/tNvCJ0obfGkYhdsQwWe5WMipz0kYIG+7ilOVQT3tt+C96c= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: M4J9sXkVM1kBSFGNg2HVbfswewcKVLAh9G85soKecPUnuzz YBWoDW5G14YbG4lcGirDPukaB7Pvf3CxPXxI28G16uI78NmllhD8Qo.bYHGT fxHHAe6c0OL7elREygfbRMQaWtY5H8IQTQcwRPaV6JgwoIVPKB8W3pEuYAyq xZlEsXocGZI2OJkCdok3X..ymm5poP.wNZoSLRsi.BqwIQ0g5N0nroy1lILL MzYXkDuKSJXqPoAm4S8PF_DvF1w45d02DtTTU_ByHUBB87RdIuCT_M2QTBU9 yVJWcHo25Ba8yyTwdNnNXVZxsrm2Gecjn0msLypKDXd8M5xQmgkc6qag9QHi _8bRgVBuAYnlsfRZOj.m_YX1PJD2nEeoMH5cz0G.JzWM4IhCQiIQ7tSDX9sq DyJ74ylotEtwneH5JUoDxc4hE9gqI X-Yahoo-SMTP: EtaTb4uswBBSx2FARGS8Y34d19mAO_hWIqN6lfRu55otbg-- Received: from BERNICE (midgems@71.135.45.172 with login) by smtp104.sbc.mail.ne1.yahoo.com with SMTP; 20 Aug 2011 22:44:55 -0700 PDT Message-ID: <2B26CB3B354F486195B61647B094753F@BERNICE> From: "Midge Brown" To: "Scott Marlowe" Cc: References: Subject: Re: settings input for upgrade Date: Sat, 20 Aug 2011 22:44:58 -0700 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0053_01CC5F8A.C9CEF190" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5931 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, UNPARSEABLE_RELAY=0.001 X-Spam-Level: X-Archive-Number: 201108/239 X-Sequence-Number: 44686 This is a multi-part message in MIME format. ------=_NextPart_000_0053_01CC5F8A.C9CEF190 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thank you!=20 ----- Original Message -----=20 From: Scott Marlowe=20 To: Midge Brown=20 Cc: pgsql-performance@postgresql.org=20 Sent: Saturday, August 20, 2011 9:01 PM Subject: Re: [PERFORM] settings input for upgrade On Thu, Aug 18, 2011 at 3:55 PM, Midge Brown wrote: > Here are the changes I made to postgres.conf. The only differences = between > the conf file for DB1 and those for DB2 & 3 are the port and > effective_cache_size (which I made slightly smaller -- 8 GB instead = of 10 -- > for the 2 write-heavy DBs). The 600 max connections are often idle = and don't > get explicitly closed in the application. I'm looking at connection = pooling > as well. > work_mem =3D 128MB I'd lower this unless you are certain that something like 16MB just isn't gonna get similar performance. Even with mostly connections idle, 128M is a rather large work_mem. Remember it's per sort, per connection. It can quickly cause the kernel to dump file cache that keeps the machine running fast if a couple dozen connections run a handful of large sorts at once. What happens is that while things run smooth when there's low to medium load, under high load the machine will start thrashing trying to allocate too much work_mem and then just slow to a crawl. ------=_NextPart_000_0053_01CC5F8A.C9CEF190 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Thank you!
----- Original Message -----
From:=20 Scott=20 Marlowe
Cc: pgsql-performance@postgr= esql.org=20
Sent: Saturday, August 20, 2011 = 9:01=20 PM
Subject: Re: [PERFORM] settings = input for=20 upgrade

On Thu, Aug 18, 2011 at 3:55 PM, Midge=20 Brown wrote:
> Here are the changes I made to = postgres.conf. The=20 only differences between
> the conf file for DB1 and those for = DB2 &=20 3 are the port and
> effective_cache_size (which I made slightly = smaller=20 -- 8 GB instead of 10 --
> for the 2 write-heavy DBs). The 600 = max=20 connections are often idle and don't
> get explicitly closed in = the=20 application. I'm looking at connection pooling
> as = well.

>=20 work_mem =3D 128MB

I'd lower this unless you are certain that = something=20 like 16MB just
isn't gonna get similar performance.  Even with = mostly=20 connections
idle, 128M is a rather large work_mem.  Remember = it's per=20 sort, per
connection.  It can quickly cause the kernel to dump = file=20 cache that
keeps the machine running fast if a couple dozen = connections run=20 a
handful of large sorts at once.  What happens is that while = things=20 run
smooth when there's low to medium load, under high load the=20 machine
will start thrashing trying to allocate too much work_mem = and=20 then
just slow to a crawl. ------=_NextPart_000_0053_01CC5F8A.C9CEF190--