Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ucz1A-0003gp-S2 for pgsql-hackers@arkaria.postgresql.org; Thu, 16 May 2013 14:18:53 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Ucz18-0006hS-Po for pgsql-hackers@arkaria.postgresql.org; Thu, 16 May 2013 14:18:50 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ucz16-0006hL-Dm for pgsql-hackers@postgreSQL.org; Thu, 16 May 2013 14:18:49 +0000 Received: from szxga01-in.huawei.com ([119.145.14.64]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ucz0t-0003TM-SM for pgsql-hackers@postgreSQL.org; Thu, 16 May 2013 14:18:47 +0000 Received: from 172.24.2.119 (EHLO szxeml211-edg.china.huawei.com) ([172.24.2.119]) by szxrg01-dlp.huawei.com (MOS 4.3.4-GA FastPath queued) with ESMTP id BCF93943; Thu, 16 May 2013 22:18:24 +0800 (CST) Received: from SZXEML448-HUB.china.huawei.com (10.82.67.191) by szxeml211-edg.china.huawei.com (172.24.2.182) with Microsoft SMTP Server (TLS) id 14.1.323.7; Thu, 16 May 2013 22:18:06 +0800 Received: from blrprnc09ns (10.18.96.98) by szxeml448-hub.china.huawei.com (10.82.67.191) with Microsoft SMTP Server id 14.1.323.7; Thu, 16 May 2013 22:18:03 +0800 From: Amit Kapila To: "'Greg Smith'" CC: "'PostgreSQL-development'" References: <002f01ce50a8$e057c7a0$a10756e0$@kapila@huawei.com> <51928CDA.7030009@2ndQuadrant.com> <003101ce5119$5bed3e70$13c7bb50$@kapila@huawei.com> In-Reply-To: <003101ce5119$5bed3e70$13c7bb50$@kapila@huawei.com> Subject: Re: Move unused buffers to freelist Date: Thu, 16 May 2013 19:48:02 +0530 Message-ID: <00cc01ce5240$2da362b0$88ea2810$@kapila@huawei.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_00CD_01CE526E.475B9EB0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Ac5Q1yPZT7QQihM4TzSJjDOZQivbdQAPfNogAEmclHA= Content-Language: en-us x-cr-hashedpuzzle: Cf1f CsR0 DKMZ Fp3c IQAQ JTLl MaW8 OjNx PxsR P1tQ P/yI Qq9R S0/m TDgn TUwP T43e; 2; ZwByAGUAZwBAADIAbgBkAHEAdQBhAGQAcgBhAG4AdAAuAGMAbwBtADsAcABnAHMAcQBsAC0AaABhAGMAawBlAHIAcwBAAHAAbwBzAHQAZwByAGUAcwBxAGwALgBvAHIAZwA=; Sosha1_v1; 7; {4847FA97-871C-48B8-8138-D8845EE2D37E}; YQBtAGkAdAAuAGsAYQBwAGkAbABhAEAAaAB1AGEAdwBlAGkALgBjAG8AbQA=; Thu, 16 May 2013 14:17:57 GMT; UgBFADoAIABbAEgAQQBDAEsARQBSAFMAXQAgAE0AbwB2AGUAIAB1AG4AdQBzAGUAZAAgAGIAdQBmAGYAZQByAHMAIAB0AG8AIABmAHIAZQBlAGwAaQBzAHQA x-cr-puzzleid: {4847FA97-871C-48B8-8138-D8845EE2D37E} X-Originating-IP: [10.18.96.98] X-CFilter-Loop: Reflected X-Pg-Spam-Score: 1.2 (+) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org ------=_NextPart_000_00CD_01CE526E.475B9EB0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_00CE_01CE526E.475B9EB0" ------=_NextPart_001_00CE_01CE526E.475B9EB0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit On Wednesday, May 15, 2013 8:38 AM Amit Kapila wrote: > On Wednesday, May 15, 2013 12:44 AM Greg Smith wrote: > > On 5/14/13 9:42 AM, Amit Kapila wrote: > > > In the attached patch, bgwriter/checkpointer moves unused > > (usage_count > > > =0 && refcount = 0) buffer's to end of freelist. I have implemented > a > > > new API StrategyMoveBufferToFreeListEnd() to > > > > There's a comment in the new function: > > > > It is possible that we are told to put something in the freelist that > > is already in it; don't screw up the list if so. > > > > I don't see where the code does anything to handle that though. What > > was your intention here? > > The intention is that put the entry in freelist only if it is not in > freelist which is accomplished by check > If (buf->freeNext == FREENEXT_NOT_IN_LIST). Every entry when removed > from > freelist, buf->freeNext is marked as FREENEXT_NOT_IN_LIST. > Code Reference (last line): > StrategyGetBuffer() > { > .. > .. > while (StrategyControl->firstFreeBuffer >= 0) > { > buf = &BufferDescriptors[StrategyControl- > >firstFreeBuffer]; > Assert(buf->freeNext != FREENEXT_NOT_IN_LIST); > > /* Unconditionally remove buffer from freelist */ > StrategyControl->firstFreeBuffer = buf->freeNext; > buf->freeNext = FREENEXT_NOT_IN_LIST; > > ... > } > > Also the same check exists in StrategyFreeBuffer(). > > > This area has always been the tricky part of the change. If you do > > something complicated when adding new entries, like scanning the > > freelist for duplicates, you run the risk of holding BufFreelistLock > > for > > too long. > > Yes, this is true and I had tried to hold this lock for minimal time. > In this patch, it holds BufFreelistLock only to put the unused buffer > at end > of freelist. > > > To try and see that in benchmarks, I would use a small > > database scale (I typically use 100 for this type of test) and a > large > > number of clients. I shall try this test, do you have any suggestions for shred buffers and number of clients for 100 scale factor? > >"-M prepared" would help get a higher transaction > > rate out of the hardware too. It might take a server with a large > core > > count to notice any issues with holding the lock for too long though. > > This is good idea, I shall take another set of readings with "-M > prepared" > > > Instead you might just invalidate buffers before they go onto the > list. > > Doing that will then throw away usefully cached data though. > > Yes, if we invalidate buffers, it might throw away usefully cached data > especially when working set just a tiny bit smaller than > shared_buffers. > This is pointed by Robert in his mail > http://www.postgresql.org/message- > id/CA+TgmoYhWsz__KtSxm6BuBirE7VR6Qqc_COkbE > ZTQpk8oom3CA@mail.gmail.com > > > > To try and optimize both insertion speed and retaining cached data, > > I think by the method proposed by patch it takes care of both, because > it > directly puts free buffer at end of freelist and > because it doesn't invalidate the buffers it can retain cached data for > longer period. > Do you see any flaw with current approach? > > > I > > was thinking about using a hash table for the free buffers, instead > of > > the simple linked list approach used in the code now. > > Okay, we can try different methods for maintaining free buffers if we > find > current approach doesn't turn out to be good. > > > Also: check the formatting on the additions to in bufmgr.c, I > noticed > > a > > spaces vs. tabs difference on lines 35/36 of your patch. > > Thanks for pointing it, I shall send an updated patch along with next > set of > performance data. Further Performance Data: Below data is for average 3 runs of 20 minutes Scale Factor - 1200 Shared Buffers - 7G 8C-8T 16C-16T 32C-32T 64C-64T HEAD 1739 1461 1578 1609 After Patch 4029 1924 1743 1706 Scale Factor - 1200 Shared Buffers - 10G 8C-8T 16C-16T 32C-32T 64C-64T HEAD 2004 2270 2195 2173 After Patch 2298 2172 2111 2044 Detailed data of 3 runs is attached with mail. Observations : 1. For scale factor 1200, With 5G and 7G Shared buffers, a. there is reasonably good performance after patch (>50%). b. However the performance increase is not so good when number of clients-threads increase. The reason for it can be that at higher number of clients/threads, there are other blocking factors(other LWLocks, I/O) that limit the benefit of moving buffers to freelist 2. For scale factor 1200, With 10G Shared buffers, a. Performance increase is observed for 8 clients/8 threads reading b. There is performance dip (3~6%) from 16C onwards. The reasons could be a. that with 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 buffer from freelist. b. The other reason is sometimes it comes to free the buffer which is already in freelist. It can also add to small overhead as currently to check weather buffer is in freelist, we need to take BufFreeListLock I will try to find more reasons for 2b and work to resolve performance dip of 2b. Any suggestions will be really helpful to proceed and crack this problem. With Regards, Amit Kapila. ------=_NextPart_001_00CE_01CE526E.475B9EB0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

On Wednesday,= May 15, 2013 8:38 AM Amit Kapila wrote:

> On Wednesday, May 15, 2013 12:44 AM Greg Smith wrote:

> > On 5/14/13 9:42 AM, Amit Kapila wrote:<= o:p>

> > > In the attached patch,= bgwriter/checkpointer moves unused

&= gt; > (usage_count

> > > = =3D0 && refcount =3D 0) buffer's to end of freelist. I have impleme= nted

> a

> > > new API StrategyMoveBufferToFreeListEnd() to

> >

> > There's a comment in the new function:

> >

> &= gt; It is possible that we are told to put something in the freelist that

> > is already in it; don't scre= w up the list if so.

> >

> > I don't see where the code does a= nything to handle that though.  What

> > was your intention here?

>

> The intention is that p= ut the entry in freelist only if it is not in

> freelist which is accomplished by check

> If (buf->freeNext =3D=3D FREENEXT_NOT_IN_LIST). Ev= ery entry when removed

> from=

> freelist, buf->freeNext is marked= as FREENEXT_NOT_IN_LIST.

> Code R= eference (last line):

> StrategyGe= tBuffer()

> {

> ..

> ..

> while (StrategyControl->firstFre= eBuffer >=3D 0)

>   &= nbsp;     {

= >            &nbs= p;    buf =3D &BufferDescriptors[StrategyControl-

> >firstFreeBuffer];<= /p>

>        &= nbsp;        Assert(buf->freeNex= t !=3D FREENEXT_NOT_IN_LIST);

>

>      &nb= sp;          /* Unconditi= onally remove buffer from freelist */

>            &nb= sp;    StrategyControl->firstFreeBuffer =3D buf->= freeNext;

>    &nbs= p;            b= uf->freeNext =3D FREENEXT_NOT_IN_LIST;

>

> ...

> }

>

> Also the same check exists in Strat= egyFreeBuffer().

>

=

> > This area has always been the tricky part= of the change.  If you do

> = > something complicated when adding new entries, like scanning the<= /o:p>

> > freelist for duplicates, you run= the risk of holding BufFreelistLock

= > > for

> > too long.

>

> Yes, this is true and I had tried to hold this lock for minimal ti= me.

> In this patch, it holds BufF= reelistLock only to put the unused buffer

> at end

> of freelist.

>

> > To try and see that in benchmarks, I would use a small<= /o:p>

> > database scale (I typically use = 100 for this type of test) and a

>= large

> > number of clients.

 = ;

I sha= ll try this test, do you have any suggestions for shred buffers and number = of clients for 100 scale factor?

 

> >"-M prepared&= quot; would help get a higher transaction

> > rate out of the hardware too.  It might take a server w= ith a large

> core

<= p class=3DMsoPlainText>> > count to notice any issues with holding th= e lock for too long though.

>

> This is good idea, I shall take ano= ther set of readings with "-M

&g= t; prepared"

>

> > Instead you might just invalidate buffer= s before they go onto the

> list.<= o:p>

> >   Doing that will= then throw away usefully cached data though.

>

> Yes, if we invali= date buffers, it might throw away usefully cached data

> especially when working set just a tiny bit smaller = than

> shared_buffers.<= /p>

> This is pointed by Robert in his mail<= /o:p>

> http://www.postgresql.org/message-

> id/CA+TgmoYhWsz__KtSxm6BuBirE7VR6Q= qc_COkbE

> ZTQpk8oom3CA@mail.gmail= .com

>

>

> > To try an= d optimize both insertion speed and retaining cached data,

>

> I th= ink by the method proposed by patch it takes care of both, because

> it

> directly puts free buffer at end of freelist and

> because it doesn't invalidate the buffers it can ret= ain cached data for

> longer perio= d.

> Do you see any flaw with curr= ent approach?

>

> > I

>= > was thinking about using a hash table for the free buffers, instead

> of

> > the simple linked list approach used in the code now.

>

> Okay, we can try different methods for maintaining free buffers if= we

> find

> current approach doesn't turn out to be good.

>

&= gt; > Also:  check the formatting on the additions to in bufmgr.c, = I

> noticed

> > a

> >= spaces vs. tabs difference on lines 35/36 of your patch.

>

> Thank= s for pointing it, I shall send an updated patch along with next=

> set of

> performance data.

 

 

<= span style=3D'color:black'>Further Performance Data:

<= p class=3DMsoPlainText> =

Below data is for a= verage 3 runs of 20 minutes

Scale Factor   - 1200=

Shared Buffers - 7G=

<= o:p> 

 

           =         8C-8T    &n= bsp;           16C-16T&nb= sp;             = ; 32C-32T          &= nbsp; 64C-64T

HEAD          = ;     1739       &nb= sp;           1461  =             &nb= sp;  1578          &= nbsp;      1609

After Patch    &= nbsp;   4029         = ;          1924   &n= bsp;            &nbs= p;1743           &nb= sp;     1706

 

 

Scale Factor   - 1200

Shar= ed Buffers – 10G

 

         =           8C-8T  &nb= sp;            = 16C-16T            =    32C-32T        &n= bsp;   64C-64T

HEAD        =        2004     &nbs= p;             2270&= nbsp;           &nbs= p;    2195        &n= bsp;        2173

After Patch  &n= bsp;     2298       =             2172 &nb= sp;            =   2111         &nbs= p;       2044

 

 

Detailed data of 3 ru= ns is attached with mail.

 

Observations :

 

1. = For scale facto= r 1200, With 5G and 7G Shared buffers,

a.  <= /span>there is reasonably good= performance after patch (>50%).

b.  However the performance incr= ease is not so good when number of clients-threads increase.

The reason for it can be that at higher number of clients/thr= eads, there are other blocking factors(other LWLocks, I/O) that limit the b= enefit of moving buffers to freelist

2.  For scale factor 1200, With 1= 0G Shared buffers,

a.  Performance increase is observed for 8 clien= ts/8 threads reading

b.  There is performance dip (3~6%) from 16C on= wards. The reasons could be

a.  that with such a long buffer list, a= ctually taking BufFreeListLock by BGwriter frequently (bgwrite_delay =3D 20= 0ms) can add to Concurrency overhead which is overcoming the need for getti= ng

buffer from freelist.

=

b.&= nbsp; The other = reason is sometimes it comes to free the buffer which is already in freelis= t. It can also add to small overhead as currently to check weather buffer i= s in freelist, we need to take BufFreeListLock

 

I will try to find more r= easons for 2b and work to resolve performance dip of 2b.<= /p>

 

Any suggestions= will be really helpful to proceed and crack this problem.

 <= /span>

With Regards,=

A= mit Kapila.

 

 

 

<= span style=3D'color:black'> 

= ------=_NextPart_001_00CE_01CE526E.475B9EB0-- ------=_NextPart_000_00CD_01CE526E.475B9EB0 Content-Type: text/html; name="move_unused_buffers_to_freelist_1.htm" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="move_unused_buffers_to_freelist_1.htm"
Configuration Used
O/S Suse-11
RAM 24GB
Number of Cores 8
Server Conf: checkpoint_segments - 256, checkpoint_timeout =3D 25 min, synchronous_commit=3Doff
shared_buffers 5GB shared_buffers 7G shared_buffer 10G
pgbench select-only
pgbench-Scale Factor 1200
Each Run unit tps
HEAD HEAD HEAD
Runs/Concurrency 8C-8T 16C-16T 32C-32T 64C-64T Runs/Concurrency 8C-8T 16C-16T 32C-32T 64C-64T Runs/Concurrency 8C-8T 16C-16T 32C-32T 64C-64T
Run-1 9664 7425 5106 2570 Run-= 1 2026 1639 1670 1711 Run-= 1 1138 2219 2194 2112
Run-2 13081 9009 4874 2713 Run-= 2 1631 1422 1542 1543 Run-= 2 2432 2280 2139 2208
Run-3 13246 8932 4988 2990 Run-= 3 1560 1324 1524 1573 Run-= 3 2443 2313 2254 2199
Average 11997 8455 4989 2757 Aver= age 1739 1461 1578 1609 Aver= age 2004 2270 2195 2173
After Patch After Patch After Patch
Runs/Concurrency 8C-8T 16C-16T 32C-32T 64C-64T Runs/Concurrency 8C-8T 16C-16T 32C-32T 64C-64T Runs/Concurrency 8C-8T 16C-16T 32C-32T 64C-64T
Run-1 16239 11539 7395 2512 Run-= 1 4039 1599 1437 1424 Run-= 1 2298 2199 2062 2015
Run-2 21408 15090 8923 2870 Run-= 2 2824 2004 1780 1685 Run-= 2 2253 2189 2159 2057
Run-3 21776 13261 8846 3083 Run-= 3 5224 2170 2013 2011 Run-= 3 2344 2128 2114 2060
Average 19807 13296 8388 2821 Aver= age 4029 1924 1743 1706 Aver= age 2298 2172 2111 2044
Diff In % 65.09 57.25 68.12 2.3 Diff In % 131.68 31.69 10.45 6.02 Diff In % 14.67 -4.32 -3.83 -5.93
= ------=_NextPart_000_00CD_01CE526E.475B9EB0 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers ------=_NextPart_000_00CD_01CE526E.475B9EB0--