Re: Autovacuum degrades all other operations by keeping all buffers dirty?

On Fri, Aug 31, 2018 at 3:50 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:

> On 2018-08-31 19:31:47 -0300, Alvaro Herrera wrote:
> > On 2018-Aug-31, David Pacheco wrote:
> >
> > > From reading the 9.6.3 source, it looks like the autovacuum process
> > > itself is single-threaded, and it reads pages essentially linearly
> > > from the relation (possibly skipping some). When the autovacuum
> > > process needs to modify a page, it doesn't write it directly, but
> > > rather marks the buffer dirty. The page will be written later,
> >
> > Unless there's some bug, there is a BufferAccessStrategy that only lets
> > a few dozen buffers go unwritten before the autovac worker process
> > itself is forced to write some.
>
> I've not re-checked, but I'm not sure that's true if the buffer is
> already in s_b, which it'll be for many workloads.
>

Does that mean this analysis from above is accurate?

It looks to me like the autovacuum process is effectively generating work
> (in
> the form of async writes) that's being distributed implicitly to the
> various
> backend processes, creating latency for any other query that happens to
> require
> a buffer (including read-only queries).

Thanks,
Dave