Re: [PATCH] Unremovable tuple monitoring

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

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Tue, Nov 15, 2011 at 10:29 AM, Alvaro Herrera
>> <alvherre(at)commandprompt(dot)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