Re: Turning off HOT/Cleanup sometimes

* Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
> > I'm -1 for a parameter as well, but I think that just stopping SELECTs
> > from doing pruning at all might well be a win. It's at least worthy
> > of some investigation.
>
> Turning HOT off completely would be an absolute disaster for OLTP on
> high update use cases against medium-large tables. That scenario is
> well represented by pgbench and TPC-C. I am *not* suggesting we
> recommend that and would look for very large caveats in the docs.

This is true even if we're only talking about turning it off for the
SELECT case...? That's what's under discussion here, after all.
Certainly, we wouldn't turn it off completely...

> (That may not have been clear, I guess I just assumed people would
> know I was heavily involved in the HOT project and understood its
> benefits).

I'm certainly aware that you were heavily involved in HOT but I don't
think anyone is argueing to turn it off for everything.

> As stated, I am interested in turning off HOT in isolated, user
> specified situations, perhaps just for isolated tables.

I tend to agree w/ Tom on this point- having this be a per-table
configurable doesn't sound very appealing to me and it wouldn't address
the case you mentioned around pg_dump, but I'm sure that'd be the next
step for this and a per-session GUC wouldn't be sufficient.

> I'm not crazy about exposing magic parameters either but then I'm not
> crazy about either automatic settings or deferring things because we
> don't know how to set it. In general, I prefer the idea of having a
> user settable parameter in one release then automating it in a later
> release if clear settings emerge from usage. I'll submit a patch with
> parameter, to allow experimentation, for possible removal at commit or
> beta.

Ugh, adding GUCs is bad *because* we end up never being able to remove
them.

> If I had to suggest a value for an internal parameter, I would say
> that each SELECT statement should clean no more than 4 blocks. That
> way current OLTP behaviour is mostly preserved while the big queries
> and pg_dump don't suck in unpredictable ways.

Right, this was one idea that I had also, as noted in the other
subthread. I'm not convinced that it's a great idea and it'd probably
be good to do a bit of testing to see just what the cost is; perhaps
even just come up with a "worst-case" example to see the difference
between a "clean" table and one with HOT chains as deep as they can go..

> I'll submit the patch and we can talk some more.

Neat.

Thanks!

Stephen