Igor Polishchuk <ipolishchuk@hi5.com> writes:
> The application often executes relatively big sorts. The work_mem size is
> 32MB, and eventually many sessions have a chance to run a sort and allocate
> a big sort area. I see hundreds of postgres processes with DATA segment >
> 15MB.
> Eventually, it consumes all the available memory. Most of this memory is
> allocated to the sessions that are idle. I cannot change the connection
> pooling on the application side, and the big sorts cannot be eliminated. I
> need a solution on the DB side.
Find out why the workspace isn't being released back to the OS. On some
old versions of Unix, not releasing data space is the norm, but that is
not true on any remotely modern version of Linux.
> In case it makes a difference: The Postgres version is 8.2.4 on Linux Suse
> 10.
... which apparently is what you are running, which makes me question
whether you are really seeing any such effect at all. Are you sure what
you are measuring isn't just top's inclination to report only a portion
of shared memory as having been used by the process?
I strongly suspect that what you are seeing is an illusion caused by
your measurement tools.
regards, tom lane