Re: OOM-killer issue with a specific query

On Mon, Dec 19, 2011 at 8:52 AM, <nabble(dot)30(dot)miller_2555(at)spamgourmet(dot)com> wrote:
> Under steady-state conditions, the following shows the virtual memory size
> for postgres backend processes:
>      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
>     8506 postgres  20   0 2327m 3084 1792 S  0.0  0.0   0:00.33 postgres
>     8504 postgres  20   0 2326m  14m  13m S  0.0  0.2   0:01.32 postgres
>     8505 postgres  20   0 2326m  728  452 S  0.0  0.0   0:00.91 postgres
>     3582 postgres  20   0 2325m  54m  53m S  0.0  0.7   0:02.03 postgres

FYI, this is not swap usage. VIRT is all the memory a process has
handles open to everywhere, including libs that it's linked against
that might not even be loaded. Generally speaking, VIRT is close to
worthless for troubleshooting.

> My current relevant postgresql.conf settings are the following:
>     shared_buffers = 2100MB
>     temp_buffers = 8MB
>     work_mem = 32MB
>     maintenance_work_mem = 16MB
>     max_stack_depth = 2MB
>     constraint_exclusion = partition

What's max_connections?

> When executing the query, I've been watching the "top" activity, sorted by
> resident memory. Upon execution, no other processes appear to take
> additional resident memory, except a postgres backend process servicing the
> query, which goes to +6Gb (triggering the OOM-killer). Given the settings in
> postgresql.conf, and my anecdotal understanding of Postgres memory
> management functions, I am uncertain why Postgres exhausts physical memory
> instead of swapping to temporary files.

> EXPLAIN ANALYZE output:
>     Note: could not produce output for exact query due to OOM-killer, but
> ran query by limiting the subquery to the first 50 results. The planner
> iterates over all partitions, but only the first two partitions are noted
> for brevity.

This may be one instance where the regular explain will be more
useful. it's quite likely that the query changes when there is no
limit. If you compare what explain for the full query says, and what
explain (analyze) for the abridged one says, the part that's causing
you to run out of memory may be more obvious.