Re: Bgwriter LRU cleaning: we've been going at this all wrong

Tom Lane wrote:
> I just had an epiphany, I think.
>
> As I wrote in the LDC discussion,
> http://archives.postgresql.org/pgsql-patches/2007-06/msg00294.php
> if the bgwriter's LRU-cleaning scan has advanced ahead of freelist.c's
> clock sweep pointer, then any buffers between them are either clean,
> or are pinned and/or have usage_count > 0 (in which case the bgwriter
> wouldn't bother to clean them, and freelist.c wouldn't consider them
> candidates for re-use). And *this invariant is not destroyed by the
> activities of other backends*. A backend cannot dirty a page without
> raising its usage_count from zero, and there are no race cases because
> the transition states will be pinned.
>
> This means that there is absolutely no point in having the bgwriter
> re-start its LRU scan from the clock sweep position each time, as
> it currently does. Any pages it revisits are not going to need
> cleaning. We might as well have it progress forward from where it
> stopped before.

All true this far.

Note that Itagaki-san's patch changes that though. With the patch, the
LRU scan doesn't look for bgwriter_lru_maxpages dirty buffers to write.
Instead, it checks that there's N (where N varies based on history)
clean buffers with usage_count=0 in front of the clock sweep. If there
isn't, it writes dirty buffers until there is again.

> In fact, the notion of the bgwriter's cleaning scan being "in front of"
> the clock sweep is entirely backward. It should try to be behind the
> sweep, ie, so far ahead that it's lapped the clock sweep and is trailing
> along right behind it, cleaning buffers immediately after their
> usage_count falls to zero. All the rest of the buffer arena is either
> clean or has positive usage_count.

Really? How much of the buffer cache do you think we should try to keep
clean? And how large a percentage of the buffer cache do you think have
usage_count=0 at any given point in time? I'm not sure myself, but as a
data point the usage counts on a quick DBT-2 test on my laptop look like
this:

usagecount | count
------------+-------
0 | 1107
1 | 1459
2 | 459
3 | 235
4 | 352
5 | 481
| 3

NBuffers = 4096.

That will vary widely depending on your workload, of course, but keeping
1/4 of the buffer cache clean seems like overkill to me. If any of those
buffers are re-dirtied after we write them, the write was a waste of time.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com