Re: Dirty pages in freelist cause WAL stuck

Is this a TODO item?

---------------------------------------------------------------------------

ITAGAKI Takahiro wrote:
> "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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +