Re: Is my database now too big?

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.

> 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?

> 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.

> 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.

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

Can we see your postgresql.conf file?