Re: Still recommending daily vacuum...

"Alvaro Herrera" <alvherre(at)commandprompt(dot)com> writes:

>> FWIW, I normally go with the 8.2 defaults, though I could see dropping
>> vacuum_scale_factor down to 0.1 or 0.15. I also think the thresholds
>> could be decreased further, maybe divide by 10.
>
> How about pushing thresholds all the way down to 0?

My intuition is that the thresholds should be lowered to about 5%.

I may be biased by the TPC-C schema where the largest table, stock, gets a
little over 20 records per page so 5% represents an average of one update per
page. But there's nothing unusual about a table like that. Waiting until 20%
of the table is potentially dead --four dead tuples out of 20 per page in the
stock table case-- seems extravagantly wasteful.

I find the idea of lowering the thresholds to 0 sort of intriguing though.
That makes the vacuum delay parameters the primary method to control how
frequently vacuum runs.

Unfortunately vacuum delay settings are hard to get right. The admin needs to
observe how much of an effect the settings have on i/o throughput which varies
from system to system. And using them to control how frequently vacuum runs
would be even harder.

In an ideal world autovacuum would be able to set the delay settings based on
how many updates had happened since the last run started. If more than 5% of
the table was cleaned by vacuum then decrease the delay settings to get this
vacuum to finish sooner and allow fewer updates. If less than 5% of the table
was cleaned by vacuum then increase the delay settings to reduce the
unnecessary impact of vacuum. But that just leaves us back where we started.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com