Re: limiting hint bit I/O - Mailing list pgsql-hackers

On 19.01.2011 15:56, Merlin Moncure wrote:
> On Wed, Jan 19, 2011 at 7:57 AM, Merlin Moncure<mmoncure@gmail.com>  wrote:
>> On Tue, Jan 18, 2011 at 12:44 PM, Robert Haas<robertmhaas@gmail.com>  wrote:
>>> On Tue, Jan 18, 2011 at 9:24 AM, Merlin Moncure<mmoncure@gmail.com>  wrote:
>>>> a few weeks back I hacked an experimental patch that removed the hint
>>>> bit action completely.  the results were very premature and/or
>>>> incorrect, but my initial findings suggested that hint bits might not
>>>> be worth the cost from performance standpoint.  i'd like to see some
>>>> more investigation in this direction before going with a complex
>>>> application mechanism (although that would be beneficial vs the status
>>>> quo).
>>>
>>> I think it's not very responsible to allege that hint bits aren't
>>> providing a benefit without providing the patch that you used and the
>>> tests that you ran.  This is a topic that needs careful analysis, and
>>> I think that saying "hint bits don't provide a benefit... maybe..."
>>> doesn't do anything but confuse the issue.  How about doing some tests
>>> with the patch from my OP and posting the results?  If removing hint
>>> bits entirely doesn't degrade performance, then surely the
>>> less-drastic approach I've taken here ought to be OK too.  But in my
>>> testing, it didn't look too good.
>>
>> hm. well, I would have to agree on the performance hit -- I figure 5%
>> scan penalty should be about the maximum you'd want to pay to get the
>> i/o reduction.  Odds are you're correct and I blew something...I'd be
>> happy to test your patch.
>
> Ah, I tested your patch vs stock postgres vs my patch, basically your
> results are unhappily correct (mine was just a hair faster than yours
> which you'd expect).  The differential was even wider on my laptop
> class hardware, maybe 26%.  I also agree that even if the penalty was
> reduced or determined to be worth it anyways, your approach to move
> the setting/i/o around to appropriate places is the way to go vs
> wholesale removal, unless some way is found to reduce clog lookup
> penalty to a fraction of what it is now (not likely, I didn't profile
> but I bet a lot of the problem is the lw lock).  Interesting I didn't
> notice this on my original test :(.

One thing to note is that the current visibility-checking code is 
optimized for the case that the hint bit is set, and the codepath where 
it's not is not particularly fast. HeapTupleSatisfiesMVCC does a lot of 
things besides checking the clog. For xmin:

1. Check HEAP_MOVED_OFF / HEAP_MOVED_IN
2. Check if xmin is the current transaction with 
TransactionIdIsCurrentTransactionId()
3. Check if xmin is still in progress with TransactionIdIsInProgress()
4. And finally, check the clog with TransactionIdDidCommit()

It would be nice to profile the code to see where the time really is 
spent. Most of it is probably in the clog access, but the 
TransactionIdInProgress() call can be quite expensive too if there's a 
lot of concurrent backends.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com