Re: Limiting setting of hint bits by read-only queries; vacuum_delay

On Tue, Mar 26, 2013 at 5:27 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 26 March 2013 01:35, Greg Stark <stark(at)mit(dot)edu> wrote:
>> On Tue, Mar 26, 2013 at 12:00 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> I'll bet you all a beer at PgCon 2014 that this remains unresolved at
>>> that point.
>>
>> Are you saying you're only interested in working on it now? That after
>> 9.3 is release you won't be interested in working on it any more?
>>
>> As you said we've been eyeing this particular logic since 2004, why
>> did it suddenly become more urgent now? Why didn't you work on it 9
>> months ago at the beginning of the release cycle?
>
> I'm not sure why your comments are so confrontational here, but I
> don't think it helps much. I'm happy to buy you a beer too.
>
> As I explained clearly in my first post, this idea came about trying
> to improve on the negative aspects of the checksum patch. People were
> working on ideas 9 months ago to resolve this, but they have come to
> nothing. I regret that; Merlin and others have worked hard to find a
> way: Respect to them.
>
> My suggestion is to implement a feature that takes 1 day to write and
> needs little testing to show it works.

Any patch in this area isn't likely to take much testing to establish
whether it improves some particular case. The problem is what happens
to all of the other cases - and I don't believe that part needs little
testing, hence the objections (with which I agree) to doing anything
about this now.

If we want to change something in this area, we might consider
resurrecting the patch I worked on for this last year, which had, I
believe, a fairly similar mechanism of operation to what you're
proposing, and some other nice properties as well:

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

...but I think the main reason why that never went anywhere is because
we never really had any confidence that the upsides were worth the
downsides. Fundamentally, postponing hint bit setting (or hint bit
I/O) increases the total amount of work done by the system. You still
end up writing the hint bits eventually, and in the meantime you do
more CLOG lookups. Now, as a compensating benefit, you can spread the
work of writing the hint-bit updated pages out over a longer period of
time, so that no single query carries too much of the burden of
getting the bits set. The worst-case-latency vs. aggregate-throughput
tradeoff is one with a long history and I think it's appropriate to
view this problem through that lens also.

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