Skip site navigation (1) Skip section navigation (2)

Re: AIX - Out of Memory

From: "Thorne, Francis" <thornef(at)cromwell(dot)co(dot)uk>
To: "Brad Nicholson" <bnichols(at)ca(dot)afilias(dot)info>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: AIX - Out of Memory
Date: 2010-02-16 16:04:41
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
Thanks for the prompt reply, if I'm totally honest I'm not 100% sure the
postgres install has built correctly into a 64-bit build.  I found it
really difficult to find any documentation on how to configure Postgres
correctly for a 64-bit install onto an AIX system.  The only pieces of
information I found where from this forum and off the back of that I
configured postgres with the following settings.

BINDIR = /usr/local/pgsql837-64/bin

DOCDIR = /usr/local/pgsql837-64/doc

INCLUDEDIR = /usr/local/pgsql837-64/include

PKGINCLUDEDIR = /usr/local/pgsql837-64/include

INCLUDEDIR-SERVER = /usr/local/pgsql837-64/include/server

LIBDIR = /usr/local/pgsql837-64/lib

PKGLIBDIR = /usr/local/pgsql837-64/lib


MANDIR = /usr/local/pgsql837-64/man

SHAREDIR = /usr/local/pgsql837-64/share

SYSCONFDIR = /usr/local/pgsql837-64/etc

PGXS = /usr/local/pgsql837-64/lib/pgxs/src/makefiles/

CONFIGURE = '--prefix=/usr/local/pgsql837-64' '--with-pgport=5422'
ead-safety' '--enable-integer-datetimes' 'CC=gcc -maix64'
CC = gcc -maix64


CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
fter-statement -Wendif-labels -fno-strict-aliasing -fwrapv


LDFLAGS = -Wl,-bbigtoc
LDFLAGS_SL = -Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE

LIBS = -lpgport -lz -lreadline -lld -lm

VERSION = PostgreSQL 8.3.7   

After this I configured the ulimit for the postgres user to the
following so that it had unlimited memory access 

core file size        (blocks, -c) 1048575   
data seg size         (kbytes, -d) 131072    
file size             (blocks, -f) unlimited 
max memory size       (kbytes, -m) unlimited 
open files                    (-n) 2000      
pipe size          (512 bytes, -p) 64        
stack size            (kbytes, -s) 32768     
cpu time             (seconds, -t) unlimited 
max user processes            (-u) 2048      
virtual memory        (kbytes, -v) unlimited  

The version of postgres that we are running is 8.3.7.  If you can see an
obvious step that I have missed out or something I haven't configured
incorrectly I would be grateful if you could let me know. Our install
isnt really using temp tables as far as I can see

Thanks again for all your help

-----Original Message-----
From: Brad Nicholson [mailto:bnichols(at)ca(dot)afilias(dot)info] 
Sent: 16 February 2010 15:21
To: Kenneth Marshall
Cc: Tom Lane; Thorne, Francis; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] AIX - Out of Memory

On Mon, 2010-02-15 at 10:18 -0600, Kenneth Marshall wrote:
> On Mon, Feb 15, 2010 at 10:57:06AM -0500, Tom Lane wrote:
> > "Thorne, Francis" <thornef(at)cromwell(dot)co(dot)uk> writes:
> > > Looking for some help with regards to an 'Out of Memory' issue I
> > > with our Postgresql install on AIX.  When running large updates or
> > > select queries we get an out of memory error returned and details
> > > entered in the log file like below.  This is a 64-bit install and
I have
> > > set the ulimit for the postgres user to unlimited.  
> > 
> > The bloat seems to be here:
> > 
> > >     AfterTriggerEvents: 131063808 total in 26 blocks; 576 free (7
> > > chunks); 131063232 used
> > 
> > but it's hard to believe you'd be getting "out of memory" after only
> > 130MB in a 64-bit build.  Are you *sure* the postgres executable is
> > 64-bit?  Are you *sure* the postmaster has been launched with
> > nonrestrictive ulimit?  On lots of setups that takes modifying the
> > PG startup script, not just fooling with some user's .profile.
> > 
> > > This is a 64-bit install (8.3) on AIX 5.3
> > 
> > 8.3.what?
> > 
> > 			regards, tom lane
> I no longer have an AIX box, but I had similar problems with other
> applications that needed large amounts of memory. Some OS specific
> steps needed to be taken to allow normal users to allocate large
> blocks of memory. The information needed was in their on-line docs
> as I recall, but I do not remember the details. The executables may
> need to be built with specific options/flags to work.

AIX has other limits besides the ulimit, there for security purposes I
believe.  2GB per process is the default.

To OP - what is the size of the postgres process?
Are you using temp tables heavily or frequently in a given session?

I've seen the same issue on AIX before (64 bit build) reporting out of
memory on trivial request size.  The issue was due to the use of temp
tables in a single session.  PG does not release the memory for the temp
tables until the session ends.  The postgres process grows up the the
2GB mark, then the final trivial request pushes it over the tipping
point, and you get the out of memory error.

Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

This email is intended for the named recipient. The information contained 
in it is confidential.  You should not copy it for any purposes, nor 
disclose its contents to any other party.  If you received this email 
in error, please notify the sender immediately via email, and delete it from
your computer. 
Any views or opinions presented are solely those of the author and do not 
necessarily represent those of the company. 
Cromwell Tools Limited, PO Box 14, 65 Chartwell Drive
Wigston, Leicester LE18 1AT. Tel 0116 2888000
Registered in England and Wales, Reg No 00986161
VAT GB 115 5713 87 900


pgsql-admin by date

Next:From: Kenneth MarshallDate: 2010-02-16 16:33:09
Subject: Re: AIX - Out of Memory
Previous:From: Brad NicholsonDate: 2010-02-16 15:20:45
Subject: Re: AIX - Out of Memory

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group