Re: One source of constant annoyance identified

Hi!

I see - so the beast is still roaming, but the current settings sort of
keep it at bay for a while...

I'd surely like to find the specific query/queries which cause this
behaviour; all I could find out as yet was the database for the query.
If I switch on query_stats and query-output in the logfile, I get about
300-500MB log per hour - even during low-load times. Scanning through
these logs is like searching for a needle in a haystack. I can indeed
identify those queries with exceptionally high execution times, but
there's no mentioning of memory-usage anywhere - so I'm still left in
the dark. I can get the PIDs from top-output and scan through the log,
taking down execution times, but then there's nothing much out of the
ordinary - mostly below 0.05 seconds or much, much less. Again I cannot
guess about any correlation between execution time and memory usage. As
one backend processes lots of queries in sequence, I cannot find it hard
to imagine that each and every one of these queries isn't in any way
extraordinary, but there might be some issue with freeing up memory that
belonged to the predecessor, when the backend begins with a new query,
thus grabbing more and more memory during its lifetime.

With the current tools I know of, I shall have a hard time finding the
error - if I ever do. I cannot claim however to really know a lot :) How
would you, being "real DBAs" and knowing the intestines of this
particular DB probably much better than your very own, go about with
this task of tracking down the memory-eater?

Regards,

Markus

> -----Ursprüngliche Nachricht-----
> Von: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Gesendet: Donnerstag, 27. Juni 2002 16:41
> An: Markus Wollny
> Cc: pgsql-general(at)postgresql(dot)org
> Betreff: Re: [GENERAL] One source of constant annoyance identified
>
>
> "Markus Wollny" <Markus(dot)Wollny(at)computec(dot)de> writes:
> > [ turned off persistent connections ]
>
> > Now there's still the odd 250MB backend lingering around
> for some time,
> > but it's not four or five of them any more, wich is a big gain when
> > there's 250MB swap around more often than not.
>
> So you haven't really solved the problem --- somewhere there
> is a query
> being issued that ramps the backend up to a lot of memory. All you've
> done is ensured that the backend won't hang around very long. The
> persistent connection isn't really at fault, except in that it causes
> backends to keep being used after their memory usage has
> become bloated.
>
> Although this might be enough to eliminate your immediate performance
> problem, you should keep looking. Starting lots more
> backends than you
> need to is a performance hit, so turning off persistent connections is
> really only a stopgap not a desirable answer. And I'm still wondering
> whether you've exposed a fixable memory leak bug. We need to identify
> exactly what query is causing the backends to eat memory.
>
> regards, tom lane
>