Re: AIX - Out of Memory

Hi Francis,

I did not see my last response so I am trying again.

From some Google-ing:

If you need more memory (larger data segment) for your
Perl programs you can set:

/etc/security/limits
default: (or your user)
data = -1 (default is 262144 * 512 byte)

With the default setting the size is limited to 128MB.
The -1 removes this limit. If the "make test" fails please
change your /etc/security/limits as stated above.

Regards,
Ken

On Tue, Feb 16, 2010 at 04:04:41PM -0000, Thorne, Francis wrote:
>
> 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
>
> LOCALEDIR =
>
> 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/pgxs.mk
>
> CONFIGURE = '--prefix=/usr/local/pgsql837-64' '--with-pgport=5422'
> '--enable-thr
> ead-safety' '--enable-integer-datetimes' 'CC=gcc -maix64'
> 'LDFLAGS=-Wl,-bbigtoc'
> CC = gcc -maix64
>
> CPPFLAGS =
>
> CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
> -Wdeclaration-a
> fter-statement -Wendif-labels -fno-strict-aliasing -fwrapv
>
> CFLAGS_SL =
>
> LDFLAGS = -Wl,-bbigtoc
> -Wl,-blibpath:/usr/local/pgsql837-64/lib:/usr/lib:/lib
> 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
>
> Fran
> -----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
> have
> > > > 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
> __________________________________________________
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>