Re: Postmaster Out of Memory

Jeff Gold <jgold(at)mazunetworks(dot)com> writes:
> Hm. After discussing this with people here we have a hypothesis. The
> process that issues the TRUNCATE command does something a little
> peculiar: every minute or so twelve distinct functions are overwritten
> using CREATE OR REPLACE FUNCTION. Perhaps this is causing the
> postmaster backend to fill its relcache.

[ scratches head ... ] It's hard to see what that would have to do with
the relcache. I was sort of expecting you to come back and say that you
thought the process might have done 640K TRUNCATEs over its lifespan,
but I guess not? What about temporary tables? (Note: you have the list
of tables involved, in the form of that dump you showed --- does
eyeballing the list suggest any pattern to you? Are there duplicate
table names in the list?)

> To test this Joe created a simple C program that does the same operation
> on slightly changed functions (changed only enough so as not to
> interfere with the real process). After running this for a little over
> an hour here is what we see from top:

> PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
> 17033 postgres 16 0 182M 182M 180M R 35.7 4.6 22:33 1 postmaster
> 17032 root 9 0 63056 61M 1336 S 0.5 1.5 0:21 0
> test_function

> The RSS value has been steadily growing the entire time, so I think we
> have our cause.

Um, maybe. On many systems, "top" counts as part of RSS whatever the
process has touched of the postmaster's shared memory pool. So it's
fairly common to see RSS slowly grow to exceed the size of the shared
memory area, as the process happens to touch one or another slot of the
shared buffer pool. Given that you are showing "SHARE" as 180M, I think
it's pretty likely you've got only 2MB of actual local storage in that
backend.

If it's a true leak, then letting it run until RSS is well beyond shared
memory will prove it.

regards, tom lane