Re: Move unused buffers to freelist - Mailing list pgsql-hackers

<div class="WordSection1"><p class="MsoPlainText">On Wednesday, May 15, 2013 8:38 AM Amit Kapila wrote:<p
class="MsoPlainText">>On Wednesday, May 15, 2013 12:44 AM Greg Smith wrote:<p class="MsoPlainText">> > On
5/14/139:42 AM, Amit Kapila wrote:<p class="MsoPlainText">> > > In the attached patch, bgwriter/checkpointer
movesunused<p class="MsoPlainText">> > (usage_count<p class="MsoPlainText">> > > =0 && refcount
=0) buffer's to end of freelist. I have implemented<p class="MsoPlainText">> a<p class="MsoPlainText">> > >
newAPI StrategyMoveBufferToFreeListEnd() to<p class="MsoPlainText">> ><p class="MsoPlainText">> > There's a
commentin the new function:<p class="MsoPlainText">> ><p class="MsoPlainText">> > It is possible that we
aretold to put something in the freelist that<p class="MsoPlainText">> > is already in it; don't screw up the
listif so.<p class="MsoPlainText">> ><p class="MsoPlainText">> > I don't see where the code does anything
tohandle that though.  What<p class="MsoPlainText">> > was your intention here?<p class="MsoPlainText">> <p
class="MsoPlainText">>The intention is that put the entry in freelist only if it is not in<p
class="MsoPlainText">>freelist which is accomplished by check<p class="MsoPlainText">> If (buf->freeNext ==
FREENEXT_NOT_IN_LIST).Every entry when removed<p class="MsoPlainText">> from<p class="MsoPlainText">> freelist,
buf->freeNextis marked as FREENEXT_NOT_IN_LIST.<p class="MsoPlainText">> Code Reference (last line):<p
class="MsoPlainText">>StrategyGetBuffer()<p class="MsoPlainText">> {<p class="MsoPlainText">> ..<p
class="MsoPlainText">>..<p class="MsoPlainText">> while (StrategyControl->firstFreeBuffer >= 0)<p
class="MsoPlainText">>        {<p class="MsoPlainText">>                 buf =
&BufferDescriptors[StrategyControl-<pclass="MsoPlainText">> >firstFreeBuffer];<p class="MsoPlainText">>
                Assert(buf->freeNext!= FREENEXT_NOT_IN_LIST);<p class="MsoPlainText">> <p
class="MsoPlainText">>                /* Unconditionally remove buffer from freelist */<p class="MsoPlainText">>
                StrategyControl->firstFreeBuffer= buf->freeNext;<p class="MsoPlainText">>
                buf->freeNext= FREENEXT_NOT_IN_LIST;<p class="MsoPlainText">> <p class="MsoPlainText">> ...<p
class="MsoPlainText">>}<p class="MsoPlainText">> <p class="MsoPlainText">> Also the same check exists in
StrategyFreeBuffer().<pclass="MsoPlainText">> <p class="MsoPlainText">> > This area has always been the tricky
partof the change.  If you do<p class="MsoPlainText">> > something complicated when adding new entries, like
scanningthe<p class="MsoPlainText">> > freelist for duplicates, you run the risk of holding BufFreelistLock<p
class="MsoPlainText">>> for<p class="MsoPlainText">> > too long.<p class="MsoPlainText">> <p
class="MsoPlainText">>Yes, this is true and I had tried to hold this lock for minimal time.<p
class="MsoPlainText">>In this patch, it holds BufFreelistLock only to put the unused buffer<p
class="MsoPlainText">>at end<p class="MsoPlainText">> of freelist.<p class="MsoPlainText">> <p
class="MsoPlainText">>> To try and see that in benchmarks, I would use a small<p class="MsoPlainText">> >
databasescale (I typically use 100 for this type of test) and a<p class="MsoPlainText">> large<p
class="MsoPlainText">>> number of clients.<p class="MsoPlainText"><span style="color:black"> </span><p
class="MsoPlainText"><spanstyle="color:black">I shall try this test, do you have any suggestions for shred buffers and
numberof clients for 100 scale factor?</span><p class="MsoPlainText"> <p class="MsoPlainText">> >"-M prepared"
wouldhelp get a higher transaction<p class="MsoPlainText">> > rate out of the hardware too.  It might take a
serverwith a large<p class="MsoPlainText">> core<p class="MsoPlainText">> > count to notice any issues with
holdingthe lock for too long though.<p class="MsoPlainText">> <p class="MsoPlainText">> This is good idea, I
shalltake another set of readings with "-M<p class="MsoPlainText">> prepared"<p class="MsoPlainText">> <p
class="MsoPlainText">>> Instead you might just invalidate buffers before they go onto the<p
class="MsoPlainText">>list.<p class="MsoPlainText">> >   Doing that will then throw away usefully cached data
though.<pclass="MsoPlainText">> <p class="MsoPlainText">> Yes, if we invalidate buffers, it might throw away
usefullycached data<p class="MsoPlainText">> especially when working set just a tiny bit smaller than<p
class="MsoPlainText">>shared_buffers.<p class="MsoPlainText">> This is pointed by Robert in his mail<p
class="MsoPlainText">>http://www.postgresql.org/message-<p class="MsoPlainText">>
id/CA+TgmoYhWsz__KtSxm6BuBirE7VR6Qqc_COkbE<pclass="MsoPlainText">> ZTQpk8oom3CA@mail.gmail.com<p
class="MsoPlainText">><p class="MsoPlainText">> <p class="MsoPlainText">> > To try and optimize both
insertionspeed and retaining cached data,<p class="MsoPlainText">> <p class="MsoPlainText">> I think by the
methodproposed by patch it takes care of both, because<p class="MsoPlainText">> it<p class="MsoPlainText">>
directlyputs free buffer at end of freelist and<p class="MsoPlainText">> because it doesn't invalidate the buffers
itcan retain cached data for<p class="MsoPlainText">> longer period.<p class="MsoPlainText">> Do you see any flaw
withcurrent approach?<p class="MsoPlainText">> <p class="MsoPlainText">> > I<p class="MsoPlainText">> >
wasthinking about using a hash table for the free buffers, instead<p class="MsoPlainText">> of<p
class="MsoPlainText">>> the simple linked list approach used in the code now.<p class="MsoPlainText">> <p
class="MsoPlainText">>Okay, we can try different methods for maintaining free buffers if we<p
class="MsoPlainText">>find<p class="MsoPlainText">> current approach doesn't turn out to be good.<p
class="MsoPlainText">><p class="MsoPlainText">> > Also:  check the formatting on the additions to in bufmgr.c,
I<pclass="MsoPlainText">> noticed<p class="MsoPlainText">> > a<p class="MsoPlainText">> > spaces vs.
tabsdifference on lines 35/36 of your patch.<p class="MsoPlainText">> <p class="MsoPlainText">> Thanks for
pointingit, I shall send an updated patch along with next<p class="MsoPlainText">> set of<p
class="MsoPlainText">>performance data.<p class="MsoPlainText"><span style="color:black"> </span><p
class="MsoPlainText"><spanstyle="color:black"> </span><p class="MsoPlainText"><span style="color:black">Further
PerformanceData:</span><p class="MsoPlainText"><span style="color:black"> </span><p class="MsoPlainText"><span
style="color:black">Belowdata is for average 3 runs of 20 minutes</span><p class="MsoPlainText"><span
style="color:black">ScaleFactor   - 1200</span><p class="MsoPlainText"><span style="color:black">Shared Buffers -
7G</span><pclass="MsoPlainText"><span style="color:black"> </span><p class="MsoPlainText"><span
style="color:black"> </span><pclass="MsoPlainText"><span style="color:black">                   8C-8T               
16C-16T              32C-32T            64C-64T</span><p class="MsoPlainText"><span
style="color:black">HEAD              1739                   1461                 1578                 1609</span><p
class="MsoPlainText"><spanstyle="color:black">After Patch        4029                   1924              
  1743                1706</span><p class="MsoPlainText"><span style="color:black"> </span><p
class="MsoPlainText"><spanstyle="color:black"> </span><p class="MsoPlainText"><span style="color:black">Scale Factor  
-1200</span><p class="MsoPlainText"><span style="color:black">Shared Buffers – 10G</span><p class="MsoPlainText"><span
style="color:black"> </span><pclass="MsoPlainText"><span style="color:black">                   8C-8T               
16C-16T              32C-32T            64C-64T</span><p class="MsoPlainText"><span
style="color:black">HEAD              2004                   2270                 2195                 2173</span><p
class="MsoPlainText"><spanstyle="color:black">After Patch        2298                   2172              
  2111                2044</span><p class="MsoPlainText"><span style="color:black"> </span><p
class="MsoPlainText"><spanstyle="color:black"> </span><p class="MsoPlainText"><span style="color:black">Detailed data
of3 runs is attached with mail.</span><p class="MsoPlainText"><span style="color:black"> </span><p
class="MsoPlainText"><spanstyle="color:black">Observations :</span><p class="MsoPlainText"><span
style="color:black"> </span><pclass="MsoPlainText" style="margin-left:.5in;text-indent:-.25in;mso-list:l1 level1
lfo1"><spanstyle="color:black"><span style="mso-list:Ignore">1.<span style="font:7.0pt "Times New Roman""> 
</span></span></span><spanstyle="color:black">For scale factor 1200, With 5G and 7G Shared buffers, </span><p
class="MsoPlainText"style="margin-left:66.0pt;text-indent:-.25in;mso-list:l0 level1 lfo2"><span
style="color:black"><spanstyle="mso-list:Ignore">a.<span style="font:7.0pt "Times New Roman""> 
</span></span></span><spanstyle="color:black">there is reasonably good performance after patch (>50%).</span><p
class="MsoPlainText"style="margin-left:66.0pt;text-indent:-.25in;mso-list:l0 level1 lfo2"><span
style="color:black"><spanstyle="mso-list:Ignore">b.<span style="font:7.0pt "Times New Roman""> 
</span></span></span><spanstyle="color:black">However the performance increase is not so good when number of
clients-threadsincrease. </span><p class="MsoPlainText" style="margin-left:66.0pt"><span style="color:black">The reason
forit can be that at higher number of clients/threads, there are other blocking factors(other LWLocks, I/O) that limit
thebenefit of moving buffers to freelist</span><p class="MsoPlainText"
style="margin-left:.5in;text-indent:-.25in;mso-list:l1level1 lfo1"><span style="color:black"><span
style="mso-list:Ignore">2.<spanstyle="font:7.0pt "Times New Roman"">  </span></span></span><span
style="color:black">Forscale factor 1200, With 10G Shared buffers, </span><p class="MsoPlainText"
style="margin-left:66.0pt;text-indent:-.25in;mso-list:l3level1 lfo3"><span style="color:black"><span
style="mso-list:Ignore">a.<spanstyle="font:7.0pt "Times New Roman"">  </span></span></span><span
style="color:black">Performanceincrease is observed for 8 clients/8 threads reading</span><p class="MsoPlainText"
style="margin-left:66.0pt;text-indent:-.25in;mso-list:l3level1 lfo3"><span style="color:black"><span
style="mso-list:Ignore">b.<spanstyle="font:7.0pt "Times New Roman"">  </span></span></span><span
style="color:black">Thereis performance dip (3~6%) from 16C onwards. The reasons could be</span><p class="MsoPlainText"
style="margin-left:84.0pt;text-indent:-.25in;mso-list:l2level1 lfo4"><span style="color:black"><span
style="mso-list:Ignore">a.<spanstyle="font:7.0pt "Times New Roman"">  </span></span></span><span
style="color:black">thatwith such a long buffer list, actually taking BufFreeListLock by BGwriter frequently
(bgwrite_delay= 200ms) can add to Concurrency overhead which is overcoming the need for getting </span><p
class="MsoPlainText"style="margin-left:84.0pt"><span style="color:black">buffer from freelist. </span><p
class="MsoPlainText"style="margin-left:84.0pt;text-indent:-.25in;mso-list:l2 level1 lfo4"><span
style="color:black"><spanstyle="mso-list:Ignore">b.<span style="font:7.0pt "Times New Roman""> 
</span></span></span><spanstyle="color:black">The other reason is sometimes it comes to free the buffer which is
alreadyin freelist. It can also add to small overhead as currently to check weather buffer is in freelist, we need to
takeBufFreeListLock</span><p class="MsoPlainText"><span style="color:black"> </span><p class="MsoPlainText"><span
style="color:black">Iwill try to find more reasons for 2b and work to resolve performance dip of 2b.</span><p
class="MsoPlainText"><spanstyle="color:black"> </span><p class="MsoPlainText"><span style="color:black">Any suggestions
willbe really helpful to proceed and crack this problem.</span><p class="MsoPlainText"><span
style="color:black"> </span><pclass="MsoPlainText"><span style="color:black">With Regards,</span><p
class="MsoPlainText"><spanstyle="color:black">Amit Kapila.</span><p class="MsoPlainText"><span
style="color:black"> </span><pclass="MsoPlainText"><span style="color:black"> </span><p class="MsoPlainText"><span
style="color:black"> </span><pclass="MsoPlainText"><span style="color:black"> </span></div>