Re: autovacuum logging, part deux.

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.

Thanks,
Larry Rosenman

--
Larry Rosenman
Database Support Engineer

PERVASIVE SOFTWARE. INC.
12365B RIATA TRACE PKWY
3015
AUSTIN TX 78727-6531

Tel: 512.231.6173
Fax: 512.231.6597
Email: Larry(dot)Rosenman(at)pervasive(dot)com
Web: www.pervasive.com

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

--
"cbbrowne","@","acm.org"
http://cbbrowne.com/info/x.html
If you stand in the middle of a library and shout "Aaaaaaaaargh" at
the top of your voice, everyone just stares at you. If you do the same
thing on an aeroplane, why does everyone join in?

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
>
--