On Thu, 21 Mar 2002, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > On Thu, Mar 21, 2002 at 02:10:08PM +0000, Nigel J. Andrews wrote:
> >> (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.
>
> Yeah, the number reported by ps should be viewed with suspicion until
> you know for certain whether it counts the shared memory segment or not.
> (In my experience, on some platforms it does and on some it doesn't.)
That I considered somewhat immaterial since the process was using the
memory. That's what normally killed the process, using up all available memory
including swap and still requiring more.
> However, I think the real issue here is probably just 6.5's well known
> problems with intra-query memory leaks. If Nigel can reproduce the
> difficulty on 7.2 then I'd be more interested in looking into it...
Good point, I hadn't tried it since the upgrade becuase that wasn't why I
upgraded (don't worry I've got a _long_ post on that subject waiting to be
sent), I tightened up limits for the generation of the SQL string in the
application before then. However, I have just tried it with 7 poster_names
listed and top never reported even 8MB for the postgres footprint. I won't give
the EXPLAIN output because it's not interesting and it would almost be an
overlap with the contents of my long, pending post.
FWIW, the table has >1 million rows and the list of names I just gave the query
includes some of the highest volume posters, including the top one with 55,000
rows in the table. There is an index on the poster_name and one on the time
columns.
Thanks for the comments, I didn't even know about the 6.5 memory leak.
Nigel Andrews
Logictree Systems Limited