On Mon, Mar 5, 2012 at 4:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Mike C <smith.not.western@gmail.com> writes:
>> I have been using table 17-2, Postgres Shared Memory Usage
>> (http://www.postgresql.org/docs/9.1/interactive/kernel-resources.html)
>> to calculate approximately how much memory the server will use. I'm
>> using Postgres 9.1 on a Linux 2.6 (RHEL 6) 64bit system, with 8GB RAM.
>> Database is approximately 5GB, and is a mixture of read/write.
>> Postgres is occasionally being killed by the linux oom-killer. I am
>> trying to understand how much memory postgres could use, and how to
>> change the configuration to bring it down to a level that won't get it
>> killed.
>
> Basically, you can't fix it that way, at least not if you want to have a
> sane configuration. The problem is misdesign of the OOM killer: it will
> count the shared memory block against the postmaster *once for each
> child process*. The only realistic solution is to turn off OOM kill for
> the postmaster (and maybe its children too, or maybe you'd rather have
> them immune as well). The former is pretty easy to do if you're
> launching the postmaster from a root-privileged initscript. I think
> most prepackaged versions of PG are set up to be able to do this
> already. If you want the children OOM-killable it requires a
> source-code tweak as well, since that property is normally inherited.
Ok, that makes sense. With regards to work_mem, am I right in thinking
the child processes only allocate enough memory to meet the task at
hand, rather than the full 16M specified in the config file?
> But anyway, your calculations are totally off:
>
>> Shared Disk Buffers = 36,077,725,286,400
>> = (block_size + 208) * shared_buffers
>> = (8192 + 208) * 4294967296
>> = ~33TB
>
> I think you've multiplied by the block size one time too many. Ditto
> for WAL buffers.
Yes spot on, removed the block_size and it is now the more sane ~4.1GB.
Thanks for your help,
Mike