Re: [PATCH] Unremovable tuple monitoring

On 16/11/2011, at 8:04 AM, Tom Lane wrote:

> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Nov 15, 2011 at 10:29 AM, Alvaro Herrera
>> <alvherre@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
includedas 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
tryingto diagnose an ineffective vacuum and postgres' logs weren't giving me any hints about what was wrong.  I turned
tothe mailing list and got immediate help, but I felt that ideally postgres would be logging something to tell me that
some1 day old transactions were preventing auto vacuum from doing its job.  Something, anything that I could google.
Othernovices in my situation probably wouldn't know to look in the pg_stats* tables, so in retrospect my patch isn't
reallyachieving my original goal. 

Should we consider taking a logging approach instead?

--Royce