Message-ID: <492DD1EA.8000504@mochima.com>
Date: Wed, 26 Nov 2008 17:47:06 -0500
From: Carlos Moreno <morenopg@mochima.com>
User-Agent: Thunderbird 2.0.0.18 (X11/20081125)
MIME-Version: 1.0
To: Ryan Hansen <ryan.hansen@brightbuilders.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Memory Allocation
References: <011101c95013$b6fca0a0$24f5e1e0$@hansen@brightbuilders.com>
In-Reply-To: <011101c95013$b6fca0a0$24f5e1e0$@hansen@brightbuilders.com>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit

Ryan Hansen wrote:
>
> Hey all,
>
> This may be more of a Linux question than a PG question, but I’m
> wondering if any of you have successfully allocated more than 8 GB of
> memory to PG before.
>
> I have a fairly robust server running Ubuntu Hardy Heron, 24 GB of
> memory, and I’ve tried to commit half the memory to PG’s shared
> buffer, but it seems to fail.
>

Though not sure why this is happening or whether it is normal, I would
suggest that such setting is maybe too high. From the Annotated
postgresql.conf document at

http://www.powerpostgresql.com/Downloads/annotated_conf_80.html,

the suggested range is 8 to 400MB. They specifically say that it
should never be set to more than 1/3 of the available memory, which
in your case is precisely the 8GB figure (I guess that's just a
coincidence --- I doubt that the server would be written so that it
fails to start if shared_buffers is more than 1/3 of available RAM)

Another important parameter that you don't mention is the
effective_cache_size, which that same document suggests should
be about 2/3 of available memory. (this tells the planner the amount
of data that it can "probabilistically" expect to reside in memory due
to caching, and as such, the planner is likely to produce more
accurate estimates and thus better query optimizations).

Maybe you could set shared_buffers to, say, 1 or 2GB (that's already
beyond the recommended figure, but given that you have 24GB, it
may not hurt), and then effective_cache_size to 16GB or so?

HTH,

Carlos
--