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
