From: Carlos Moreno <morenopg@mochima.com>
To: Ryan Hansen <ryan.hansen@brightbuilders.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Memory Allocation
Date: Wed, 26 Nov 2008 17:47:06 -0500
Message-ID: <492DD1EA.8000504@mochima.com> (raw)
In-Reply-To: <011101c95013$b6fca0a0$24f5e1e0$@hansen@brightbuilders.com>
References: <011101c95013$b6fca0a0$24f5e1e0$@hansen@brightbuilders.com>
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
--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: morenopg@mochima.com, ryan.hansen@brightbuilders.com
Subject: Re: Memory Allocation
In-Reply-To: <492DD1EA.8000504@mochima.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox