Re: Running out of memory while making a join

On 11/11/2012 12:50 AM, Carlos Henrique Reimer wrote:
> Hi,
>
> How is the best way to attach a debugger to the SELECT and identify
> why is it exhausting server storage.

This page is more focused on getting a stack trace after a crash, but
provides some information about how to identify the backend to connect
to, how to connect with gdb, etc:

http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD

Short version is:

Get the pid of the backend that's growing with

SELECT pg_backend_pid()

before running the test, from `top`, or from pg_stat_activity.

gdb -p pid_of_backend

when you get a (gdb) prompt:

cont<enter>

Let the memory use grow until you think it's fairly big. Hit control-C
in the gdb terminal to interrupt execution of the backend. You'll get
another (gdb) prompt.

Now follow Tom's advice:
> In gdb,
> call MemoryContextStats(TopMemoryContext)
> should produce some useful information on the process's stderr file.

It may also be helpful to have a stack trace or two or three to see what
Pg is doing while it's running and growing, so if you want, also run:

bt

then "cont" again, control C, bt again, cont, control c, bt, and provide
the copied and pasted backtraces in case they provide additional
information about what's going on.

--
Craig Ringer