Re: Autovacuum running out of memory

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