Re: How much do the hint bits help?

On 23/12/10 05:06, Merlin Moncure wrote:
> On Wed, Dec 22, 2010 at 10:59 AM, Tom Lane<tgl@sss.pgh.pa.us>  wrote:
>> Heikki Linnakangas<heikki.linnakangas@enterprisedb.com>  writes:
>>> My gut feeling is that a reasonable compromise is to set hint bits like
>>> we do today, but don't mark the page as dirty when only hint bits are
>>> set. That way you get the benefit of hint bits for tuples that are
>>> frequently accessed and stay in buffer cache. But you don't spend any
>>> extra I/O to set them.
>> I think it's far more likely that that could be acceptable than the
>> radical method of removing hint bits altogether.
>>
>> I have not looked into what's wrong with Merlin's test case, but my
>> thinking about it goes like this: we know that contention for buffer
>> lookup is significant at high loads, despite the facts that the accesses
>> are distributed across a lot of independently-usable buffers and we've
>> done much work to partition the lookup locks.  If we remove hint bits
>> and thereby force an access to clog for every tuple touch, we can expect
>> that the contention for clog access will be comparable to the worst case
>> for buffer access contention ... except that in many cases, it will be
>> distributed across far fewer pages and so the actual interference rate
>> will be far higher.  This will make our past experiences with "context
>> swap storms" look like a day at the beach.
> right.  note I'm not suggesting they they should actually be removed,
> at least not yet.  I was just playing around and noticed that the cost
> of not having them is not immediately obvious in highly synthetic
> tests.  The cost of clog access in best case scenario appears to be
> near zero, which I thought was interesting enough to point out.  What
> I'm after here is the worst case scenario, how likely it is to happen,
> and looking into possible remedies (if any).
>
> I'm going to do lots more testing over the holidays.  I'm fishing for
> ideas on good ways to flesh things out more.
>
>

Certainly having a choice about configuring them would be a good 
addition in itself, e.g  for data warehousing use the hint bits can be a 
considerable impediment so the *ability* to not have them would be a 
huge advantage.

if I have time over the early new year I'll do some testing too.

Cheers

Mark