Skip site navigation (1) Skip section navigation (2)

Re: Dirty pages in freelist cause WAL stuck

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Dirty pages in freelist cause WAL stuck
Date: 2007-02-03 02:08:56
Message-ID: 200702030208.l1328ul26953@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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. +

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2007-02-03 02:44:27
Subject: Re: [HACKERS] unixware and --with-ldap
Previous:From: David FetterDate: 2007-02-03 02:03:13
Subject: Re: writing new regexp functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group