Re: Autovacuum improvements

Tom Lane wrote:

> Actually the reason it's not enabled by default today has nothing to do
> with either of those; it's
>
> 3. Unexpected side effects on foreground processes, such as surprising
> failures of DROP DATABASE commands. (See archives for details.)

The referred to thread starts here:

http://archives.postgresql.org/pgsql-hackers/2006-08/msg01814.php

> Until (3) is addressed I don't think there is any chance of having
> autovac on by default, and so worrying about (1) and (2) seems a bit
> premature. Or at least, if you want to work on those fine, but don't
> expect that it will alter the fact that the factory default is "off".

Hmm, right. The mentioned problems are:

> * manual ANALYZE issued by regression tests fails because autovac is
> analyzing the same table concurrently.

> * contrib tests fail in their repeated drop/create database operations
> because autovac is connected to that database. (pl tests presumably
> have same issue.)

I suggest we should fix at least the second problem and then turn
autovac on by default, to see if there are more hurdles (and to get more
autovacuum testing during this development cycle, at least as far as
regression tests are concerned). We can turn it back off after the 8.3
cycle is done, if we don't find it living up to expectations.

I'm not sure how to fix the second problem. If it was autovac's ANALYZE
that was failing, ISTM it would be a simple problem, but we don't have
much control over the regression test's own ANALYZEs.

One idea would be to introduce the concept of launcher process I
mentioned, and have it act like the bgwriter for checkpoints: have it
start the analyze when backends request it, and then inform when the
analyze is done. So if an analyze is already running, then the launcher
does nothing except inform the backend when the analyze is finished.

So a sort of roadmap for my proposal would be to first introduce the
autovacuum launcher, and have backends communicate with it instead of
doing the work by themselves; and then introduce the scheduling concept
into the launcher.

In fact, if we have the scheduler be a separate process from the
launcher, the scheduler could be pluggable: sites for which the current
autovacuum is enough just use today's autovacuum as scheduler, and sites
which need more elaborate configuration just turn on the advanced
module.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.