Re: Dirty pages in freelist cause WAL stuck

I think there's improvement to be made in how we track buffer usage  
in general. Seqscans still hold the same weight as any other  
operation, the freelist is of questionable value, and there's a lot  
of work done to find a free buffer out of the pool, for example.

On Feb 2, 2007, at 8:08 PM, Bruce Momjian wrote:

>
> Is this a TODO item?
>
> ---------------------------------------------------------------------- 
> -----
>
> ITAGAKI Takahiro wrote:
>> "Simon Riggs" <simon@2ndquadrant.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@momjian.us
>   EnterpriseDB    http://www.enterprisedb.com
>
>   + If your life is a hard drive, Christ can be your backup. +
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)