Re: Proposal: Log inability to lock pages during vacuum

On 10/20/14, 10:29 AM, Greg Stark wrote:
> On Mon, Oct 20, 2014 at 2:57 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
>> Currently, a non-freeze vacuum will punt on any page it can't get a cleanup
>> lock on, with no retry. Presumably this should be a rare occurrence, but I
>> think it's bad that we just assume that and won't warn the user if something
>> bad is going on.
>>
>> My thought is that if we skip any pages elog(LOG) how many we skipped. If we
>> skip more than 1% of the pages we visited (not relpages) then elog(WARNING)
>> instead.
>
> Is there some specific failure you've run into where a page was stuck
> in a pinned state and never got vacuumed?

Not that I know of... but how would I actually know? Having that info available is the point of my proposal. :)

> I would like to see a more systematic way of going about this. What
> LSN or timestamp is associated with the oldest unvacuumed page? How
> many times have we tried to visit it? What do those numbers look like
> overall -- i.e. what's the median number of times it takes to vacuum a
> page and what does the distribution look like of the unvacuumed ages?
>
> With that data it should be possible to determine if the behaviour is
> actually working well and where to draw the line to determine outliers
> that might represent bugs.

I agree we could use better data about/for vacuum (see http://www.postgresql.org/message-id/544468C1.6050101@BlueTreble.com)

In the meantime, I think it's worth adding this logging. If in fact this basically never happens (the current assumption), it doesn't hurt anything. If it turns out our assumption is wrong, then we'll actually be able to find that out. :)
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com