Re: Turning off HOT/Cleanup sometimes

On Wed, Apr 22, 2015 at 04:36:17PM +0100, Greg Stark wrote:
> On Mon, Apr 20, 2015 at 8:48 PM, Bruce Momjian  wrote:
> > Well, we have to assume there are many misconfigured configurations ---
> > autovacuum isn't super-easy to configure, so we can't just blame the
> > user if this makes things worse.  In fact, page pruning was designed
> > spefically for cases where autovacuum wasn't running our couldn't keep
> > up.
> 
> Well autovacuum isn't currently considering HOT pruning part of its
> job at all. It's hard to call it "misconfigured" when there's
> literally *no* way to configure it "correctly".

Good point, but doesn't vacuum remove the need for pruning as it removes
all the old rows?

> If you update less than autovacuum_vacuum_scale_factor fraction of the
> table and then never update another row autovacuum will never run.
> Ever. Every select will forevermore need to follow hot chains on that
> table. Until eventually transaction wraparound forces a vacuum on that
> table if that ever happens.

Yes, that is a very good point, and it matches my concerns.  Of course,
Simon's concern is to avoid overly-aggressive pruning where the row is
being pruned but will soon be modified, making the prune, and its WAL
volume, undesirable.  We have to consider both cases in any final
solution.

> Possibly autovacuum could be adjusted to count how many selects are
> happening on the table and decide to vacuum it when the cost of the
> selects following the dead tuples is balanced by the cost of doing a
> vacuum. But that's not something included in the design of autovacuum
> today.

Well, autovacuum is also going to clean indexes, which seem like
overkill for pruning HOT updates.

> The original design of tuple storage was aimed at optimizing the
> steady state where most tuples were not recently updated. It
> guaranteed that except for tuples that were in the process of being
> updated or were recently updated a tuple read didn't have to read the
> CLOG, didn't have to follow any chains, didn't have to do any I/O or
> other work other than to read the bits on the tuple itself. When a
> tuple is updated it's put into a state where everyone who comes along
> has to do extra work but as soon as practical the hint bits get set
> and that extra work stops.

Yes, Simon is right that doing everything as-soon-as-possible is not
optimal.  I think the trick is knowing when we should give up waiting
for something else to dirty the page and prune it.

> We had similar discussions about setting hint bits in the past. I'm
> not sure why HOT pruning is the focus now because I actually think
> hint bit setting is a larger source of I/O in innocent looking selects
> even today. And it's a major headache, people are always being
> surprised that their selects cause lots of I/O and slow down
> dramatically after a big update or data load has finished. It's
> characterized as "why is the database writing everything twice" (and
> saying it's actually writing everything three times doesn't make
> people feel better). In the new age of checksums with hint bit logging
> I wonder if it's even a bigger issue.

What would be the downside of only doing pruning during SELECT hint bit
setting?  Hinting is delayed by long-running transactions, but so is
pruning.  I assume you can do more pruning than setting all_visible
hints because the old prunable rows are older by definition, but I am
unclear how much older they are.

FYI, while hint bit setting causes page writes, it does not cause WAL
writes unless you have wal_log_hints set or page-level checksums are
enabled.  By doing pruning at the same time as hint bit setting, you are
sharing the same page write, but are generating more WAL.  Of course, if
you are setting all-visible, then you are by definition waiting longer
to prune than before, and this might be enough to make it a win for all
use cases.  You wouldn't never-prune in a read-only workload because
your hint bits would eventually cause the pruning.

--  Bruce Momjian          http://momjian.us EnterpriseDB                             http://enterprisedb.com
 + Everyone has their own god. +