Re: Tons of free RAM. Can't make it go away.

On 10/27/2012 10:49 PM, Віталій Тимчишин wrote:

> It can be that some query(s) use a lot of work mem, either because of
> high work_mem setting or because of planner error. In this case the
> moment query runs it will need memory that will later be returned and
> become free. Usually this can be seen as active memory spike with a lot
> of free memory after.

Yeah, I had briefly considered that. But our work-mem is only 16MB, and
even a giant query would have trouble allocating 10+GB with that size of
work-mem buckets.

That's why I later listed the numa info. In our case, processor 0 is
heavily unbalanced with its memory accesses compared to processor 1. I
think the theory that we didn't start with interleave put an 8GB (our
shared_buffers) segment all on processor 0, which unbalanced a lot of
other stuff.

Of course, that leaves 4-6GB unaccounted for. And numactl still shows a
heavy preference for freeing memory from proc 0. It seems to only do it
on this node, so we're going to switch nodes soon and see if the problem
reappears. We may have to perform a node hardware audit if this persists.

Thanks for your input, though. :)

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

From pgsql-performance-owner(at)postgresql(dot)org Mon Oct 29 16:42:28 2012
Received: from makus.postgresql.org ([98.129.198.125])
by malur.postgresql.org with esmtp (Exim 4.72)
(envelope-from <jeff(dot)janes(at)gmail(dot)com>)
id 1TSsPz-0004so-M0
for pgsql-performance(at)postgresql(dot)org; Mon, 29 Oct 2012 16:42:27 +0000
Received: from mail-ie0-f174.google.com ([209.85.223.174])
by makus.postgresql.org with esmtp (Exim 4.72)
(envelope-from <jeff(dot)janes(at)gmail(dot)com>)
id 1TSsPx-0002Gl-Mc
for pgsql-performance(at)postgresql(dot)org; Mon, 29 Oct 2012 16:42:26 +0000
Received: by mail-ie0-f174.google.com with SMTP id k13so6847858iea.19
for <pgsql-performance(at)postgresql(dot)org>; Mon, 29 Oct 2012 09:42:25 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=gmail.com; s 120113;
h=mime-version:in-reply-to:references:date:message-id:subject:from:to
:cc:content-type;
bh=QlF9Zw1rgSnU75GTyYyMQuZ7/WJoBSGdhSXr1I/1ng8=;
b=rHo03cjL+AxNbgth+jUBml9GF0vg4khcM0OU8ewCdXGmU1Aj7DxdrMGNdie6BTC7ur
fBplxeI7YNvLdvRJBGCCQmk2+6JjcCNs9DIZvjoxqLwSD591+70Wl+fGElorpbps9Cr+
Uawh8tBDEDQIIPLuCOKVFqYvPuAOTrj9SNCI+w5KFYMY+7JLeBLshPV+JGnlqtIdS2+x
pkA+578gi71LyMgayQoXcIbtcc0qfHMpzNpCz/qXQTZL7LA7//KjDnQa4r9QKzeNpJg3
uWe6n/JfCCJ/DNy6nxSO5FYoxa+rQTc9HqGtqH0LzhAiXwT59i5DzjfY8XZoRaFQGnwQ
dFmA=
MIME-Version: 1.0
Received: by 10.50.181.231 with SMTP id dz7mr5938873igc.59.1351528944982; Mon,
29 Oct 2012 09:42:24 -0700 (PDT)
Received: by 10.42.255.136 with HTTP; Mon, 29 Oct 2012 09:42:24 -0700 (PDT)
In-Reply-To: <D960CB61B694CF459DCFB4B0128514C2089A5F80(at)exadv11(dot)host(dot)magwien(dot)gv(dot)at>
References: <D960CB61B694CF459DCFB4B0128514C2089A5F80(at)exadv11(dot)host(dot)magwien(dot)gv(dot)at>
Date: Mon, 29 Oct 2012 09:42:24 -0700
Message-ID: <CAMkU=1z_GYXM+cn_TuVLx78_FAeFVw0+H0KDjFVf+rooiWYebw(at)mail(dot)gmail(dot)com>
Subject: Re: Replaying 48 WAL files takes 80 minutes
From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
Cc: pgsql-performance(at)postgresql(dot)org
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201210/351
X-Sequence-Number: 48310

On Mon, Oct 29, 2012 at 6:05 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
> I am configuring streaming replication with hot standby
> with PostgreSQL 9.1.3 on RHEL 6 (kernel 2.6.32-220.el6.x86_64).
> PostgreSQL was compiled from source.
>
> It works fine, except that starting the standby took for ever:
> it took the system more than 80 minutes to replay 48 WAL files
> and connect to the primary.
>
> Can anybody think of an explanation why it takes that long?

Could the slow log files be replaying into randomly scattered pages
which are not yet in RAM?

Do you have sar or vmstat reports?

Cheers,

Jeff