Out of memory - Mailing list pgsql-general

Hi,

I've been having repeated troubles trying to get a PostgreSQL app to play nicely on Ubuntu. I recently posted a message
onthis list about an out of memory error and got a resolution by reducing the work_mem setting. However I'm now getting
furtherout of memory issues during the same stage of plpgsql function as mentioned before. 

The function itself is run as part of larger transaction which does the following:

1/ Maintains 104 tables (15 PostGIS tables), by loading and applying incremental table changes. A typical incremental
loadwith maintain about 10,000 rows. 

2/ When each one of these tables is updated an after trigger is fired that maintains an associated table revision
table.

3/ After all of the tables are maintained a plpgsql function is called to build/maintain a set of de-normalised tables.
Thesetables total about 20GB. Each one of these tables is compared against the previous table revision to determine its
rowchanges. It's in this function that the out of memory exception is occurring. 

The server log error message I'm getting in the function is here http://pastebin.com/346zi2sS. It's very long and
containsthe top transaction memory debug info. 

My initial observation about this error is that maybe PostgreSQL is encountering a memory corruption error because the
amountof OS memory does not seem to run out. The plpgsql function uses functions from both PostGIS and pgc_checksum
(http://pgfoundry.org/projects/pg-comparator)- so maybe they are the cause of the problem. Or maybe I have configured
somethingwrong... 

I did some memory logging during and the execution of the function. It shows for the majority of the transaction
executionthat the actual memory used is about 1GB (grows from the initial 600mb) with about 6.5GB cached for the OS: 

             total       used       free     shared    buffers     cached
Mem:          8004       7839        165          0          0       6802
-/+ buffers/cache:       1037       6967
Swap:          397          0        397

But just before the out of memory error occurs there is a spike to 2.5GB of used memory, but there us still 4.5GB
cachedby the OS: 

             total       used       free     shared    buffers     cached
Mem:          8004       7702        301          0          0       4854
-/+ buffers/cache:       2848       5156
Swap:          397          0        397

Then after the error the memory slowly returns this state:

             total       used       free     shared    buffers     cached
Mem:          8004       1478       6526          0          0       1133
-/+ buffers/cache:        345       7659
Swap:          397          0        397

The OS I'm running 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, not much else is running
otherthan cacti, ssh and ftp server daemons. The main OS parameters I have tuned are: 

vm.swappiness=0
vm.overcommit_memory=2
kernel.shmmax = 4196769792
kernel.shmall = 1024602

And the PostgreSQL is:

PostgreSQL 9.0.3 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit.

The main changed postgresql.conf parameters I've tuned are:

shared_buffers = 512MB
maintenance_work_mem = 512MB
temp_buffers = 256MB
work_mem = 1MB
wal_buffers = 16MB
effective_cache_size = 4094MB

The size of the database is 350GB. The typical number of users connected to the database is 1 or 2. This database is
usedfor loading external data, managing revision table information and generating and outputting de-normalised
datasets,so it does not have a high number of transactions running. Typically 1 large one per day. 

Two questions:

1) Have I set the OS and postgresql parameter to sensible values given the hardware and database utilization.
2) Can anyone help me make sense of the top transaction memory error to help track down the issue?

Any other suggestions would be greatly appreciated.

Thanks
Jeremy

________________________________________
From: Jeremy Palmer
Sent: Saturday, 26 March 2011 9:57 p.m.
To: Scott Marlowe
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] Out of memory

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
notreleased until the function completes? Rather then deallocating the memory after each sort operation has completed. 

Thanks,
Jeremy

________________________________________
From: Scott Marlowe [scott.marlowe@gmail.com]
Sent: Friday, 25 March 2011 5:04 p.m.
To: Jeremy Palmer
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Out of memory

On Thu, Mar 24, 2011 at 9:23 PM, Jeremy Palmer <JPalmer@linz.govt.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@linz.govt.nz) and
destroythe original message. 
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________