Greg,
I had a similar problem. An application that did lots of complicated queries
constantly running out the server out of ram. I started watching the server
with TOP set to .5 seconds and saw the postmaster swell up to 300+mb for
15seconds at a time!
After several of those queries would run (10 or so) I would notice my ram
not getting released and starting to go up. My initial fix was to increase
the ram to 512mb (from 128), which only prolonged the crash of the server by
a day. Then I installed a second CPU, which helped delay the crash some, but
not as much as the ram.
The end fix was to break up the complicated searches in to many smaller
searches and do a bulk of the real thinking work (JOINs,ANDs,ORs) with PERL.
Currently I server will run for about 14 days, then the used ram gets up to
~ 250MB so I reboot and it starts over at ~ 40MB.
Hope this helps
Jeff
- PS, I spent LOTS of time looking over and over for infinite loops,
Problems/Leaks in the scripts, etc. I found nothing.
> From: "Gregory Krasnow" <[EMAIL PROTECTED]>
> Date: Thu, 11 May 2000 17:58:21 -0700
> To: [EMAIL PROTECTED]
> Subject: RE: [GENERAL] backend running out of memory in v7.0
>
> tables are not that large...
>
>
> essentially we are working on a tool for someone to be able to slice and
> dice their database any which way they
> want. Thus it allows users to create really ugly SQL with a bunch of AND/OR
> joins. I am currently running on
> a box with 128M. I have tried ulimit -s unlimited to unlimit the stack size
> (didn't help), tried setting -B (buffer size to 256 and 1024 to no avail).
>
> - Greg
>
> -----Original Message-----
> From: The Hermit Hacker [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, May 11, 2000 5:01 PM
> To: Gregory Krasnow
> Cc: [EMAIL PROTECTED]
> Subject: RE: [GENERAL] backend running out of memory in v7.0
>
>
>
> okay, well, you ask for a recommended configuration, but how about
> throwing yours out at us, and we can try and suggestion areas where you
> could improve vs doing it all at once? :) btw ... what are the sizes of
> your tables/databases?
>
> On Thu, 11 May 2000, Gregory Krasnow wrote:
>
>> [postgres@warpfactor2 pgsql]$ ulimit -a
>> core file size (blocks) 0
>> data seg size (kbytes) unlimited
>> file size (blocks) unlimited
>> max locked memory (kbytes) unlimited
>> max memory size (kbytes) unlimited
>> open files 1024
>> pipe size (512 bytes) 8
>> stack size (kbytes) 8192
>> cpu time (seconds) unlimited
>> max user processes 2048
>> virtual memory (kbytes) unlimited
>>
>> thanks... I thought of that as well.
>>
>> - Greg
>>
>> -----Original Message-----
>> From: The Hermit Hacker [mailto:[EMAIL PROTECTED]]
>> Sent: Thursday, May 11, 2000 4:32 PM
>> To: Gregory Krasnow
>> Cc: [EMAIL PROTECTED]
>> Subject: Re: [GENERAL] backend running out of memory in v7.0
>>
>>
>> On Thu, 11 May 2000, Gregory Krasnow wrote:
>>
>>> I know that there were some issues with many AND/OR joins in
>>> PostgreSQL 6.5 which caused the backend process to run out of memory.
>>> I am still having some similar issues in PostgreSQL 7.0. I was
>>> wondering if there some recommended configurations (i.e. amount of RAM
>>> on the machine, size of swap partition, buffers settings for backend
>>> process) which I could use to help with my problems. If I cannot
>>> create complex AND/OR queries, I cannot use PostgreSQL for my needs
>>> and will have to turn back to using MySQL which has no problems with
>>> the same queries.
>>
>> Might be even more simple then that ... I've seen the problem here, and
>> each time its cause I ... ummm ... forgot to raise the limits for the
>> pgsql user :( doing a 'unlimit' before starting postmaster fixes my
>> problem each time ...
>>
>> Marc G. Fournier ICQ#7615664 IRC Nick:
>> Scrappy
>> Systems Administrator @ hub.org
>> primary: [EMAIL PROTECTED] secondary:
>> scrappy@{freebsd|postgresql}.org
>>
>
> Marc G. Fournier ICQ#7615664 IRC Nick:
> Scrappy
> Systems Administrator @ hub.org
> primary: [EMAIL PROTECTED] secondary:
> scrappy@{freebsd|postgresql}.org
>
