Re: Performance, vacuum and reclaiming space, fsm

On Monday 13 October 2003 19:22, Seum-Lim Gan wrote:
> I am not sure I can do the full vacuum.
> If my system is doing updates in realtime and needs to be
> ok 24 hours and 7 days a week non-stop, once I do
> vacuum full, even on that table, that table will
> get locked out and any quiery or updates that come in
> will timeout.

If you have 150MB type of data as you said last time, you could take a pg_dump
of database, drop the database and recreate it. By all chances it will take
less time than compacting a database from 2GB to 150MB.

It does involve downtime but can't help it. Thats closet you can get.

> Any suggestion on what to do besides shutting down to
> do full vacuum ?

Drop the indexes and recreate them. While creating the index, all the updates
will be blocked anyways.

> Peter Child also mentions there is indexing bugs.
> Is this fixed in 7.3.4 ? I did notice after the database

No. It is fixed in 7.4 and 7.4 is in beta still..

> grew in disk usage, its performance greatly decreases !

Obviously that is due to unnecessary IO it has to do.

Thing is your database has reached a state that is really bad for it's
operation. I strongly encourage you to recreate the database from backup,
from scratch, tune postgresql properly and run autovacuum daemon from 7.4
source dir. Besides that you would need to reindex nightly or per 5-6 hour
depending upon rate of insertion.

Check http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html for
performance tuning starter tips..

HTH

Shridhar