Re: Turning off HOT/Cleanup sometimes

On 2014-09-28 19:51:36 +0100, Simon Riggs wrote:
> On 27 September 2014 09:29, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > On 2014-09-27 10:23:33 +0300, Heikki Linnakangas wrote:
> >> This patch has gotten a fair amount of review, and has been rewritten once
> >> during the commitfest. I think it's pretty close to being committable, the
> >> only remaining question seems to be what to do with system catalogs. I'm
> >> marking this as "Returned with feedback", I take it that Simon can proceed
> >> from here, outside the commitfest.
> >
> > FWIW, I don't think it is, even with that. As is it seems very likely
> > that it's going to regress a fair share of workloads. At the very least
> > it needs a fair amount of benchmarking beforehand.
>
> There is some doubt there. We've not seen a workload that does
> actually exhibit a negative behaviour.

Neither is there much data about the magnitude of positive effect the
patch has...

> I'm not saying one doesn't exist, but it does matter how common/likely
> it is. If anyone can present a performance test case that demonstrates
> a regression, I think it will make it easier to discuss how wide that
> case is and what we should do about it. Discussing whether to do
> various kinds of limited pruning are moot until that is clear.

I doubt it'll be hard to construct a case where it'll show. My first try
of using a pgbench scale 100, -M prepared, -cj8 with a custom file with
1 write and 5 read transaction yielded the following on my laptop:

Baseline:
relname | pgbench_tellers
pg_total_relation_size | 458752
relname | pgbench_accounts
pg_total_relation_size | 1590337536
relname | pgbench_branches
pg_total_relation_size | 286720
relname | pgbench_history
pg_total_relation_size | 49979392
Patched:
relname | pgbench_tellers
pg_total_relation_size | 516096
relname | pgbench_accounts
pg_total_relation_size | 1590337536
relname | pgbench_branches
pg_total_relation_size | 360448
relname | pgbench_history
pg_total_relation_size | 49528832

So, there's a noticeable increase in size. Mostly on the smaller tables,
so probably HOT cleanup was sometimes skipped during UPDATEs due to
locks.

Baseline was:
tps = 9655.486532 (excluding connections establishing)
Patched was:
tps = 9466.158701 (including connections establishing)

That's not a unrealistic testcase.

I'm pretty sure this could be made quite a bit more pronounced by not
using a uniform distribution in the pgbench runs. And selecting a test
that's more vulnerable to the change (e.g. using a wider distribution
for the read only statements than the modifying ones) would make the the
CPU overhead of the additional heap_hot_search_buffer() overhead
heavier.

>
> My memory was that it took months for people to understand the
> frequent update use case, since catching it in flagrante delicto was
> hard. That may be the case here, or not, but negative-benefit
> experimental results very welcome.
>
> Updated patch attached to address earlier comments.

contrib (at least pgstattuple) doesn't currently compile with
this... Easily patched up tho.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services