autovacuum logging, part deux.

lrosenman@pervasive.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@[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@pervasive.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@[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

--

Rod Taylor wrote:

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.

Does anyone think we should have a stats view for the last vacuum stats
for each table?

I.E. capture all the verbose info somewhere?

Or, do people just want to increase the logging?

I still don't see a consensus on what needs to come out.

Do we still need the autovacuum_verbosity type change?

LER

--
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.Rosenman@pervasive.com
Web: www.pervasive.com

On Thu, 2006-05-04 at 11:25 -0500, Larry Rosenman wrote:

Rod Taylor wrote:

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.

Does anyone think we should have a stats view for the last vacuum stats
for each table?

This would actually suit me better as it would be trivial to plug into a
monitoring system with home-brew per table thresholds at that point.

--

On Thu, May 04, 2006 at 12:37:48PM -0400, Rod Taylor wrote:

On Thu, 2006-05-04 at 11:25 -0500, Larry Rosenman wrote:

Rod Taylor wrote:

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.

Does anyone think we should have a stats view for the last vacuum stats
for each table?

This would actually suit me better as it would be trivial to plug into a
monitoring system with home-brew per table thresholds at that point.

+1. But I also think it would be handy to have some means to better
control autovacuum logging, probably via something like
autovacuum_verbosity.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461