Re: Limiting setting of hint bits by read-only queries; vacuum_delay

On Mon, 2013-03-25 at 12:21 +0000, Greg Stark wrote:
> On Mon, Mar 25, 2013 at 2:50 AM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> > The idea I was thinking about is refactoring the background writer's role in
> > hint bit maintenance
>
> A good first step might be to separate the "dirty" bit into two bits.
> "mandatory dirty" and "optional dirty". (Or maybe "hard dirty" and
> "soft dirty"). Hint bit updates could set the latter and then some
> later policy decision could be made about whether to bother writing
> out the buffer if it's only optionally dirty.

I like this idea, but I think there's a problem. When checksums are
enabled, we may need to write WAL at the time the page is dirtied. It
would seem a waste if a full-page image was written, but the bgwriter
never wrote the page out because it was only a "soft dirty".

One solution is to make it work as you say, unless checksums are enabled
and it needs to write WAL (which is only for the first modification
after a checkpoint). Unfortunately, it basically means that users of
checksums will still see a big impact from doing a SELECT on a large,
freshly loaded table. So that doesn't really answer Simon's use case.

Maybe it can still be made to work. Let's say that the bgwriter can't
write soft-dirty pages out, it can only ignore them or drop them on the
floor. But VACUUM would promote pages from soft-dirty to dirty at a
controlled rate, and would write out WAL if necessary for checksums.

That should still keep some distance between the WAL writing and the WAL
flushing, but still offer better control than writing it at SELECT time.
Also, it would solve a complaint I have about Simon's proposal: that we
lose the information that we have a changed buffer in shared memory.
Maybe that's not a practical problem, but it seems like we should keep
track of that and have a way to make sure a page gets cleaned if we want
to.

The downside is that it doesn't allow anyone to get the current
behavior, unless we provide an additional GUC to say whether SELECT
causes a page to be marked soft-dirty or dirty. At least that would be a
boolean though, and the finer-grained control can be over VACUUM.

Regards,
Jeff Davis