Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VM0p0-0001ae-AF for pgsql-admin@arkaria.postgresql.org; Tue, 17 Sep 2013 19:20:26 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VM0oz-0005Di-Q0 for pgsql-admin@arkaria.postgresql.org; Tue, 17 Sep 2013 19:20:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VM0oz-0005DR-33 for pgsql-admin@postgresql.org; Tue, 17 Sep 2013 19:20:25 +0000 Received: from internet06.ebureau.com ([65.127.24.25]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VM0ou-0000aJ-4P for pgsql-admin@postgresql.org; Tue, 17 Sep 2013 19:20:24 +0000 Received: from localhost (localhost [127.0.0.1]) by internet06.ebureau.com (Postfix) with ESMTP id 1D7073F18EA1; Tue, 17 Sep 2013 14:20:18 -0500 (CDT) X-Virus-Scanned: amavisd-new at ebureau.com Received: from internet06.ebureau.com ([127.0.0.1]) by localhost (internet06.ebureau.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id EGEHqPlVdnsW; Tue, 17 Sep 2013 14:20:12 -0500 (CDT) Received: from punch.office.ebureau.com (punch.office.ebureau.com [10.10.20.24]) by internet06.ebureau.com (Postfix) with ESMTPSA id 6CCCC3F18E80; Tue, 17 Sep 2013 14:20:10 -0500 (CDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_CA6AC83F-AD37-42C0-97F1-4CEF92769397" Mime-Version: 1.0 (Mac OS X Mail 6.5 \(1508\)) Subject: Re: Dumping a database that is not accepting commands? From: Natalie Wenz In-Reply-To: Date: Tue, 17 Sep 2013 14:19:17 -0500 Cc: "pgsql-admin@postgresql.org" Message-Id: <2EE7C659-6FA5-409D-8401-3F8A22E9DF3A@ebureau.com> References: <1379421830.30619.YahooMailNeo@web162902.mail.bf1.yahoo.com> To: bricklen X-Mailer: Apple Mail (2.1508) X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --Apple-Mail=_CA6AC83F-AD37-42C0-97F1-4CEF92769397 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=windows-1252 No=85 the shared_buffers value is just a legacy value that never got = changed (the shmmax value in sysctl is still 1073741824). When I set up = the new database, I set the shared_buffers to 25% of system memory, so = 12GB. (And since the new database is on 9.3, I didn't have to adjust the = sysctl value for shmmax! Happy day!) We used to have maintenance_work_mem set to something smaller, but we = bumped that up after=85=85 the last time this database shut = itself down to avoid wraparound in March 2012. We were hoping that would = help speed the recovery at that time. Not sure if it did, but we left it = that way afterward anyway. On Sep 17, 2013, at 2:02 PM, bricklen wrote: > On Tue, Sep 17, 2013 at 9:48 AM, Natalie Wenz = wrote: > maintenance_work_mem | 10GB > shared_buffers | 128MB >=20 > maintenance_work_mem seems pretty high, and shared_buffers seems = really low. Out of curiousity, were those set as a product of internal = testing which determined those were effective settings? >=20 --Apple-Mail=_CA6AC83F-AD37-42C0-97F1-4CEF92769397 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=windows-1252 No=85 =  the shared_buffers value is just a legacy value that never got = changed (the shmmax value in sysctl is still 1073741824). =  When I set up the new database, I set the shared_buffers to 25% of = system memory, so 12GB. (And since the new database is on 9.3, I didn't = have to adjust the sysctl value for shmmax! Happy = day!)

We used to have maintenance_work_mem set to = something smaller, but we bumped that up after=85<coughs>=85 the = last time this database shut itself down to avoid wraparound in March = 2012. We were hoping that would help speed the recovery at that time. = Not sure if it did, but we left it that way afterward = anyway.


On Sep 17, 2013, at 2:02 = PM, bricklen <bricklen@gmail.com> = wrote:

On Tue, Sep 17, 2013 at 9:48 AM, Natalie Wenz = <nataliewenz@ebureau.com> wrote:
 maintenance_work_mem         =    | 10GB
 shared_buffers       =            | 128MB

maintenance_work_mem seems pretty high, and = shared_buffers seems really low.  Out of curiousity, were those set = as a product of internal testing which determined those were effective = settings?


= --Apple-Mail=_CA6AC83F-AD37-42C0-97F1-4CEF92769397--