Re: VACUUM FULL on 24/7 server

Matthew T. O'Connor wrote:
> 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.

The reason is that I have few tables of about 5 milion with ~ 10000 insert per
day. Even with setting -v 300 -V 0.1 this means these tables will be analyzed
each 50 days. So I have to force it.

Regards
Gaetano Mendola

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>