Re: Is my database now too big?

On 10/7/07, Darren Reed <darrenr+postgres(at)fastmail(dot)net> wrote:
> Scott Marlowe wrote:
> > On 10/7/07, Darren Reed <darrenr(at)fastmail(dot)net> wrote:
> > > Scott Marlowe wrote:

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

Are you sure postgresql was starting up in the /data/db directory
after reboot and not somewhere else like /var/lib/pgsql/data???

IF you're definitely hitting the right directory, then Is the database
shutting down cleanly on reboot? It might be that it's getting killed
during a write and you've got some kind of problem with fsync on your
machine so the db is getting corrupted

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

definitely sounds like some kind of issue other just the size of the
table, like some kind of corruption.

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

If nothing else, wrapping your load and building in begin;commit;
should make it faster.

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

You should definitely run analyze after updating the table. It helps
the query planner make the optimal choice for query plans.

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

Nothing odd here:

> shared_buffers = 2000 # min 16 or max_connections*2, 8KB each
> temp_buffers = 200 # min 100, 8KB each
> max_prepared_transactions = 5 # can be 0 or more
> # note: increasing max_prepared_transactions costs ~600 bytes of shared memory
> # per transaction slot, plus lock space (see max_locks_per_transaction).
> work_mem = 4096 # min 64, size in KB
> maintenance_work_mem = 8192 # min 1024, size in KB
> max_stack_depth = 400 # min 100, size in KB
>
> # - Free Space Map -
>
> max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
> max_fsm_relations = 200 # min 100, ~70 bytes each
>

This:
> effective_cache_size = 1000 # typically 8KB each
is a little low, but that's not a huge deal. effective cache size
doesn't allocate anything, it just tells the planner about how much
memory the OS is using to cache your database.

And I don't see anything else in your postgresql.conf that looks
suspicious. I'm leaning towards possible pilot error in shutting down
or starting up the db.