On 16/11/2011, at 8:04 AM, Tom Lane wrote: > Robert Haas <robertmh...@gmail.com> writes: >> On Tue, Nov 15, 2011 at 10:29 AM, Alvaro Herrera >> <alvhe...@commandprompt.com> wrote: >>> Excerpts from Robert Haas's message of mar nov 15 12:16:54 -0300 2011: >>>> I guess this is a dumb question, but why don't we remove all the dead >>>> tuples? > >>> They were deleted but there are transactions with older snapshots. > >> Oh. I was thinking "dead" meant "no longer visible to anyone". But >> it sounds what we call "unremovable" here is what we elsewhere call >> "recently dead". > > Would have to look at the code to be sure, but I think that > "nonremovable" is meant to count both live tuples and > dead-but-still-visible-to-somebody tuples. > > The question that I think needs to be asked is why it would be useful > to track this using the pgstats mechanisms. By definition, the > difference between this and the live-tuple count is going to be > extremely unstable --- I don't say small, necessarily, but short-lived. > So it's debatable whether it's worth memorializing the count obtained > by the last VACUUM at all. And doing it through pgstats is an expensive > thing. We've already had push-back about the size of the stats table > on large (lots-o-tables) databases. Adding another counter will impose > a performance overhead on everybody, whether they care about this number > or not. > > What's more, to the extent that I can think of use-cases for knowing > this number, I think I would want a historical trace of it --- that is, > not only the last VACUUM's result but those of previous VACUUM cycles. > So pgstats seems like it's both expensive and useless for the purpose. > > Right now the only good solution is trawling the postmaster log. > Possibly something like pgfouine could track the numbers in a more > useful fashion.
Thanks all for the input. Tom: My first patch attempted to log the number of unremovable tuples in this log, but it was done inappropriately -- it was included as part of the log_autovacuum_min_duration's output. You rightly objected to that patch :) Personally I think some log output, done better, would have been more useful for me at the time. At the time I was trying to diagnose an ineffective vacuum and postgres' logs weren't giving me any hints about what was wrong. I turned to the mailing list and got immediate help, but I felt that ideally postgres would be logging something to tell me that some 1 day old transactions were preventing auto vacuum from doing its job. Something, anything that I could google. Other novices in my situation probably wouldn't know to look in the pg_stats* tables, so in retrospect my patch isn't really achieving my original goal. Should we consider taking a logging approach instead? --Royce -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
