Skip site navigation (1) Skip section navigation (2)

Re: Linux server connection process consumes all memory

From: Ioannis Anagnostopoulos <ioannis(at)anatec(dot)com>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-novice(at)postgresql(dot)org, ahodgson(at)simkin(dot)ca
Subject: Re: Linux server connection process consumes all memory
Date: 2011-12-06 09:14:23
Message-ID: 4EDDDCEF.5080903@anatec.com (view raw or flat)
Thread:
Lists: pgsql-novice
On 05/12/2011 21:13, Merlin Moncure wrote:
>
> On Mon, Dec 5, 2011 at 4:41 AM, Ioannis Anagnostopoulos
> <ioannis(at)anatec(dot)com> wrote:
> > Below is the forum discussion I have opened with Devart's ado.net 
> driver and
> > the NPGSQL (opensource) driver.
> >
> > http://www.devart.com/forums/viewtopic.php?p=76192#76192
> > http://pgfoundry.org/forum/forum.php?thread_id=10481&forum_id=519 
> <http://pgfoundry.org/forum/forum.php?thread_id=10481&forum_id=519>
> >
> > As I say there, I managed to reduce my queries to 55.000 from 22 
> million.
> > The server in this scenario is not running out of memory
> > but it is still apparent that memory consumption is high (8.9%!!). 
> So if 20
> > people try to run the same query we are going to be back
> > in square 1. For one more time let me assure that there is ONLY one 
> prepared
> > statement that is created at the beginning and is executed
> > over and over again. My question to postgres people is IF there is 
> any bug
> > that becomes apparent as a memory leak after a lot of executions
> > of the same statement.  My next test will be to convert my .net code to
> > PGSql and execute it on the server without involving any client.
> >
> > The top below show the "updated" query running. This time the load is at
> > 8.9% right at the end of the run. Nowhere near the 96% but please
> > keep in mind that this happened when I reduced to iterations to 55.000.
> >
> > top - 10:35:23 up 96 days,  1:30,  1 user,  load average: 0.00, 
> 0.01, 0.05
> > Tasks:  87 total,   1 running,  86 sleeping,   0 stopped,   0 zombie
> > Cpu(s):  1.5%us,  0.0%sy,  0.0%ni, 98.5%id,  0.0%wa,  0.0%hi,  0.0%si,
> >  0.0%st
> > Mem:   8070900k total,  8017768k used,    53132k free,    56800k buffers
> > Swap:  7811068k total,     4336k used,  7806732k free,  7671980k cached
> >
> >
> >  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
> > 22181 postgres  20   0 2187m 701m 697m S    6  8.9   1:29.33 postgres
>
> You are misinterpreting what top is telling you (it's a pretty common
> mistake).  Most of the memory postgres is using as you can see is
> inside SHR, or is shared memory that is shared between all backend
> processes.
>
Hello Merlin, thank you for the answer. Well the example that you quote 
is from
a sunshine scenario were I had reduced the iteration to something more 
down to earth
and the process concluded. However if you followed the other examples I 
listed in earlier
post you will see cases like this:

top - 11:46:05 up 85 days, 2:41, 1 user, load average: 0.67, 1.03, 1.19
Tasks: 89 total, 1 running, 88 sleeping, 0 stopped, 0 zombie
Cpu(s): 10.3%us, 0.7%sy, 0.0%ni, 69.8%id, 19.2%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8070900k total, 8017232k used, 53668k free, 1508k buffers
Swap: 7811068k total, 4283720k used, 3527348k free, 2088528k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
15965 postgres 20 0 12.9g 6.4g 1.4g S 11 83.4 13:59.15 postgres
15961 postgres 20 0 4285m 1.8g 1.8g D 31 23.2 9:35.03 postgres
15827 postgres 20 0 4276m 52m 51m S 0 0.7 0:00.50 postgres
15830 postgres 20 0 4278m 25m 24m S 0 0.3 0:01.27 postgres
15959 postgres 20 0 4288m 4376 4288 S 0 0.1 0:00.25 postgres
15832 postgres 20 0 4279m 1388 888 S 0 0.0 0:00.19 postgres

This is an extract from top and below is an extract of iostat:

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 158.00 1802.00 1602.00 3604 3204
sdb 70.50 590.00 40.00 1180 80
sdc 16.00 528.00 0.00 1056 0

Please note that process 15965 is my connection process. In that example 
we have
VIRT = 12.9G
RES = 6.4G
SHR=1.4G

And swap of course is almost exhausted. Maybe you are right but in the 
light of this example
can you provide any further explanation?


> What have you set shared_buffers to?  My money is on you having
> oversubscribed your box.
>
> merlin
>
Shared_buffers = 2GB and according to this 
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
I am spot on since my machine has 8G of RAM.

Thank you
Yiannis

In response to

Responses

pgsql-novice by date

Next:From: Merlin MoncureDate: 2011-12-06 17:02:02
Subject: Re: Linux server connection process consumes all memory
Previous:From: Carlo AscaniDate: 2011-12-06 08:25:29
Subject: Re: How to add description for databases and tables?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group