Re: Out of memory

Hi. Your idea is cool - i think this feature ought to be added to
TODO. Sorted rows should be materialized when memory is exhaused, and
memory reused.

2011/3/26, Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz>:
> Hi Scott,
>
> It was the work_mem that was set too high. I reduced it to 32mb and the
> function executed.
>
> Just so I understand this. Every time a sort is performed within a function,
> the sort memory is allocated, and then it not released until the function
> completes? Rather then deallocating the memory after each sort operation has
> completed.
>
> Thanks,
> Jeremy
>
> ________________________________________
> From: Scott Marlowe [scott(dot)marlowe(at)gmail(dot)com]
> Sent: Friday, 25 March 2011 5:04 p.m.
> To: Jeremy Palmer
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Out of memory
>
> On Thu, Mar 24, 2011 at 9:23 PM, Jeremy Palmer <JPalmer(at)linz(dot)govt(dot)nz> wrote:
>> I’ve been getting database out of memory failures with some queries which
>> deal with a reasonable amount of data.
>>
>> I was wondering what I should be looking at to stop this from happening.
>>
>> The typical messages I been getting are like this:
>> http://pastebin.com/Jxfu3nYm
>> The OS is:
>>
>> Linux TSTLHAPP01 2.6.32-29-server #58-Ubuntu SMP Fri Feb 11 21:06:51 UTC
>> 2011 x86_64 GNU/Linux.
>>
>> It’s a running on VMWare and, has 2 CPU’s and 8GB of RAM. This VM is
>> dedicated to PostgreSQL. The main OS parameters I have tuned are:
>>
>> work_mem = 200MB
>
> That's a really big work_mem. I have mainline db servers with 128G of
> ram that have work_mem set to 16M and that is still considered a
> little high in my book. If you drop work_mem down to 1MB does the out
> of memory go away? work_mem is how much memory EACH sort can use on
> its own, if you have a plpgsql procedure that keeps running query
> after query, it could use a LOT of memory really fast.
> ______________________________________________________________________________________________________
>
> This message contains information, which is confidential and may be subject
> to legal privilege.
> If you are not the intended recipient, you must not peruse, use,
> disseminate, distribute or copy this message.
> If you have received this message in error, please notify us immediately
> (Phone 0800 665 463 or info(at)linz(dot)govt(dot)nz) and destroy the original message.
> LINZ accepts no responsibility for changes to this email, or for any
> attachments, after its transmission from LINZ.
>
> Thank you.
> ______________________________________________________________________________________________________
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
------------
pasman