Re: Dirty pages in freelist cause WAL stuck

"Simon Riggs" <simon(at)2ndquadrant(dot)com> wrote:

> I think what you are saying is: VACUUM places blocks so that they are
> immediately reused. This stops shared_buffers from being polluted by
> vacuumed-blocks, but it also means that almost every write becomes a
> backend dirty write when VACUUM is working, bgwriter or not. That also
> means that we flush WAL more often than we otherwise would.

That's right. I think it's acceptable that vacuuming process writes dirty
buffers made by itself, because only the process slows down; other backends
can run undisturbedly. However, frequent WAL flushing should be avoided.

I found the problem when I ran VACUUM FREEZE separately. But if there were
some backends, dirty buffers made by VACUUM would be reused by those backends,
not by the vacuuming process.

> From above my thinking would be to have a more general implementation:
> Each backend keeps a list of cache buffers to reuse in its local loop,
> rather than using the freelist as a global list. That way the technique
> would work even when we have multiple Vacuums working concurrently. It
> would also then be possible to use this for the SeqScan case as well.

Great idea! The troubles are in the usage of buffers by SeqScan and VACUUM.
The former uses too many buffers and the latter uses too few buffers.
Your cache-looping will work around both cases.

> Another connected thought is the idea of a having a FullBufferList - the
> opposite of a free buffer list. When VACUUM/INSERT/COPY fills a block we
> notify the buffer manager that this block needs writing ahead of other
> buffers, so that the bgwriter can work more effectively. That seems like
> it would help with both this current patch and the additional thoughts
> above.

Do you mean that bgwriter should take care of buffers in freelist, not only
ones in the tail of LRU? We might need activity control of bgwriter. Buffers
are reused rapidly in VACUUM or bulk insert, so bgwriter is not sufficient
if its settings are same as usual.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center