Обсуждение: Autovacuum degrades all other operations by keeping all buffers dirty?
Hello,
We've been struggling with some major performance issues related to autovacuum.
(I know this is a common problem.) For a while, we believed this was primarily
related to I/O contention, but recent observations from our monitoring make me
wonder if there's a deeper issue here, so I'm looking for some better
understanding.
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, either by the checkpointer (according to its configuration, in
terms of time and WAL), the bgwriter (according to its configuration, in terms
of write count and sleep time), or else some other backend process that requires
a free buffer (if the count of non-dirty buffers reaches zero). Is this
accurate?
In our case, we found that when autovacuum runs, the number of dirty buffers
written by regular backends shoots from a handful to as much as 300 buffers per
second. (We have 200 backends on most databases.) More specifically: prior to
recent autovacuums starting, databases did under 1000 buffer allocations per
second. Most of those were done by the checkpointer. Individual backends did
just a handful. After autovacuum started, buffer allocations rose to between
2,000 and 8,000 per second. The checkpointer handled many of these, but so did
individual backends (see above). The bgwriter peaked around 25 buffer writes
per second. So it seems like the spike in buffers written by normal backends
could explain the significant degradation that we see in average latency and
overall throughput (which can be as much as 50%).
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). Maybe we can improve this by tuning the
bgwriter. But if that's single-threaded, presumably there's an upper bound to
how many buffer writes it can handle? Is there anything to guarantee that the
bgwriter will keep up with the work that's being generated? That is, for some
workloads, wouldn't it be possible that autovacuum could always generate work
faster than the bgwriter can do it, and you'd always have some pretty major
degradation to all other queries?
I've drawn a lot of inferences here, and I'm sure there are some mistakes. I'd
appreciate any clarification, correction, or confirmation!
Thanks in advance,
Dave
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. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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. Greetings, Andres Freund
On Fri, Aug 31, 2018 at 3:50 PM, Andres Freund <andres@anarazel.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