Skip site navigation (1) Skip section navigation (2)

Re: Is my database now too big?

From: Darren Reed <darrenr(at)fastmail(dot)net>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: Darren Reed <darrenr+postgres(at)fastmail(dot)net>, "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Is my database now too big?
Date: 2007-10-07 09:41:48
Message-ID: 4708A9DC.5060306@fastmail.net (view raw or flat)
Thread:
Lists: pgsql-admin
Scott Marlowe wrote:
> ...
>
> Any reasonably modern version of pgsql should simply stop accepting
> requests rather than suffering loss due to txid wraparound.So,I can
> think of two possibilities here. Bad hardware or operator error.
>
> Assuming you've checked out your machine thoroughly for bad hardware,
> I can see a scenario where one does something like:
>
> begin;
> create table xyz;
> load 10,000,000 rows
> manipulate rows
> shutdown db without committing
> start database
> voila, table xyz is gone, and rightly so.
>
> Got more detailed info on what you're doing?

That does describe what was happening (I haven't used BEGIN/COMMIT.)

Nothing very special, I thought...

But, doing "SELECT * FROM ifl LIMIT 1;" causes postgres to grow its
process to 2GB and then die because the OS ran out of swap!

Actually, this is a table that sees a lot of INSERT/DELETE (it's a place to
store work to be done and bits get removed when completed) and I haven't
been using BEGIN/COMMIT.  This is how postgres currently handles it:

LOG:  database system was not properly shut down; automatic recovery in 
progress
LOG:  record with zero length at 0/891157C8
LOG:  redo is not required
LOG:  database system is ready
LOG:  transaction ID wrap limit is 2147484146, limited by database 
"postgres"
LOG:  unexpected EOF on client connection
LOG:  server process (PID 7212) was terminated by signal 9
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back 
the current transaction and exit, because another server process exited 
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and 
repeat your command.
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back 
the current transaction and exit, because another server process exited 
abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and 
repeat your command.
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted at 2007-10-07 02:22:05 PDT
LOG:  checkpoint record is at 0/891157C8
LOG:  redo record is at 0/891157C8; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 2715; next OID: 24576
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system was not properly shut down; automatic recovery in 
progress
LOG:  record with zero length at 0/8911580C
LOG:  redo is not required
LOG:  database system is ready
LOG:  transaction ID wrap limit is 2147484146, limited by database 
"postgres"

I'm modifying the work to use BEGIN/COMMIT, but the ifl table worries me...
I can't seem to do anything with it that doesn't cause postgres to crap 
out ;(

Darren


In response to

Responses

pgsql-admin by date

Next:From: Scott MarloweDate: 2007-10-07 14:13:07
Subject: Re: Is my database now too big?
Previous:From: Scott MarloweDate: 2007-10-07 08:14:10
Subject: Re: Is my database now too big?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group