To: =?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= <alq@datadoghq.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Autovacuum running out of memory 
In-reply-to: 
 <CAAGz8TOkadGSS=_bOC_F5-pXQ4dhB=AjT9H=notCkKfD8AtxHA@mail.gmail.com>
References: 
 <CAAGz8TMPCzcMR0uo1Kjzi8uBgv3TpLRWVDOB4wHTdSASt=Q=1A@mail.gmail.com>
	<13854.1312996666@sss.pgh.pa.us>
	<CAAGz8TPX8O7e=r8ZsLEE2ABW6yv9gZreXqGeDydJgeQF2oCf_Q@mail.gmail.com>
	<15771.1313002463@sss.pgh.pa.us>
	<CAAGz8TOkadGSS=_bOC_F5-pXQ4dhB=AjT9H=notCkKfD8AtxHA@mail.gmail.com>
Comments: In-reply-to =?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= <alq@datadoghq.com>
	message dated "Wed, 10 Aug 2011 15:08:49 -0400"
Date: Wed, 10 Aug 2011 15:27:51 -0400
Message-ID: <16358.1313004471@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= <alq@datadoghq.com> writes:
> On Wed, Aug 10, 2011 at 2:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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?

My guess is that it's an address space restriction at bottom.  Postgres
starts (on typical systems) with program text at the beginning of its
address space, static data after that, a large hole in the middle, and
stack up at the top.  Then the shared memory block gets allocated
somewhere in the hole, at a spot that's more or less at the whim of the
OS.  If a Postgres process subsequently asks for more private memory via
malloc, it can only possibly get as much as the distance from the
original static area to the shared memory block's position in the
process's address space.

So I'm thinking that the EC2 environment is giving you some lowball
address for the shared memory block that's constraining the process's
private memory space to just a few hundred meg, even though in a 64-bit
build there's room for umpteen gigabytes.  Possibly it's worth filing a
bug with Amazon about how they should pick a more sensible address ...
but first you should confirm that theory by looking at the process
memory map (should be visible in /proc someplace).

It may also be that the problem is not process-address-space related but
reflects some inefficiency in EC2's overall use of RAM, possibly
exacerbated by PG's request for a large shared memory block.  But you'd
need to find an EC2 expert to investigate that idea.

			regards, tom lane