468,136 Members | 1,435 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,136 developers. It's quick & easy.

Out of swap space & memory

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:35:52 LOG: checkpoint record is at 7/4AA2F590
2004-08-02 19:35:52 LOG: redo record is at 7/4AA2F590; undo record is at
0/0; shutdown TRUE
2004-08-02 19:35:52 LOG: next transaction ID: 740; next OID: 116212470
2004-08-02 19:35:52 LOG: database system is ready
2004-08-02 19:38:04 LOG: server process (PID 25302) was terminated by
signal 9
2004-08-02 19:38:04 LOG: terminating any other active server processes
2004-08-02 19:38:04 LOG: all server processes terminated; reinitializing
2004-08-02 19:38:04 FATAL: the database system is starting up
2004-08-02 19:38:04 LOG: database system was interrupted at 2004-08-02
19:35:52 PDT
2004-08-02 19:38:05 LOG: checkpoint record is at 7/4AA2F590
2004-08-02 19:38:05 LOG: redo record is at 7/4AA2F590; undo record is at
0/0; shutdown TRUE
2004-08-02 19:38:05 LOG: next transaction ID: 740; next OID: 116212470
2004-08-02 19:38:05 LOG: database system was not properly shut down;
automatic recovery in progress
2004-08-02 19:38:05 LOG: redo starts at 7/4AA2F5D8
2004-08-02 19:38:05 LOG: record with zero length at 7/4AA4D5C0
2004-08-02 19:38:05 LOG: redo done at 7/4AA4B560
2004-08-02 19:38:07 LOG: database system is ready

And the query never completes. This happens reproducibly and consistently. I
like to think that two gigs should be enough memory. What is killing the
postmaster process and how can I stop it? And, more importantly, is there
any way I can run this query? Thanks for any help you can provide,

Kevin
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
3 2351
What version of Postgresql are you running?

I believe this was a problem in 7.4.1 but fixed by 7.4.3 if I remember
correctly.

Mike

On Mon, 2004-08-02 at 22:08, Kevin Bartz wrote:
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:35:52 LOG: checkpoint record is at 7/4AA2F590
2004-08-02 19:35:52 LOG: redo record is at 7/4AA2F590; undo record is at
0/0; shutdown TRUE
2004-08-02 19:35:52 LOG: next transaction ID: 740; next OID: 116212470
2004-08-02 19:35:52 LOG: database system is ready
2004-08-02 19:38:04 LOG: server process (PID 25302) was terminated by
signal 9
2004-08-02 19:38:04 LOG: terminating any other active server processes
2004-08-02 19:38:04 LOG: all server processes terminated; reinitializing
2004-08-02 19:38:04 FATAL: the database system is starting up
2004-08-02 19:38:04 LOG: database system was interrupted at 2004-08-02
19:35:52 PDT
2004-08-02 19:38:05 LOG: checkpoint record is at 7/4AA2F590
2004-08-02 19:38:05 LOG: redo record is at 7/4AA2F590; undo record is at
0/0; shutdown TRUE
2004-08-02 19:38:05 LOG: next transaction ID: 740; next OID: 116212470
2004-08-02 19:38:05 LOG: database system was not properly shut down;
automatic recovery in progress
2004-08-02 19:38:05 LOG: redo starts at 7/4AA2F5D8
2004-08-02 19:38:05 LOG: record with zero length at 7/4AA4D5C0
2004-08-02 19:38:05 LOG: redo done at 7/4AA4B560
2004-08-02 19:38:07 LOG: database system is ready

And the query never completes. This happens reproducibly and consistently. I
like to think that two gigs should be enough memory. What is killing the
postmaster process and how can I stop it? And, more importantly, is there
any way I can run this query? Thanks for any help you can provide,

Kevin
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2
On Mon, 2 Aug 2004 20:08:45 -0700, "Kevin Bartz"
<kb****@loyaltymatrix.com> wrote:
is there any way I can run this query?


What query? You didn't show us your SQL.

Servus
Manfred

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3
"Kevin Bartz" <kb****@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by lebo | last post: by
3 posts views Thread by ReGenesis0 | last post: by
2 posts views Thread by news.onet.pl | last post: by
2 posts views Thread by cnprasad | last post: by
14 posts views Thread by Otto Meijer | last post: by
reply views Thread by Kevin Bartz | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.