Re: Out of memory during index creation

Alanoly Andrews <alanolya(at)invera(dot)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(at)comcast(dot)net
p: 305.321.1144