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" <[EMAIL PROTECTED]> 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

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.

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

  Bruce Momjian   [EMAIL PROTECTED]
  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                                            [EMAIL PROTECTED]
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at


Reply via email to