Re: Vacuum, Freeze and Analyze: the big picture

On Mon, Jun 3, 2013 at 6:42 AM, Andres Freund <andres(at)2ndquadrant(dot)com>wrote:

> 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.

Cheers,

Jeff