Re: VACUUM FULL on 24/7 server

On Sun, 2004-10-03 at 21:01, Gaetano Mendola wrote:
> Tom Lane wrote:
> > Gaetano Mendola <mendola(at)bigfoot(dot)com> writes:
> >
> >>Christopher Browne wrote:
> >>pg_autovacuum -d 3 -v 300 -V 0.5 -S 0.8 -a 200 -A 0.8
> >
> > I'm not very familiar at all with appropriate settings for autovacuum,
> > but doesn't the above say to vacuum a table only when the dead space
> > reaches 50%? That seems awfully lax to me. I've always thought one
> > should vacuum often enough to keep dead space to maybe 10 to 25%.

Yes that is what those options say. The default values are even more
lax. I wasn't sure how best to set them, I erred on the conservative
side.

> The problem is that I can not set these value per table and per database
> so, I had to find some compromise, however I will test in the next days
> what happen with -V 0.2
>
> However each six hour I perform a vacuum on all database and the HD space
> continue to grow even with FSM parameters large enough.

Since you are running autovacuum I doubt the doing vacuumdb -a -z is 3
times a day buying you much. It's not a bad idea to do once in a while.

Given the way Postgres works, it is normal to have slack space in your
tables. The real question is do your table stop growing? At some point
you should reach a stead state where you have some percentage of slack
space that stops growing.

You said that after running for a week you have 400M of reclaimable
space. Is that a problem? If you don't do a vacuum full for two weeks
is it still 400M? My guess is most of the 400M is created in the first
few hours (perhaps days) after running your vacuum full.

Matthew