Ernesto Quiñones wrote:
> Scott Marlowe wrote:
>> Ernesto Quiñones wrote:
>>> I want to know if it's possible to predict (calculate), how long
>>> a VACUUM FULL process will consume in a table?
I don't think you said what version of PostgreSQL you're using.
VACUUM FULL prior to version 9.0 is not recommended for most
situations, and can take days or weeks to complete where other
methods of achieving the same end may take hours. If you have
autovacuum properly configured, you will probably never need to run
VACUUM FULL.
>> If you look at what iostat is doing while the vacuum full is
>> running, and divide the size of the table by that k/sec you can
>> get a good approximation of how long it will take. Do you have
>> naptime set to anything above 0?
>
> Thanks for the answer Scott, actually my autovacuum_naptime is 1h
Ah, well that right there is likely to put you into a position where
you need to do painful extraordinary cleanup like VACUUM FULL. In
most situation the autovacuum defaults are pretty good. Where they
need to be adjusted, the normal things which are actually beneficial
are to change the thresholds to allow more aggressive cleanup or (on
low-powered hardware) to adjust the cost ratios so that performance
is less affected by the autovacuum runs. When autovacuum is disabled
or changed to a long interval, it almost always results in bloat
and/or outdated statistics which cause much more pain than a more
aggressive autovacuum regimine does.
> but I don't find naptime parameter for a manual vacuum
I'm guessing that Scott was thinking of the vacuum_cost_delay
setting:
http://www.postgresql.org/docs/current/interactive/runtime-config-resource.html#GUC-VACUUM-COST-DELAY
-Kevin