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