Re: Turning off HOT/Cleanup sometimes

On 15 April 2015 at 12:39, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Apr 15, 2015 at 8:42 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> I won't take responsibility for paying my neighbor's tax bill, but I
>>> might take responsibility for picking up his mail while he's on
>>> holiday.
>>
>> That makes it sound like this is an occasional, non-annoying thing.
>>
>> It's more like, whoever fetches the mail needs to fetch it for
>> everybody. So we are slowing down one person disproportionately, while
>> others fly through without penalty. There is no argument that one
>> workload necessarily needs to perform that on behalf of the other
>> workload.
>
> Sure there is. It's called a tragedy of the commons - everybody acts
> in their own selfish interest (it's not *my* responsibility to limit
> grazing on public land, or prune this page that I'm not modifying) and
> as a result some resource that everybody cares about (grass,
> system-wide I/O) gets trashed to everyone's detriment. Purely selfish
> behavior can only be justified here if we assume that the selfish
> actor intends to participate in the system only once: I'm going to run
> one big reporting query which must run as fast as possible, and then
> I'm getting on a space ship to Mars. So if my refusal to do any
> pruning during that reporting query causes lots of extra I/O on the
> system ten minutes from now, I don't care, because I'll have left the
> playing field forever at that point.

It all depends upon who is being selfish. Why is a user "selfish" for
not wanting to clean every single block they scan, when the people
that made the mess do nothing and go faster 10 minutes from now?
Randomly and massively penalising large SELECTs makes no sense. Some
cleanup is OK, with reasonable limits, which is why that is proposed.

On 04/15/2015 05:44 PM, Alvaro Herrera wrote:
> Robert's proposal is "when reading a page, if dirty HOT-clean it; if not
> dirty, also HOT-clean it but only 5 times in each scan". This runs
> HOT-cleanup some number of times (as many as there are dirty), and
> causes at most 5 pages to become dirty.

My understanding of Robert's proposal was "when reading a page,
HOT-clean it, but only do this up to 5 times on clean pages, but
continue to do this indefinitely when the page is already dirty.".
Andres said that was the only way and I have agreed to it.

Are you now saying not to commit your proposal at all?

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services