On Mon, Dec 19, 2011 at 8:52 AM, <nabble.30.miller_2555@spamgourmet.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.
