Re: Linux server connection process consumes all memory

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

What have you set shared_buffers to? My money is on you having
oversubscribed your box.

merlin