Re: Autovacuum and Autoanalyze

On Wed, 2008-09-17 at 10:09 +0300, Heikki Linnakangas wrote:
> David Fetter wrote:
> > On Tue, Sep 16, 2008 at 08:59:08PM -0400, Alvaro Herrera wrote:
> >> Simon Riggs wrote:
> >>> Disabling autovacuum can have catastrophic effects, since it disables
> >>> the ANALYZing of tables.
> >>>
> >>> Can we have a mode where we disable autoVACUUM yet enable autoANALYZE?
> >> You mean something like
> >> autovacuum = on / off / analyze ?
> >>
> >> We can certainly do that, but is there buy-in?
> >
> > +1
> >
> > Having autovacuum on during bulk loads can really tank performance,
> > but having autoanalyze on is good :)
>
> Isn't autoanalyze a waste of time during a bulk load? Seems better to
> run ANALYZE manually at the end.

Its not a waste of time because it catches tables immediately they have
been loaded, not just at the end of the bulk load. Running ANALYZE is a
waste of time if autoanalyze has already caught it, which is why that's
never been added onto the end of a pg_dump script. But currently this is
true only when we have both autoVACUUM and autoANALYZE enabled.

> Adding that option feels natural to me, but it is a rather blunt
> instrument. You can already do that with pg_autovacuum, though that
> interface isn't very user-friendly. I whole-heartedly support the idea
> of controlling autovacuum with storage options, e.g "ALTER TABLE ...
> WITH (autoanalyze = on)".

Yes, have that option also, since it is fine tuning.

I definitely want a blunt instrument! I don't want to have to run ALTER
TABLE on *every* table. Even if you think that's possible, it won't work
in conjunction with interfaces submitting standard SQL, plus it won't
work if I forget either.

This request comes from a real situation where a dump was reloaded
during the day when autovacuum was off and so ANALYZE was missed. Not my
mistake, but it took time to resolve that could have been avoided by the
new option suggested here.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support