Re: Linux server connection process consumes all memory

Hello all, and thanks again for the effort. So here are the details:
This is a dual core machine with 8G of Ram running Ubuntu server.
It runs Postgres 9.0 with its Postgis extension. Now it is true that
the linux's OutOfMemory Daemon killer was killing the connection before as
the connection process indeed was consuming the memory. To fight against this
we follow instructions and we reduced to 25% of the machine's physical memory the
shared_buffers (i.e 2GB) while we also set the effective_cache_size to 6GB.
After this we no longer run out of memory but of course we have the issue described
before.

Some background of this database, this is a "readonly" database which contain millions of
rows regarding shipping positions. The main database that receives the data
updates/adds about 15000 rows per minute with a processing time of 1000 lines per 1.16 sec.
Now imagine that the "readonly" database we are working on has monthly data so you can get
an idea of how many rows it contains in its major tables.

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. In case
that you are wondering for the code, you will have the same issue whether or not
the command is prepared or not. In fact the only way to get this issue resolved
is to close and open the connection for every call/instance of this class. But then
of course as I have to query 22million rows my speed degrades (for example this code
returns 1 row every 0.001 sec while If I close and open connection to get rid of the
memory issue it returns 1 row every 0.01 sec.

Thank you in advance
Yiannis

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Sun 04/12/2011 04:45
To: Ioannis Anagnostopoulos
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Linux server connection process consumes all memory

"Ioannis Anagnostopoulos" <ioannis(at)anatec(dot)com> writes:
> This is my first post here and as I wanted to play it safe my first option was the novice list. My problem is as follows:

> I have a simple select command that I need for some reason to execute it around 22million times. In fact it is a prepared command from a .net environment. I have used both ado.net postgres options (the freeware NPGSQL and the commercial from Devart) and the behaviour is the same. What happens is that once I establish the connection with the corresponding connection object, a new process is created on the server as expected. When I start iterating executing the select command as time goes by the memory footprint of the process grows bigger and bigger. Sooner or later the server either collapses or becomes unworkable as the physical memory gets exhausted and the swap is heavily used. This is just a simple select, with a data reader that I make sure to close after each iteration. I find no reason why the connection process on the server gets so out of control. Is it caching data? If yes I don't want to. Do I need to close the connection every so often to free up the memory?

This problem is not resolvable with the amount of information you've
provided. Your description makes it sound like you've run into a
server-internal memory leak. While those are certainly possible, we
don't find one very often, which is why Alan was skeptical about whether
the memory growth was in the server process or not. It seems at least
as likely that you've misdiagnosed what's happening. Also, even if
there is a memory leak, a well-configured system will normally fail with
an "out of memory" error before it gets into the kind of distress that
you're describing.

So at this point I'm wondering which PG version you're running, what
non-default configuration settings you've selected, and how much RAM the
box has got. It might also be useful to see the specific query you're
running and the output that EXPLAIN gives for it.

regards, tom lane