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
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 [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
http://www.postgresql.org/about/donate
