Re: autovacuum blues

Anton Melser wrote:
> On 09/11/06, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> >Anton Melser wrote:
> >
> >> Thanks for that. Just a clarification, can someone tell me what the
> >> "number of tuples" means in the context of the multipliers? I mean,
> >> when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I
> >> get the min val, but what are we multiplying by 0.4? The total number
> >> of tuples in the table? The total modified/deleted?
> >
> >pg_class.reltuples
>
> So I am still a little unclear... I need to analyse to get relevant
> stats for autovacuum... but autovacuum needs to be used to update the
> relevant stats? Can I REALLY do without a cronjob, or am I just
> thinking wishfully (I can invent expressions if I want!).

This means that we use the previously-known value of tuples in the table, plus
the number of new tuples that have been inserted, deleted and/or updated
(numbers you can see in the pg_stat_* views), and compare them to the
thresholds. If I'm being too unclear, here is the relevant code comment:

* A table needs to be vacuumed if the number of dead tuples exceeds a
* threshold. This threshold is calculated as
*
* threshold = vac_base_thresh + vac_scale_factor * reltuples
*
* For analyze, the analysis done is that the number of tuples inserted,
* deleted and updated since the last analyze exceeds a threshold calculated
* in the same fashion as above. Note that the collector actually stores
* the number of tuples (both live and dead) that there were as of the last
* analyze. This is asymmetric to the VACUUM case.

(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/autovacuum.c?rev=1.28;content-type=text%2Fx-cvsweb-markup)

The idea is that you _can_ do without a cronjob. You may need to do a
first ANALYZE just to get things warmed up, and then let autovacuum do
its job.

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