On 2013-06-02 11:44:04 -0700, Jeff Janes wrote: > Do we know why anti-wraparound uses so many resources in the first place? > The default settings seem to be quite conservative to me, even for a > system that has only a single 5400 rpm hdd (and even more so for any real > production system that would be used for a many-GB database).
I guess the point is that nobody can actually run a bigger OLTP database successfully with the default settings. Usually that will end up with a) huge amounts of bloat in the tables autovac doesn't scan first b) forced shutdowns because autovac doesn't freeze quickly enough.
I think that Greg Smith posted elsewhere that 4MB/sec of dirtying (which is the default) was about right for some of his very busy systems, which seem like they had pretty impressive IO subsystems. I was surprised it was so low. Are there other anecdotes about what settings work well in practise, assuming people ever find ones that work well?
Which raises the question, Is the primary problem that there are no settings that work well for very those systems, or that there usually are such sweet-spot settings but mere mortals cannot find them?
The default suggestion that frequently seems to be made is just to disable autovac cost limitations because of that.
Is there general agreement that this suggestion is bad? Setting autovacuum_vacuum_cost_delay to zero is basically saying "I dare you to do your best to destroy my IO performance." So it is not surprising that this just moves one from the frying pan to the fire, or maybe the reverse. (The small ring buffer used by vacuums might save your bacon if your fsyncs actually need to hit disk, as the constant stream of fsync requests to the WAL will act as a secondary throttle).
How about recommending that if autovacuum is not keeping up, that it be tried to set it to the default divided by the number of spindles? That may be overly aggressive, but infinitely less aggressive than setting it to zero would be.