Re: Linux server connection process consumes all memory

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
>