Re: Autovacuum running out of memory

From: Alexis Lê-Quôc <alq(at)datadoghq(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Autovacuum running out of memory
Date: 2011-08-10 19:08:49
Message-ID: CAAGz8TOkadGSS=_bOC_F5-pXQ4dhB=AjT9H=notCkKfD8AtxHA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Wed, Aug 10, 2011 at 2:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alexis Le-Quoc <alq(at)datadoghq(dot)com> writes:
>> On Wed, Aug 10, 2011 at 1:17 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> However, I find it a bit odd that you're getting this failure in what
>>> appears to be a 64-bit build.  That means you're not running out of
>>> address space, so you must actually be out of RAM+swap.  Does the
>>> machine have only 4GB or so of RAM?  If so, that value for
>>> shared_buffers is unrealistically large; it's not leaving enough RAM for
>>> other purposes such as this.
>
>> The box has little under 8GB (it's on EC2, a "m1.large" instance)
>> There is no swap.
>
> Hmph.  Is there other stuff being run on the same instance?  Are there a
> whole lot of active PG processes?  Maybe Amazon isn't really giving you
> a whole 8GB, or there are weird address space restrictions in the EC2
> environment.  Anyway I think I'd suggest reducing shared_buffers to 1GB
> or so.
>

Done and that fixed it. Thanks.

Now this is counter-intuitive (so much for intuition).
Any pointers to educate myself on why more shared buffers is
detrimental? I thought they would only compete with the OS page cache.
Could it be caused by the "no-overcommit" policy that I told the
kernel to enforce.

As far as other things running on the same instance, nothing stands
out. It is a "dedicated" db instance.

>>> Where did you get the above-quoted parameter settings, anyway?
>
>> In turn they come from High-Performance Postgresql 9.0
>> (http://www.postgresql.org/about/news.1249)
>
> I'm sure even Greg wouldn't claim his methods are good to more than one
> or two significant digits.

Agreed, they are meaningless. I just did not make the effort to
automatically round the values in my ruby code.

--
Alexis Lê-Quôc

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2011-08-10 19:27:51 Re: Autovacuum running out of memory
Previous Message Tom Lane 2011-08-10 18:54:23 Re: Autovacuum running out of memory