Re: Dirty pages in freelist cause WAL stuck

On Mon, 2006-12-18 at 11:13 -0500, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > 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.
>
> Do we care? As long as the writes are done by the vacuum process, ISTM
> this is taking load off the foreground query processes, by saving them
> from having to do writes.

I'm not bothered about speeding up VACUUM FREEZE at all, but the effect
noted by Itagaki-san is clearly real and so can easily effect other
processes. I believe it does effect other backends and had already noted
what I thought was that effect myself. If we had better server
instrumentation it would be easy to demonstrate either way.

> In any case, I'm unclear on why we should add a boatload of complexity
> to improve performance of something that's done as rarely as VACUUM
> FREEZE is. Quite aside from maintainability concerns, even a few extra
> cycles added to the more common code paths would make it a net
> performance loss overall.

As I noted, this isn't just VACUUM FREEZE (why would it be?), but all
VACUUMs - that *is* a common code path on a busy system. VACUUM FREEZE
simply dirties more blocks and has a more clearly noticeable effect.

>From your comments we clearly need more testing to demonstrate the
effect on normal backends before we move to a solution.

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