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
Date: Mon, 11 Apr 2011 16:11:52 -0400
Message-ID: <87fwpo4kfr.fsf@comcast.net> (raw)
In-Reply-To: <09B23E7BF70425478C1330D893A722C603075D2A98@MailSVR.invera.com>
References: <09B23E7BF70425478C1330D893A722C603075D2A98@MailSVR.invera.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gsievers19@comcast.net, alanolya@invera.com
Subject: Re: Out of memory during index creation
In-Reply-To: <87fwpo4kfr.fsf@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox