Re: Postmaster processes running out of control?

On Thu, 21 Mar 2002, Martijn van Oosterhout wrote:

> On Wed, Mar 20, 2002 at 06:04:17PM -0500, Joshua Hoover wrote:
> > I'm running PostgreSQL 7.1.3 on Red Hat Linux 7.1 and believe there is a
> > problem with my PostgreSQL server.  I have a PHP application on a separate
> > server accessing the PostgreSQL server.  The PostgreSQL server seems to be
> > getting hammered, as even simple queries on indexed columns are taking
> > FOREVER.  When I run top, here I normally see at least 50 entries similar to
> > these for postmaster:
> >
> > 19336 postgres   9   0 92960  90M 92028 S     0.0  9.0   0:18 postmaster
> > 19341 postgres   9   0 87996  85M 87140 S     0.0  8.5   0:09 postmaster
> > 19355 postgres   9   0 87984  85M 87112 S    11.6  8.5   0:09 postmaster
> > 19337 postgres   9   0 87952  85M 87092 S     0.0  8.5   0:09 postmaster
>
> 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:

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.

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.

(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.


Nigel J.Andrews
Logictree Systems Limited