From: Jerry Sievers <gsievers19@comcast.net>
To: Alanoly Andrews <alanolya@invera.com>
Cc: "'pgsql-admin\@postgresql.org'" <pgsql-admin@postgresql.org>
Subject: Re: Out of memory during index creation
References: <09B23E7BF70425478C1330D893A722C603075D2A98@MailSVR.invera.com>
Date: Mon, 11 Apr 2011 16:11:52 -0400
In-Reply-To: <09B23E7BF70425478C1330D893A722C603075D2A98@MailSVR.invera.com>
	(Alanoly Andrews's message of "Mon, 11 Apr 2011 14:49:33 -0400")
Message-ID: <87fwpo4kfr.fsf@comcast.net>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Alanoly Andrews <alanolya@invera.com> writes:

> Hello,
>
> PG version 8.4.7 on AIX 6.1.
>
> While creating a large multi-column index on a table of about 2.5 million rows, I got the following error:
>
>    ERROR:  out of memory
>
>      DETAIL:  Failed on request of size 50331648.
>
> I doubled the value of the shared_buffers parameter (from 512Mb to 1024Mb), recycled the PG instance and then was able to
> run the create index statement successfully.
>
> Question: Does PG use a main memory sort algorithm for the creation of
> indexes? Can it not make do with whatever memory is available and
> supplement it with disk space (external sort)? I have a temporaray

It can and does but for sorting too large to be done in
maintenance_work_mem.

If your box doesn't have enough virtual mem to support shared_buffers,
work_mem on behalf of all live backends, whatever the OS needs... you'll
get an error like that while Pg is trying to alloc for the
maintenance_work_mem chunk.

Inspect your logs for more cases of that error since autovac can run
into the same problem.

HTH

> tablespace defined (with the temp_tablespaces) parameter. The disk
> area for this tablespace had about 2GB of free space available. But
> the sort does not seem to have used it.
>
> Thanks.
>
> Alanoly Andrews.
>
> ------------------------------------------------------------------------------------------------------------------------------
> This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any
> distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is
> unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.
>
> Ce courriel est confidentiel et prot?g?. L'exp?diteur ne renonce pas aux droits et obligations qui s'y rapportent. Toute
> diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les)
> destinataire(s) d?sign?(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser imm?diatement, par
> retour de courriel ou par un autre moyen.
>
> Mail sent via the Abaca EPG
> ------------------------------------------------------------------------------------------------------------------------------
>

-- 
Jerry Sievers
e: gsievers19@comcast.net
p: 305.321.1144