On Fri, Mar 31, 2017 at 12:31 PM, Dilip Kumar <dilipbal...@gmail.com> wrote:
> On Thu, Mar 30, 2017 at 5:27 PM, Amit Kapila <amit.kapil...@gmail.com> > wrote: > > I am not sure if we can consider it as completely synthetic because we > > might see some similar cases for json datatypes. Can we once try to > > see the impact when the same test runs from multiple clients? For > > your information, I am also trying to setup some tests along with one > > of my colleague and we will report the results once the tests are > > complete. > We have done some testing and below is the test details and results. > > Test: > I have derived this test from above test given by pavan[1] except > below difference. > > - I have reduced the fill factor to 40 to ensure that multiple there > is scope in the page to store multiple WARM chains. > - WARM updated all the tuples. > - Executed a large select to enforce lot of recheck tuple within single > query. > - Smaller tuple size (aid field is around ~100 bytes) just to ensure > tuple have sufficient space on a page to get WARM updated. > > Results: > ----------- > * I can see more than 15% of regression in this case. This regression > is repeatable. > * If I increase the fill factor to 90 than regression reduced to 7%, > may be only fewer tuples are getting WARM updated and others are not > because of no space left on page after few WARM update. > Thanks for doing the tests. The tests show us that if the table gets filled up with WARM chains, and they are not cleaned up and the table is subjected to read-only workload, we will see regression. Obviously, the test is completely CPU bound, something WARM is not meant to address.I am not yet certain if recheck is causing the problem. Yesterday I ran the test where I was seeing regression with recheck completely turned off and still saw regression. So there is something else that's going on with this kind of workload. Will check. Having said that, I think there are some other ways to fix some of the common problems with repeated rechecks. One thing that we can do it rely on the index pointer flags to decide whether recheck is necessary or not. For example, a WARM pointer to a WARM tuple does not require recheck. Similarly, a CLEAR pointer to a CLEAR tuple does not require recheck. A WARM pointer to a CLEAR tuple can be discarded immediately because the only situation where it can occur is in the case of aborted WARM updates. The only troublesome situation is a CLEAR pointer to a WARM tuple. That entirely depends on whether the index had received a WARM insert or not. What we can do though, if recheck succeeds for the first time and if the chain has only WARM tuples, we set the WARM bit on the index pointer. We can use the same hint mechanism as used for marking index pointers dead to minimise overhead. Obviously this will only handle the case when the same tuple is rechecked often. But if a tuple is rechecked only once then may be other overheads will kick-in, thus reducing the regression significantly. Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
