Re: autovacuum logging, part deux.

I don't know about anyone else, but the only time I look at that mess is
to find poor tuple/table or tuple/index ratios and other indications
that vacuum isn't working as well as it should be.

How about this instead:

Log when the actual autovacuum_vacuum_scale_factor (dead space cleaned
up) was more than 2 times the autovacuum_vacuum_scale_factor listed in
postgresql.conf. This means autovacuum isn't keeping up to what you want
it to.

Another interesting case would be a large amount of empty space in the
index or table (say 3x autovacuum_vacuum_scale_factor). This may
indicate unnecessary bloat and something to fix.

Aside from that, the raw numbers don't really interest me.

On Thu, 2006-05-04 at 14:46 +0000, Chris Browne wrote:
> lrosenman(at)pervasive(dot)com ("Larry Rosenman") writes:
> > Gentlepeople,
> > Now that the patch is out for keeping the last
> > autovacuum/vacuum/analyze/autoanalyze
> > timestamp in the stats system is pending, what's the consensus view on
> > what, if any,
> > logging changes are wanted for autovacuum?
> >
> > I have the time and inclination to cut code quickly for it.
>
> It would be Really Nice if it could draw in the verbose stats as to
> what the VACUUM did...
>
> e.g. - to collect some portion (INFO? DETAIL? I'm easy :-)) of the
> information that PostgreSQL generates at either INFO: or DETAIL:
> levels.
>
> /* cbbrowne(at)[local]/dba2 vacdb=*/ vacuum verbose analyze vacuum_requests;
> INFO: vacuuming "public.vacuum_requests"
> INFO: index "vacuum_requests_pkey" now contains 2449 row versions in 64 pages
> DETAIL: 3 index pages have been deleted, 3 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: index "vr_priority" now contains 0 row versions in 19 pages
> DETAIL: 16 index pages have been deleted, 16 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "vacuum_requests": found 0 removable, 2449 nonremovable row versions in 65 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 2809 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: vacuuming "pg_toast.pg_toast_95167460"
> INFO: index "pg_toast_95167460_index" now contains 0 row versions in 1 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "pg_toast_95167460": found 0 removable, 0 nonremovable row versions in 0 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: analyzing "public.vacuum_requests"
> INFO: "vacuum_requests": 65 pages, 2449 rows sampled, 2449 estimated total rows
> VACUUM
>
--