Обсуждение: Out of Memory on Reindex

I am getting the following error when trying to run a reindex on one of my databases.

reindexdb: reindexing of database "xxx" failed: ERROR:  out of memory
DETAIL:  Failed on request of size 268435456.

Can someone advise on what memory parameter was violated?  Are we looking at work_mem, shmmax, or something else?

Thanks,

Chris

PG 8.1.3

Any ideas?

---------- Forwarded message ----------
From: Chris Hoover <revoohc@gmail.com>
Date: May 29, 2007 11:36 AM
Subject: Out of Memory on Reindex
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>

I am getting the following error when trying to run a reindex on one of my databases.

reindexdb: reindexing of database "xxx" failed: ERROR:  out of memory
DETAIL:  Failed on request of size 268435456.

Can someone advise on what memory parameter was violated?  Are we looking at work_mem, shmmax, or something else?

Thanks,

Chris

PG 8.1.3

Chris Hoover wrote:
> Any ideas?

Drop it and try to recreate it. As far as the parameter it is
maintenance_work_mem but that should spill to disk which means you ran
out of actual memory too.

Joshua D. Drake

P.S. You *need* to upgrade to 8.1.9

>
> ---------- Forwarded message ----------
> From: *Chris Hoover* <revoohc@gmail.com <mailto:revoohc@gmail.com>>
> Date: May 29, 2007 11:36 AM
> Subject: Out of Memory on Reindex
> To: "pgsql-admin@postgresql.org <mailto:pgsql-admin@postgresql.org>"
> <pgsql-admin@postgresql.org <mailto:pgsql-admin@postgresql.org>>
>
> I am getting the following error when trying to run a reindex on one of
> my databases.
>
> reindexdb: reindexing of database "xxx" failed: ERROR:  out of memory
> DETAIL:  Failed on request of size 268435456.
>
> Can someone advise on what memory parameter was violated?  Are we
> looking at work_mem, shmmax, or something else?
>
> Thanks,
>
> Chris
>
> PG 8.1.3
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

"Chris Hoover" <revoohc@gmail.com> writes:
> I am getting the following error when trying to run a reindex on one of my
> databases.

> reindexdb: reindexing of database "xxx" failed: ERROR:  out of memory
> DETAIL:  Failed on request of size 268435456.

> Can someone advise on what memory parameter was violated?

Kernel's process size limit (ulimit -m or something like that).

What have you got maintenance_work_mem set to ... 256MB by any chance?
If so make it less.

            regards, tom lane

maintenance_work_mem = 1048576 (I have 9GB ram on this server).

Chris

"Chris Hoover" <revoohc@gmail.com> writes:
> maintenance_work_mem = 1048576 (I have 9GB ram on this server).

You might have that much RAM, but I wonder how much of it the kernel
will give to any one process.  Did you check the ulimit settings the
postmaster is running under?  Is it possibly a 32-bit rather than
64-bit build of Postgres?

            regards, tom lane

Sorry for not providing that info  Here are the ulimits for my postgres account:
[postgres@dbserver ~]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) unlimited
pending signals                 (-i) 1024
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 159744
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

This is on a 32bit dual HT Xeon system using the official rpms.

Chris

P.S.

Sorry for the direct reply, forgot to hit reply all.

"Chris Hoover" <revoohc@gmail.com> writes:
> This is on a 32bit dual HT Xeon system using the official rpms.

Um.  I suspect you can't usefully set maintenance_work_mem as high as
1Gb in a 32-bit environment.  Last I heard, the max address space
available to userland in 32-bit Linux is 3Gb (the kernel takes the other
Gb).  Out of that you've got to subtract Postgres' shared memory, the
program code itself, and whatever other random internal overhead a
backend has got.  I don't think you mentioned what shared memory
settings you were using, but if you've been equally expansive on
shared_buffers and so on it could easily be approaching 2Gb in shared
memory.

Try knocking maintenance_work_mem back to 512M or 256M and see if things
get better.

            regards, tom lane