Re: Turning off HOT/Cleanup sometimes

On Mon, Apr 20, 2015 at 3:28 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> 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.

I think that's a fundamental mischaracterization of the problem. As
soon as you define this as "vacuum's problem", then of course it makes
no sense to prune in the foreground, ever. But if you define the
problem as "get the best overall system performance", then it clearly
DOES sometimes make sense to prune in the foreground, as benchmark
results upthread demonstrate.

The fact is that on a workload like pgbench - and it doesn't have to
be exactly pgbench, just any kind of workload where there are lots of
changes to the table - vacuum can at any given time be pruning at most
one page of the table. That is because only one vacuum process can be
running in a given table at one time, and it can't be doing two things
at once. But there can be many processes doing inserts, updates, or
deletes on that table, as many as whatever you have max_connections
set to. There can easily be dozens even on a well-configured system;
on a poorly configured system, there could be hundreds. It seems
obvious that if you can have dozens or hundreds of processes creating
garbage and at most one process cleaning it up, there will be cases
where you get further and further behind.

Now, it might well be that the right solution to that problem is to
allow multiple vacuum processes in the same database, or add
background workers to help with opportunistic HOT-pruning of pages so
it doesn't get done in the foreground. Fine. But as of today, on a
heavily-modified table, the ONLY way that we can possibly remove junk
from the table as fast as we're creating junk is if the backends
touching the table do some of the work. Now, Simon is making the
argument that it should be good enough to have people *modifying* the
table help with the cleanup rather than imposing that load on the
people who are only *reading* it, and that's not a dumb argument, but
there are still cases where that strategy loses - specifically, where
the table churn has stopped or paused, by autovacuum hasn't run yet.
If you're going to do 1 sequential scan of the table and then go home
for the day, HOT-pruning is dumb even in that case. If you're going
to do 1000 sequential scans of that table in a row, HOT-pruning may
very well be smart. There's no guarantee that the table has met the
autovacuum threshold, but HOT-pruning it could well be a win anyway.
Or it might be a loss. You can make any policy here look smart or
dumb by picking a particular workload, and you don't even have to
invent crazy things that will never happen in real life to do it.

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