Re: One source of constant annoyance identified - Mailing list pgsql-general
From | Markus Wollny |
---|---|
Subject | Re: One source of constant annoyance identified |
Date | |
Msg-id | 2266D0630E43BB4290742247C8910575014CE2AC@dozer.computec.de Whole thread Raw |
In response to | One source of constant annoyance identified ("Markus Wollny" <Markus.Wollny@computec.de>) |
List | pgsql-general |
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@sss.pgh.pa.us] > Gesendet: Donnerstag, 27. Juni 2002 16:41 > An: Markus Wollny > Cc: pgsql-general@postgresql.org > Betreff: Re: [GENERAL] One source of constant annoyance identified > > > "Markus Wollny" <Markus.Wollny@computec.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 >
pgsql-general by date: