Re: Improving vacuum/VM/etc

On Fri, Apr 24, 2015 at 4:11 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
> [ lots of discussion of various states ]

This is pretty confused, because you are conflating whether a page is
hinted and/or frozen with whether there are dead tuples on it. Those
two things are entirely unrelated.

>> Did you notice the discussion on the other thread about this
>> increasing WAL volume by a factor of 113?
>
> Yeah, though I'd forgotten about it. :(
>
> I wonder if there's some way we can reduce that. I just looked at what we
> WAL log for a freeze and it appears to only be xl_heap_freeze_tuple, which
> if my math is correct is 12 bytes (11 ignoring alignment). I don't
> understand how that can be 113 times worse than a plain vacuum.

Freezing requires full page writes. Setting the all-visible bit doesn't.

>> I can't really follow why you've got these states to begin with. 0,
>> 1, and 2 are all pretty much the same. The useful distinction AFAICS
>> is between not-all-visible, all-visible, and all-visible-plus-frozen.
>
> Index scanning is probably the most expensive part of cleanup, so it seems
> like it would be useful to be able to track that as visible/frozen.

The existing visibility map already does *exactly* that.

> (What
> would probably be more useful is a way to directly link a heap tuple to any
> index tuples pointing at it, but that would certainly be a lot harder to
> do.)

That's a complete non-starter. Even if you could make it work, it
would eat up huge quantities of disk space, generate massive
additional disk writes every time an index tuple was moved (which
happens constantly), and generally destroy performance completely.

> There's also the idea of being proactive about hinting and pruning, instead
> of foisting that onto later foreground processes or hoping that vacuum comes
> along. Certainly the most obvious part is doing that before buffers are
> evicted, but it's not uncommon for the OS cache to be 10x larger (or more).
> Even if we can't hit these pages before they're all the way on disk, if we
> at least know there's a pile of them we can do something before a foreground
> process (or at least let the DBA know). But if we are keeping the number of
> tuples in this state more limited then perhaps a map isn't the right way to
> track it; I think that will come down to a question of how that ties in with
> the long-lived states.

I think having a background process that prunes before eviction is an
idea worth exploring, but it's a totally separate question from what
states we need to track in a visibility map or similar.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company