Re: Is IDLE session really idle? - Mailing list pgsql-admin

On Mon, Jun 15, 2009 at 4:25 PM, Igor Polishchuk<ipolishchuk@hi5.com> wrote:
> 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

Out of 32G, you've got 14G free, 18G used of which 15G is cache.  Looks good.

>  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

VIRT of 2.2G is pretty normal, considering it likely includes all of
shared_buffers ever touched.  Is this machine now at idle?

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

Well, this top doesn't show that.  As long as you've got 14G or so
free the linux kernel won't be discarding cache.

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

Do you have output of top and vmstat when this is happening?

> Isn't it true, that work memory once allocated for a  session does not get
> deallocated till the sessions is closed?

By session do you mean transaction, or the life of the connection?

> It was my impression, anyway.
> So, eventually enough sessions get big work memory allocated to starve the
> Linux out of memory.

But we've seen no evidence of this happening in vmstat or top.  It
would be really handy to see what they're saying when this is
happening.

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