Re: Set visibility map bit after HOT prune

On Wed, Dec 19, 2012 at 10:40 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Dec 19, 2012 at 11:49 AM, Pavan Deolasee
> <pavan(dot)deolasee(at)gmail(dot)com> wrote:
>> Also, if extra WAL-logging is really worrisome, may be we should again
>> seriously reconsider my idea of *not* clearing the bit at HOT update.
>> My apologies for repeating myself. But that seems very important in a
>> steady system when almost every update is a HOT update. So you don't
>> clear the bit at HOT update and so don't need to set it back either,
>> thus saving two WAL activity. You definitely don't need any vacuum in
>> this case if pruning keeps reclaiming dead space at appropriate time
>> and make it available for the next update. More so, IOS still works
>> great. Why is this so bad ?
>
> It's bad because then sequential scans will return wrong answers,
> unless we also rip out the optimization that uses PD_ALL_VISIBLE as an
> excuse to skip all visibility checks for the page. That optimization
> is worth a significant amount of performance.
>

This can be handled by breaking 1-to-1 mapping on VM bit and
PD_ALL_VISIBLE bit. So seq scans will only look at PD_ALL_VISIBLE. It
was proposed by Andres up thread, but shot down by Tom and Simon. But
I still feel that was over reaction and there is a lot of merit in the
idea. As I said elsewhere, it will also help the case when there are
DEAD line pointers in a page. Today we can't mark such pages
all-visible, but if we break this mapping, we can do that.

I would like to run some pgbench tests where we get the system in a
steady state such as all/most updates are HOT updates (not entirely
unlikely scenario for many real life cases). And then try running some
concurrent queries which can be executed via IOS. My gut feel is that,
today we will see slow and continuous drop in performance for these
queries because IOS will slowly stop working.

> It's also bad because then vacuum won't visit the page, and it really
> should. It's much better to have vacuum prune the page in the
> background than to have some query do it in the foreground, although
> the latter is still better than not doing it at all.
>

Hmm. This is a good point and I don't have an easy answer. I'm not
sure how this will pan out in real life cases though. We definitely
made great progress by having HOT, though the same concerns were
raised even then that we are moving work from background to
foreground. But I think generally HOT made great difference to the
system as a whole, may be at a cost of slowdown for some read-only,
select queries. And HOT prune is not the only operation that we do in
foreground. We also set hint bits and make buffers dirty in an
otherwise read-only queries.

>
> IMHO, the goal here should be to have some method of setting the
> visibility map, in some set of circumstances, outside of vacuum.
> Figuring out which set of circumstances is appropriate is the hard
> part.
>

Yeah, if we can figure that out conclusively, I'm sure we might be
able to auto-tune the system even further.

Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee