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@xxxxxxxxxxxx>: > 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.marlowe@xxxxxxxxx] > Sent: Friday, 25 March 2011 5:04 p.m. > To: Jeremy Palmer > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: Out of memory > > On Thu, Mar 24, 2011 at 9:23 PM, Jeremy Palmer <JPalmer@xxxxxxxxxxxx> 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@xxxxxxxxxxxx) 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- ------------ pasman -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
