pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOut of memory during index creation
2+ messages / 2 participants
[nested] [flat]
* Out of memory during index creation
@ 2011-04-11 18:49 Alanoly Andrews <alanolya@invera.com>
0 siblings, 1 reply; 2+ messages in thread
From: Alanoly Andrews @ 2011-04-11 18:49 UTC (permalink / raw)
To: pgsql-admin
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 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.
****************************************************
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Out of memory during index creation
@ 2011-04-11 20:11 Jerry Sievers <gsievers19@comcast.net>
parent: Alanoly Andrews <alanolya@invera.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jerry Sievers @ 2011-04-11 20:11 UTC (permalink / raw)
To: Alanoly Andrews <alanolya@invera.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-04-11 20:11 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-11 18:49 Out of memory during index creation Alanoly Andrews <alanolya@invera.com>
2011-04-11 20:11 ` Jerry Sievers <gsievers19@comcast.net>
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