Re: Is IDLE session really idle?

Thank you guys for your responses.

I'm using top to look for the memory consumption by the postgres processes.
Inside the top, I'm using the interactive sorting command F and choose the
sort by Data segment size. The exact sequence is:
1. launch top
2. Press 'F', the list of available for sorting fields appears on the
screen, including "s: DATA = Data+Stack size (kb)"
3. press "s"
4. Top now shows the list of processes sorted by the field DATA

Here how the sorted top screen looks in the end:

top - 15:14:37 up 1 day, 23:20, 3 users, load average: 5.64, 5.18, 5.10
Tasks: 819 total, 2 running, 817 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.4%us, 0.8%sy, 0.0%ni, 75.4%id, 22.1%wa, 0.0%hi, 0.3%si,
0.0%st
Mem: 32876676k total, 18455704k used, 14420972k free, 129856k buffers
Swap: 2104504k total, 2720k used, 2101784k free, 15038240k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ DATA COMMAND
4392 hyperic 16 0 410m 73m 9672 S 0 0.2 136:17.29 363m java
29487 postgres 16 0 108m 83m 2680 S 0 0.3 0:01.10 81m view
803 postgres 16 0 2300m 180m 163m S 0 0.6 0:01.04 23m postgres
1808 postgres 16 0 2300m 164m 147m S 0 0.5 0:01.03 23m postgres
577 postgres 16 0 2298m 166m 150m S 0 0.5 0:00.87 22m postgres
568 postgres 16 0 2298m 141m 126m S 0 0.4 0:00.63 22m postgres
1506 postgres 16 0 2298m 139m 124m S 0 0.4 0:00.81 22m postgres
362 postgres 16 0 2292m 128m 115m S 0 0.4 0:00.66 16m postgres
7674 postgres 15 0 2288m 29m 20m S 0 0.1 0:00.10 13m postgres
7238 postgres 16 0 2289m 61m 52m S 0 0.2 0:00.23 12m postgres
7440 postgres 16 0 2288m 51m 42m S 0 0.2 0:00.18 12m postgres
7248 postgres 16 0 2288m 52m 44m S 0 0.2 0:00.17 12m postgres
7336 postgres 16 0 2288m 59m 50m S 0 0.2 0:00.20 12m postgres
7246 postgres 16 0 2288m 52m 44m S 0 0.2 0:00.12 12m postgres
6913 postgres 16 0 2288m 59m 51m S 0 0.2 0:00.22 12m postgres
7013 postgres 16 0 2288m 51m 43m S 0 0.2 0:00.10 12m postgres
7288 postgres 16 0 2288m 48m 39m S 0 0.2 0:00.16 12m postgres
7327 postgres 16 0 2288m 53m 44m S 0 0.2 0:00.16 12m postgres
7070 postgres 16 0 2288m 50m 42m S 0 0.2 0:00.16 12m postgres
7543 postgres 15 0 2288m 47m 39m S 0 0.1 0:00.13 11m postgres
........

Also, in vmstat, I see the gradual reduction in size of the cache memory.
Apparently, the Linux cache gets gradually dismissed by the postgres
processes memory areas. Eventually, the database just hangs and the host
becomes unresponsive for about 15 minutes till the sessions die out.
So, I believe it is not just my misinterpretation of the metrics.

Isn't it true, that work memory once allocated for a session does not get
deallocated till the sessions is closed? It was my impression, anyway.
So, eventually enough sessions get big work memory allocated to starve the
Linux out of memory.

My physical memory size is 32GB,
Shared_buffers = 2GB

On 6/15/09 2:13 PM, "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:

> 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.