Re: Linux server connection process consumes all memory

Hello again, here is an extract of my TOP:

top - 10:38:57 up 95 days, 1:34, 1 user, load average: 0.00, 0.01, 0.05
Tasks: 87 total, 2 running, 85 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.1%us, 0.0%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8070900k total, 2797004k used, 5273896k free, 46532k buffers
Swap: 7811068k total, 5336k used, 7805732k free, 2477508k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
18059 postgres 20 0 2186m 102m 99m S 0 1.3 0:03.19 postgres
17028 postgres 20 0 2180m 61m 60m S 0 0.8 0:08.06 postgres
11631 postgres 20 0 2181m 13m 13m S 0 0.2 0:15.20 postgres

PID 18059 is the connection process. It is already running at 1.3% of memory and its
RES is already 102m. If I leave it run for the rest of the day, it will go up
to 96% of Mem, the physical memory will get down down to 50M (from 5G available at this stage)
and then it will start swapping eventually exhausting even the swap.

Kind Regards
Yiannis

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org on behalf of Ioannis Anagnostopoulos
Sent: Sun 04/12/2011 09:38
To: Tom Lane
Cc: pgsql-novice(at)postgresql(dot)org; ahodgson(at)simkin(dot)ca
Subject: Re: [NOVICE] 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