Re: Is IDLE session really idle?

On Mon, Jun 15, 2009 at 2:15 PM, Igor Polishchuk<ipolishchuk(at)hi5(dot)com> wrote:
> Hello everybody!
>
> I have many app servers using connection pooling. At any time, there are
> about 1000 total connection to the database from all the app servers;
>  however, only few random connections are active.
> 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.

Could you post the commands and output you used to determine this? I
agree with Tom that you might be mis-measuring memory usage.

You do not mention your shared_memory size or total memory size. If
you've got say 16G of ram and 12 Gig of shared_memory, then it's quite
possible having touched all of shared_memory a pgsql backend will show
10 or 12 Gigs memory used. It's not actually independently using that
much, but some people freak when they see it the first time.