Re: [HACKERS] autovacuum logging, part deux.
[EMAIL PROTECTED] (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. /* [EMAIL PROTECTED]/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 Argh 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? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 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 +, Chris Browne wrote: [EMAIL PROTECTED] (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. /* [EMAIL PROTECTED]/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 -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] autovacuum logging, part deux.
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: [EMAIL PROTECTED] Web: www.pervasive.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] autovacuum logging, part deux.
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. -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] autovacuum logging, part deux.
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
