Re: StrategyGetBuffer optimization, take 2 - Mailing list pgsql-hackers

<div dir="ltr">Performance testing this patch is a real bugaboo for me; the VMs I have to work with are too unstable to
giveuseful results :-(.  Need to scrounge up a doner box somewhere...</div><div class="gmail_extra"><br /><br /><div
class="gmail_quote">OnTue, Aug 13, 2013 at 12:26 AM, Amit Kapila <span dir="ltr"><<a
href="mailto:amit.kapila16@gmail.com"target="_blank">amit.kapila16@gmail.com</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">Merlin Moncure
wrote:<br/> On Wed, Aug 7, 2013 at 11:52 PM, Amit Kapila<br /></div><amit(dot)kapila(at)huawei(dot)com> wrote:<br
/><divclass="im">>>> -----Original Message-----<br /> >>> From:
pgsql-hackers-owner(at)postgresql(dot)org[mailto:<a href="mailto:pgsql-hackers-">pgsql-hackers-</a><br /> >>>
owner(at)postgresql(dot)org]On Behalf Of Merlin Moncure<br /> >>> Sent: Thursday, August 08, 2013 12:09 AM<br
/>>>> To: Andres Freund<br /> >>> Cc: PostgreSQL-development; Jeff Janes<br /> >>> Subject:
Re:[HACKERS] StrategyGetBuffer optimization, take 2<br /> >>><br /></div>>>> On Wed, Aug 7, 2013 at
12:07PM, Andres Freund <andres(at)2ndquadrant(dot)com><br /><div class="im">>>> wrote:<br />
>>>> On 2013-08-07 09:40:24 -0500, Merlin Moncure wrote:<br /><br /></div><div class="im">>>> I
havesome very strong evidence that the problem is coming out of the<br /> >>> buffer allocator.  Exhibit A is
thatvlad's presentation of the<br /> >>> problem was on a read only load (if not allocator lock, then
what?).<br/> >>> Exhibit B is that lowering shared buffers to 2gb seems to have (so<br /> >>> far, 5
daysin) fixed the issue.  This problem shows up on fast<br /> >>> machines with fast storage and lots of
cores. So what I think is<br /> >>> happening is that usage_count starts creeping up faster than it gets<br />
>>>cleared by the sweep with very large buffer settings which in turn<br /> >>> causes the 'problem'
buffersto be analyzed for eviction more often.<br /> ><br /> >>   Yes one idea which was discussed previously
isto not increase usage<br /> >> count, every time buffer is pinned.<br /> >>   I am also working on some
ofthe optimizations on similar area, which you<br /> >> can refer here:<br /> >><br /> >> <a
href="http://www.postgresql.org/message-id/006e01ce926c$c7768680$56639380$@kapila@"
target="_blank">http://www.postgresql.org/message-id/006e01ce926c$c7768680$56639380$@kapila@</a><br/> >> <a
href="http://huawei.com"target="_blank">huawei.com</a><br /><br /> > yup -- just took a quick look at your proposed
patch. You're<br /> > attacking the 'freelist' side of buffer allocation where my stripped<br /> > down patch
addressesissues with the clocksweep.  I think this is a<br /> > good idea but more than I wanted to get into
personally.<br/><br /> > Good news is that both patches should essentially bolt on together<br /> > AFAICT.<br
/><br/></div>True, I also think so as both are trying to reduce contention in same area.<br /><div class="im"><br />
> I propose we do a bit of consolidation of performance testing<br /> > efforts and run tests with patch A, B,
andAB in various scenarios.  I<br /> > have a 16 core vm (4gb ram) that I can test with and want to start<br /> >
withsay 2gb database 1gb shared_buffers high concurrency test and see<br /> > how it burns in.  What do you
think?<br/><br /></div>I think this can mainly benefit with large data  and shared buffers (><br /> 10G), last year
alsoI had ran few tests with similar idea's but<br /> didn't get much<br /> in with less shared buffers.<br /><div
class="im"><br/> >  Are you at a point where we can<br /> > run some tests?<br /><br /></div>Not now, but I will
tryto run before/during next CF.<br /><br /><br /> With Regards,<br /> Amit Kapila.<br /> EnterpriseDB: <a
href="http://www.enterprisedb.com"target="_blank">http://www.enterprisedb.com</a><br /></blockquote></div><br /></div>