On Tue, 2007-03-13 at 13:40 +0900, ITAGAKI Takahiro wrote: > "Simon Riggs" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
