Re: Is my database now too big?

Scott Marlowe wrote:
> On 10/7/07, Darren Reed <darrenr(at)fastmail(dot)net> wrote:
> > 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.)
>
> then it isn't the same thing. If you did a begin, then did everything
> else without commit, the table would rightly disappear.
>

Right, I'm with you on that.
A few days ago I did:
pg_dumpall > foo
What I was doing yesterday was:
rm -rf /data/db/*
initdb -D /data/db
start
psql < foo
run for some period
stop
reboot
start
...tables have gone but disk space is still in use.
I dont know if it was during the period of running that the
database got corrupted (interrupted insert/update/query?)
or what happened.

> > 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!
>
> I doubt that exact query is causing the db to run out of memory,
> unless ifl is a complex view or something.
>
> Can you be more specific on what exact query causes the problem to show up?
>

It turned out that _any_ query on that table caused the problem to show up.

I couldn't even do "DROP TABLE ifl;" without postgres growing until it
ran out of memory.

So in the end, I wiped it clean and reloaded the data - this time
bounding all of the
work with BEGIN/COMMIT. So far things are looking better. All of the
data I've
been building the tables with is elsewhere, so I can reconstruct it.
Maybe adding
BEGIN/COMMIT makes no difference to not using them before, but I'm curious
to see if it does. Ideally I'd like to get to a place where I don't
need to use vacuum
at all.

> > 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
>
> Looks like some query is running the server out of memory. Normally,
> postgresql will spill to disk if it needs more memory, unless it's
> miconfigured.
>

Yes. I tried increasing the swap space but that just meant it grew
larger...from limit:
datasize 3145728 kbytes

This is from NetBSD 4.99. I ended up running with 3.5GB of SWAP and
1.5GB of RAM.

> > 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 ;(
>
> begin/commit ain't the problem here. Looks like you've either got
> pgsql set to use too much memory or it's choosing a bad plan where it
> thinks something will fit in memory but it won't.
>

I have no other problems with any of the other tables and it is only a
small table (at the time
it should have had less than 5000 rows.)

> Have you been analyzing your data before you start working on it?
>

No.

> Can we see your postgresql.conf file?
>

Sure, I've attached it.
I've also run with the "default" .conf file without tuning it (down.)

Darren