At 08:07 27.02.03, you wrote:
>On 24 Feb 2003 at 15:53, Ericson Smith wrote:
> > Currently our database is at about 15gigs. Over the period of a month,
> > it grows to about 25gigs (10Gb wasted space). Every month we have a
> > cleanup routine which involves copying the most actively updated tables
> > to text, and importing the data again. We vacuum every night and analyze
> > 4 times per day, but we're loath to do a VACUUM FULL because of the
> > table locking issues (locking some of the tables would break the
> > operation of some of our 24/7 systems), hence we prefer to stop the db
> > about once per month, eat the downtime as scheduled (about 1.5 hours),
> > and get back to business for the next 30 days again.
>
>We ahd a discussion on this few days back and the solution might work as well
>for you(apart from suggestions you have already received).
>
>Instead of vacuum full on a table, backup the table to a dump file, drop
>it and
>recreate it. It takes more efforts than simple vacuum full but may run much
>faster if you have large amount of space to recover.
>
> Try it.
It was me having this problem with our DB, although it only went to 1 gig
for some
3000 rows overall. So far, the problem is not really solved for me, since
vacuum full
simply takes far too long to clean up the DB. Disksize of this DB varies around
800 MB to 1 Gig and a vacuum full 'only' takes it down to 500 megs.
A complete dump and re-creation of the dbase creates a nice 120 megs DB.
Shouldnt a 'vacuum full' come close to this ? atleast not 4-5 times the size.
Our problems are caused by a popular (?) bannersystem, btw, which simply does
too many updates. (A few on each user request to the webserver).
(The very same bannersystem exists for MySQL and the table there is less
than a megabyte. We switched to MySQL after one of the servers couldnt
stand the load
anymore. Another Server is still running the pg version, but mainly for
testing purposes and
to help out the dude who is porting it to pg ...)
I also want to add, that postgres' "contrib/vacuumlo" didnt change much on the
large DB and a load of changes on that bannersystem didn't do much either.
l8r
Andreas Rust - webnova GmbH
rust@webnova.de - www.webnova.de
Tel: +49 (0)234 - 912 96 10
Fax: +49 (0)234 - 912 96 15
+:----------------------------------------------------------:+
Internet Solutions & Creative Design