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

On Wed, Jan 19, 2011 at 9:13 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> 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.

Nice thought -- it's worth checking out. I'll play around with it some
more -- I think you're right and the first step is to profile.  If the
bottleneck is in fact the lock there's not much that can be done
afaict.

merlin