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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-novice(at)postgresql(dot)org, ahodgson(at)simkin(dot)ca
Subject: Re: Linux server connection process consumes all memory
Date: 2011-12-05 10:41:36
Message-ID: 4EDC9FE0.8030506@anatec.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

Kind Regards
Yiannis

On 04/12/2011 16:30, Tom Lane wrote:
> "Ioannis Anagnostopoulos"<ioannis(at)anatec(dot)com>  writes:
>> I am not sure if I can but I will try to attach my server's configuration for
>> you to take a look. I am also attaching the vb.net code that is
>> executed.
> I don't know much of anything about VB, but I am filled with suspicion
> that something in the VB infrastructure you're using is not doing what
> you think.  In particular, I'm betting that repeated executions of this
> code are in fact generating new prepared statements without deallocating
> old ones.  You could investigate that theory by setting log_statement =
> all in the server configuration and then watching the server log to see
> exactly what SQL commands are actually getting sent.
>
> 			regards, tom lane
>


In response to

Responses

pgsql-novice by date

Next:From: =?iso-8859-1?B?Sm9z6SBGZXJt7W4gRnJhbmNpc2NvIEZlcnJlcmFz?=Date: 2011-12-05 12:38:30
Subject: RE: problema con comando \c en el psql
Previous:From: Lisibeth TroconisDate: 2011-12-05 03:32:37
Subject: Re: problema con comando \c en el psql

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