Re: Bug: Buffer cache is not scan resistant

On Tue, 2007-03-13 at 13:40 +0900, ITAGAKI Takahiro wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> wrote:
>
> > > > With the default
> > > > value of scan_recycle_buffers(=0), VACUUM seems to use all of buffers in pool,
> > > > just like existing sequential scans. Is this intended?
> > >
> > New test version enclosed, where scan_recycle_buffers = 0 doesn't change
> > existing VACUUM behaviour.
>
> This is a result with scan_recycle_buffers.v3.patch. I used normal VACUUM
> with background load using slowdown-ed pgbench in this instance. I believe
> the patch is useful in normal cases, not only for VACUUM FREEZE.
>
> N | time | WAL flush(*)
> -----+--------+-----------
> 0 | 112.8s | 44.3%
> 1 | 148.9s | 52.1%
> 8 | 105.1s | 17.6%
> 16 | 96.9s | 8.7%
> 32 | 103.9s | 6.3%
> 64 | 89.4s | 6.6%
> 128 | 80.0s | 3.8%

Looks good.

Not sure what value of N to pick for normal use. The objectives are
i) don't let VACUUMs spoil the cache
ii) speed up standalone VACUUMs
iii) don't let VACUUM cause others to repeatedly WAL flush

I'm thinking N=16 meets all 3 objectives. We could make VACUUM go faster
still, but by knocking more blocks out of cache that someone doing real
work might need. That will slow them down almost as much as forcing them
to flush WAL, so I'd want to be conservative with VACUUM.

Does anybody think we need a new parameter for this, or are we happy at
16 buffers in the recycle loop for VACUUM?

At this point I should note something I haven't mentioned before.
VACUUMs force other backends to flush out WAL only when we have an I/O
bound workload. If the database already fits in memory then BufferAlloc
never needs to run and therefore we don't need to flush WAL. So I can
understand that the effect of WAL flushing may not have been noticed by
many testers.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com