"Alvaro Herrera" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
