Re: Autovacuum loose ends

This is great news! I will do what I can to continue improving the code
and address these concerns as best I can. Many of the items below will
need to be addressed by Alvaro, but I will comment where I think I have
something useful to say :-)

Tom Lane wrote:

>I've applied Alvaro's latest integrated-autovacuum patch. There are
>still a number of loose ends to be dealt with before beta, though:
>
>* Not all the functionality of the current contrib code seems to have
>made it in. In particular I noted the "sleep scaling factor" is
>missing, as well as the options to use nondefault vacuum_cost_delay
>settings. (I'm not sure how important the sleep scale factor is,
>but the vacuum cost options seem pretty critical for practical use.)
>There may be other stuff to move over; Matthew or someone more familiar
>than I with the contrib version needs to take a look. (I have refrained
>from removing the contrib module until we're sure we have extracted
>everything from it.)
>
>

I will take a look for missing features, thanks for not removing it from
contrib yet.

As for the sleep factor I'm not sure it makes sense. It was initially
put in as a way to prevent autovacuum from running more than X% of the
time. However, I think the better answer these days is to use the vacuum
delay settings.

Speaking of which, I think I mentioned this to Alvaro, but I guess it
just didn't make it in. The pg_autovacuum table should have a few
additional columns that allow setting vacuum delay settings on a per
table basis. I also think that there should be GUC settings for the
default autovacuum delay settings which an admin might want to be
separate from the system wide default vacuum delay settings.

>* The code does not make a provision to ignore temporary tables.
>Although vacuum.c and analyze.c will disregard the request to touch
>such tables, it'd probably be better to recognize the situation further
>upstream. In particular it seems that autovacuum will continually throw
>ANALYZE requests for a temp table due to lack of stats.
>
>

Does the stats system track data about temp tables? If it doesn't then
autovacuum won't try to vacuum them. Will take a look.

>* ANALYZE also refuses to do anything with pg_statistic itself, which
>is another case that may need special treatment to avoid useless cycles.
>
>

Should be easy enough to tell autovacuum to ignore this table specifically.

>* For that matter I'm unconvinced that it's a good idea to try to force
>the pgstat DB to pick up every table in every database. If there's no
>entry it's because the table is not getting modified, and therefore it
>seems to me that we can just leave well enough alone. The code really
>is not very good about doing nothing where nothing is called for ;-)
>
>

I think in a production environment, this won't be an issue, but in a
development situation where the postmaster is getting stopped and
started fairly often, it could be an issue. Actually, if the stats
system doesn't reset it's data on postmaster restart, this shouldn't be
a problem. Any thoughts on changing this default?

>* The code ignores datallowconn and therefore will periodically vacuum
>template0. I've got mixed emotions about this --- it could save
>someone's bacon if they failed to properly VACUUM FREEZE a template
>database, but in 99.99% of installations it's just wasted cycles.
>Maybe it'd make sense to perform XID-wraparound-prevention vacuuming,
>but not anything more, in a template DB. Thoughts?
>
>

Sounds like a good idea. Bacon conservation is clearly one of the goals
of autovacuum.

>* Or actually, it would vacuum template0, except that since no regular
>backend ever connects to template0, there will be no stats DB entry for
>it and so the loop in AutoVacMain will ignore it. This is definitely
>BAD as it means that a database that's not been touched since postmaster
>start will never be vacuumed, not even for XID wraparound prevention.
>That test needs to be weakened.
>
>* I'm still pretty concerned about the handling of shared catalogs.
>AFAICS the current pgstats infrastructure simply gets this wrong,
>meaning that shared catalogs will not get adequate vacuuming. We need
>to fix that.
>
>

This was handled in the contrib version by only vacuuming shared
catalogs inside template1, however it would then analyze those tables in
each and every database. Is there a reason this solution is not
adequate? Or perhaps this concept doesn't translate to the integrated
version?

>* As Alvaro noted, the default parameter settings need a lookover.
>What is in the patch is not what was the default in the contrib module,
>but the contrib defaults seem awfully passive.
>
>

Alvaro and I talked about this. I suggested these as the new defaults
as there seemed to be a consensus that the defaults in the contrib
version were not very useful for most people. Hopefully these defaults
still a bit conservative, but useful.

>* The documentation badly needs work. I committed some minimal
>additions to runtime.sgml and catalogs.sgml, but the chapter about
>routine maintenance needs a section added about how to use autovac.
>

I promised Alvaro that I would do all the documentation. I will work on
it in the next few days now that the patch has been applied.

Thanks!

Matthew O'Connor