From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | "David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Out of memory in create index |
Date: | 2008-10-13 10:44:54 |
Message-ID: | 87skr0rcyh.fsf@oxford.xeocode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com> writes:
> create index val_datestamp_idx on vals(datestamp) tablespace space2;
>
> About 30 seconds into the query, I get:
> ERROR: out of memory
> DETAIL: Failed on request of size 536870912.
>
> Increasing maintenance_work_mem from 1GB to 2GB changed nothing at
> all- exact same error at exact same time. Watching memory on the
> machine shows the out of memory error happens when the machine is only
> at about 35% user. create index concurrently shows an identical error.
Try *lowering* maintenance_work_mem. That's how much memory you're telling the
index build to use. Evidently your machine doesn't have enough RAM/swap to
handle 1G of temporary sort space. In practice values over a few hundred megs
don't seem to help much anyways. Try 512M or 256M.
Also, a little known fact is that an index build can actually allocate
maintenance_work_mem plus an extra work_mem. So if you have work_mem set
unreasonably high that could be contributing to the problem.
> Actually, while I was writing this, I added an additional column to
> the index and it now appears to be completing (memory has reached
> about the point it had been failing at and is now holding steady, and
> the query has been going for significantly longer than the 30 seconds
> or so it took to error out previously). I sort by both columns at
> times, so the extra column may in fact turn out to be useful, but the
> failure of the single column create index in the face of the other
> successful creates has me confused. Can anyone shed some light on the
> situation?
How much memory the OS allows Postgres to allocate will depend on a lot of
external factors. At a guess you had some other services or queries running at
the same time the first time which reduced the available memory.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Smith | 2008-10-13 10:49:12 | Re: Improve dump and restore time |
Previous Message | Stuart Bishop | 2008-10-13 10:43:36 | Re: [Slony1-general] Re: Stripping out slony after / before / during pg_restore? |