Re: How about a option to disable autovacuum cancellation on lock conflict?

On Tue, Dec 2, 2014 at 11:12 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> On 12/02/2014 11:08 AM, Andres Freund wrote:
> > On 2014-12-02 11:02:07 -0800, Josh Berkus wrote:
> >> On 12/02/2014 10:35 AM, Alvaro Herrera wrote:
> >>> If the table is large, the time window for this to happen is large
> also;
> >>> there might never be a time window large enough between two lock
> >>> acquisitions for one autovacuum run to complete in a table. This
> >>> starves the table from vacuuming completely, until things are bad
> enough
> >>> that an emergency vacuum is forced. By then, the bloat is disastrous.
> >>>
> >>> I think it's that suicide that Andres wants to disable.
> >
> > Correct.
> >
> >> A much better solution for this ... and one which would solve a *lot* of
> >> other issues with vacuum and autovacuum ... would be to give vacuum a
> >> way to track which blocks an incomplete vacuum had already visited.
> >> This would be even more valuable for freeze.
> >
> > That's pretty much a different problem. Yes, some more persistent would
> > be helpful - although it'd need to be *much* more than which pages it
> > has visited - but you'd still be vulnerable to the same issue.
>
> If we're trying to solve the problem that vacuums of large, high-update
> tables never complete, it's solving the same problem. And in a much
> better way.
>
> And yeah, doing a vacuum placeholder wouldn't be simple, but it's the
> only solution I can think of that's worthwhile. Just disabling the
> vacuum releases sharelock behavior puts the user in the situation of
> deciding between maintenance and uptime.
>

I think it would be more promising to work on downgrading lock strengths so
that fewer things conflict, and it would be not much more work than what
you propose.

What operations are people doing on a regular basis that take locks which
cancel vacuum? create index?

Cheers,

Jeff