On Wed, Jan 19, 2011 at 9:13 AM, Heikki Linnakangas <heikki.linnakan...@enterprisedb.com> wrote: > On 19.01.2011 15:56, Merlin Moncure wrote: >> >> On Wed, Jan 19, 2011 at 7:57 AM, Merlin Moncure<mmonc...@gmail.com> >> wrote: >>> >>> On Tue, Jan 18, 2011 at 12:44 PM, Robert Haas<robertmh...@gmail.com> >>> wrote: >>>> >>>> On Tue, Jan 18, 2011 at 9:24 AM, Merlin Moncure<mmonc...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers