Re: 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