Re: multiple runs of the same query cause out of memory - WAS [Re: capturing/viewing sort_mem utilization on a per query basis]

On Wed, 23 Feb 2005 19:56:56 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Lonni J Friedman <netllama(at)gmail(dot)com> writes:
> > We have found that this query can indeed cause terrible things to
> > happen to postgresql - it can run several times with response times of
> > a few seconds, and then the next time cause postgres to go out of
> > control on memory usage. (I've had to reboot my machine twice to get
> > control). On a beefier machine with more memory and two CPUs, the
> > query worked okay for a few dozen repetitions (with a few in
> > parallel), then eventually one instance would cause the postgres
> > process to spiral out of control and consume more
> > and more memory at the rate of a megabyte every second or so.
>
> > I'm not quite sure where to go from here, but this is definitely
> > reproducable now. Help?!
>
> That's a bit hard to believe --- if nothing is changing, the query
> should get processed the same way every time.

Its definitely not the same every time after this additional testing.

>
> Can you package up a test case for other people to look at?

I could try, but i'm not sure what you'd need. Right now, i can only
replicate this behavior with a snapshot of this one customer's DB.
Running on the same schema, with little or no data, it always
completes fine, so it looks to be partially data driven in nature, or
perhaps its the volume of data.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman netllama(at)gmail(dot)com
LlamaLand http://netllama.linux-sxs.org