Re: Server side backend permanent session memory usage ?

"Day, David" <dday(at)redcom(dot)com> writes:
> There seems to be an unbounded growth of memory usage by the backend postgres process representing a "permanent" session in our system.

It's hard to evaluate this report with so little information, but there
are at least three possible explanations:

1. Many people misunderstand "top"'s output and believe they are seeing
memory bloat when they aren't. This happens because top only charges
pages of shared memory to a process after the process has first physically
touched those pages. So a PG process's claimed use of shared memory will
gradually grow from nothing to the whole shared-memory area, as it has
occasion to make use of different shared buffers, lock table entries, etc.
You can correct for this by subtracting the SHR (shared) column from
the process's reported size, but people often fail to. (Note: it's
possible that FreeBSD's implementation doesn't suffer from this problem,
but the issue definitely exists on e.g. Linux.)

2. If, over time, the queries issued to the process touch many different
tables (I'm talking thousands of tables), or execute large numbers of
distinct plpgsql functions, etc, then you will get bloating of the
internal caches that hold copies of that catalog data. PG generally
operates on the principle that cached is better than not cached, so it
doesn't try to limit the size of those caches; but in some installations
that can cause problems. If this is your situation, then indeed
restarting the sessions periodically may be necessary.

3. Or you might have found an actual memory leak. PG's memory usage
conventions are such that true leaks that persist across transactions
are pretty rare ... but I won't say it doesn't happen.

If you've eliminated point 1 and want to try to look into the other
theories, you could do this: attach to a recently-started session with
gdb, and execute
call MemoryContextStats(TopMemoryContext)
quit
This will cause a memory usage map to get dumped to stderr (hopefully
you are starting the postmaster in such a way that that gets captured
to a log file rather than sent to /dev/null). Save that. Wait until
you see bloat, reattach and repeat, compare the memory maps. Let us
know what you see. If possible, compare maps taken at points where
the session is idle and waiting for input.

regards, tom lane