On Mon, Dec 19, 2011 at 8:52 AM, <nabble.30.miller_2555@xxxxxxxxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
