Re: Turning off HOT/Cleanup sometimes

On Fri, Sep 19, 2014 at 5:42 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> On 2014-09-19 17:29:08 -0400, Robert Haas wrote:
>> > I generally have serious doubts about disabling it generally for
>> > read workloads. I imagine it e.g. will significantly penalize
>> > workloads where its likely that a cleanup lock can't be acquired
>> > every time...
>>
>> I share that doubt. But I understand why Simon wants to do something,
>> too, because the current situation is not great either.
>
> Right, I totally agree. I doubt a simple approach like this will work in
> the general case, but I think something needs to be done.
>
> I think limiting the amount of HOT cleanup for readonly queries is a
> good idea, but I think it has to be gradual. Say after a single cleaned
> up page at least another 500 pages need to have been touched till the
> next hot cleanup. That way a single query won't be penalized with
> cleaning up everything, but there'll be some progress.

I tried this kind of thing several years ago with hint-bit-setting and
was unimpressed by the results.

http://www.postgresql.org/message-id/AANLkTik5QzR8wTs0MqCWwmNp-qHGrdKY5Av5aOB7W4Dp@mail.gmail.com
http://www.postgresql.org/message-id/AANLkTimGKaG7wdu-x77GNV2Gh6_Qo5Ss1u5b6Q1MsPUy@mail.gmail.com

Granted, I never tried a ratio as low as 500:1, and HOT pruning is not
the same thing as setting hint bits, but I think the basic problems
are similar, namely:

1. You can't know how many times the page is going to be referenced in
the future before it again gets modified. If that number is small,
then you shouldn't bother with hint bits, or HOT-pruning, or freezing.
But if it's big, you should do all of those things as soon as possible
because the benefits are quite significant. Therefore, any change in
this area is guaranteed to lose on some easy-to-construct workload,
because I just described two of them that want opposing things.

2. Dirtying every N'th page is a great way to generate lots of random
I/O that will quite possibly make your disk almost as sad - or even
sadder - than dirtying all of them, but without anywhere as near as
much performance benefit.

Variations on this idea have been proposed so many times over the
years that I'm tempted to give some credence to the theory that we
ought to adopt one of them. But there will certainly be losers, as
well as winners.

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