Re: Postmaster processes running out of control?

On Thu, Mar 21, 2002 at 02:10:08PM +0000, Nigel J. Andrews wrote:
>
> On Thu, 21 Mar 2002, Martijn van Oosterhout wrote:
>
> > 90MB per process? wow. Can you look in the server logs to see which query is
> > taking all the time?
> >
>
> I can't help with the problem but is 90MB such a shock? I can get towards that
> just by running something like:

We have tables here running into the hundreds of megabytes and if a backend
process goes over 40MB, it generally means a bug in the query.

> SELECT * FROM big_table
> WHERE time > 'sometime'
> AND time < 'someothertime'
> AND name IN ('first', 'second', 'third', 'fourth', 'fifth')
> ORDER BY time
>
>
> Indeed I got blase about running such a thing and rather than the backend
> instance dying the last time it froze my kernel. I haven't done it again.

That basically means you gave the backend enough memory to hang the machine.
I think that means you went too far.

Given the above query, do you have any form of index on "time"? How big is
your table? If the backend process is using a lot of memory, that's
generally an indication that it's doing a sort on a large resultset. An
index can solve that problem.

From here it becomes hard to suggest anything without an indication of the
EXPLAIN output of a query. Sometimes with proper jiggling, a 10 second query
can become a sub-second one.

> BTW, the killer bit was the fifth name, up to that point things got large
> but stayed within capabilities of the machine. I tried all I could think
> of to get limits applied to the backend processes (short of editing and
> recompiling from source) but nothing worked. There wasn't any change when
> switching from a IN test to a string of ORs.

I beleive the parser converts the IN to ORs anyway.

> (6.5.1 I think postgres, since upgraded to 7.2 on FreeBSD 3.3-STABLE)
>
> Why am I saying this? No idea. Just not sure why a 90MB footprint for a DB
> backend would be so shocking.

I think as someone else pointed out, it's probably all shared memory any and
so may not be a problem. That doesn't solve your basic problem though.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Ignorance continues to thrive when intelligent people choose to do
> nothing. Speaking out against censorship and ignorance is the imperative
> of all intelligent people.