Re: Out of swap space & memory - Mailing list pgsql-general

From Tom Lane
Subject Re: Out of swap space & memory
Date
Msg-id 22881.1091543979@sss.pgh.pa.us
Whole thread Raw
In response to Out of swap space & memory  ("Kevin Bartz" <kbartz@loyaltymatrix.com>)
List pgsql-general
"Kevin Bartz" <kbartz@loyaltymatrix.com> writes:
> I'm de-duping a 45-million-row table (about 4 gigs) by creating a new table
> with the unique rows of the first table (should be about 3 gigs). I'm on a
> 64-bit SuSE Linux with 2 gigs of memory and another gig of swap space. I
> tracked postmaster's use of memory and swap space after I run the query, and
> I noticed that as soon as postmaster first uses up all available memory and
> swap space, I get this:

> 2004-08-02 19:38:04 LOG:  server process (PID 25302) was terminated by
> signal 9

This is the infamous "out of memory kill" that is perpetrated by some
versions of Linux after the kernel realizes that it has given out memory
it does not have.  Google for "OOM kill" and you'll find info.  It's an
extremely good idea to disable this kernel bug^H^H^Hfeature, as a lot of
the time the process that gets zapped is not the one that was actually
consuming all the RAM, but some innocent bystander.  The first time the
OOM killer takes out your postmaster, your mail daemon, or some other
highly critical process, you'll wish you had turned it off.  (You turn
it off by adjusting kernel settings so that it won't give out more
memory than it has in the first place.)

However, that doesn't really answer your problem, which is why your
query is consuming unreasonable amounts of RAM and what you can do about
it.  What PG version is this, what is the query *exactly*, what does
EXPLAIN show for the query, and what nondefault postgresql.conf settings
are you using?

            regards, tom lane

pgsql-general by date:

Previous
From: Robert Treat
Date:
Subject: Re: [ANNOUNCE] == PostgreSQL Weekly News - August 1st
Next
From: Greg Stark
Date:
Subject: Trying to use GIST indexes again