Re: High memory usage

"Rainer Mager" <rmager@vgkk.com> writes:
>     First of all we see some of the PG processes use as much as 80 MB
> (according to top and ps). This seems to be allocated during a query
> (SELECT). Unfortunately, we haven't been able to discover exactly what query
> is doing this.

If you can catch the thing while the memory usage is going up, you could
attach to the backend with gdb and examine debug_query_string to see
what the current query is.  (Note: I think you need 7.1.1 or later to
have this variable available.  You should be on 7.1.2 in any case, just
on general principles.)  Otherwise, consider turning on query logging
and see if you can correlate log entries with the memory consumption.

> The second significant problem is that this memory allocation does not go
> down once the SELECT is completed. According to ps the process in IDLE yet
> the memory usage remains the same.

Very few Unix programs of any description will release memory back to
the OS before process exit.  Postgres is no different.  Start a fresh
backend if you need to cut down the memory usage.

There's not a lot more we can say about it until you can identify the
query that sucks up all the space.  7.1 in general is a lot better about
memory consumption than prior releases, but it sounds like you may have
found a leak that's not plugged yet.  I would like to know about it when
you identify the problem query.

            regards, tom lane