Re: Turning off HOT/Cleanup sometimes

On Mon, Apr 20, 2015 at 10:33 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, Apr 16, 2015 at 03:41:54PM +0100, Simon Riggs wrote:
> > That is how we arrive at the idea of a cleanup limit, further enhanced
> by a
> > limit that applies only to dirtying clean blocks, which we have 4?
> recent votes
> > in favour of.
> >
> > I would personally be in favour of a parameter to control the limit,
> since
> > whatever we chose is right/wrong depending upon circumstances. I am
> however
> > comfortable with not having a parameter if people think it is hard to
> tune
> > that, which I agree it would be, hence no parameter in the patch.
>
> I think the limit has to be in terms of a percentage of the table size.
> For example, if we do one SELECT on a table with all non-dirty pages, it
> would be good to know that 5% of the pages were pruned --- that tells me
> that another 19 SELECTs will totally prune the table, assuming no future
> writes.

But why should 1 SELECT or 20 SELECTs or 200 SELECTs have to do a job,
while the user waits, which is fundamentally VACUUM's duty to do in the
background? If there are a handful of very hot pages, then it makes sense
not to wait for vacuum to get to them. And that is what a block-count
limit does.

But if the entire table is very hot, I think that that is just another of
way of saying that autovacuum is horribly misconfigured. I think the
purpose of this patch is to fix something that can't be fixed through
configuration alone.

> If there are future writes, they would dirty the pages and
> cause even more pruning, but the 5% gives me the maximum pruning number
> of SELECTs. If there aren't another 19 SELECTs, do I care if the table
> is pruned or not?

The use case I see for this is when there is a mixed workload. There is
one select which reads the entire table, and hundreds of thousands of
selects/updates/insert that don't, and of course vacuum comes along every
now and then and does it thing. Why should the one massive SELECT have
horrible performance just because it was run right before autovacuum would
have kicked in instead of right after if finished?

Cheers,

Jeff