Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UcFXy-0004YS-Mq for pgsql-hackers@arkaria.postgresql.org; Tue, 14 May 2013 13:45:43 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UcFXx-0005Zv-Up for pgsql-hackers@arkaria.postgresql.org; Tue, 14 May 2013 13:45:42 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UcFXv-0005Ye-Iy for pgsql-hackers@postgreSQL.org; Tue, 14 May 2013 13:45:40 +0000 Received: from szxga01-in.huawei.com ([119.145.14.64]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UcFXi-0006Sq-DT for pgsql-hackers@postgreSQL.org; Tue, 14 May 2013 13:45:39 +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 BCC95949; Tue, 14 May 2013 21:45:20 +0800 (CST) Received: from SZXEML421-HUB.china.huawei.com (10.82.67.160) by szxeml211-edg.china.huawei.com (172.24.2.182) with Microsoft SMTP Server (TLS) id 14.1.323.7; Tue, 14 May 2013 21:42:37 +0800 Received: from blrprnc09ns (10.18.96.98) by szxeml421-hub.china.huawei.com (10.82.67.160) with Microsoft SMTP Server id 14.1.323.7; Tue, 14 May 2013 21:42:28 +0800 From: Amit Kapila To: "'PostgreSQL-development'" Subject: Move unused buffers to freelist Date: Tue, 14 May 2013 19:12:27 +0530 Message-ID: <002f01ce50a8$e057c7a0$a10756e0$@kapila@huawei.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0030_01CE50D6.FA1003A0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Ac5QqB5g6EWipdghQWuymNvg6s4JAgAADVZg Content-Language: en-us X-Originating-IP: [10.18.96.98] X-CFilter-Loop: Reflected X-Pg-Spam-Score: 0.4 (/) 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_0030_01CE50D6.FA1003A0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0031_01CE50D6.FA1003A0" ------=_NextPart_001_0031_01CE50D6.FA1003A0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit As discussed and concluded in mail thread (http://www.postgresql.org/message-id/006f01ce34f0$d6fa8220$84ef8660$@kapila @huawei.com), for moving unused buffer's to freelist end, I having implemented the idea and taken some performance data. 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 move buffer's to end of freelist. Performance Data : Configuration Details O/S - Suse-11 RAM - 24GB Number of Cores - 8 Server Conf - checkpoint_segments = 256; checkpoint_timeout = 25 min, synchronous_commit = 0FF, shared_buffers = 5GB Pgbench - Select-only Scalefactor - 1200 Time - Each run is of 20 mins Below data is for average 3 runs of 20 minutes 8C-8T 16C-16T 32C-32T 64C-64T HEAD 11997 8455 4989 2757 After Patch 19807 13296 8388 2821 Detailed each run data is attached with mail. This is just the initial data, I will collect more data based on different configuration of shared buffers and other configurations. Feedback/Suggesions? With Regards, Amit Kapila. ------=_NextPart_001_0031_01CE50D6.FA1003A0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

As discussed and conclude= d in mail thread (http://www.postgresql.org/message= -id/006f01ce34f0$d6fa8220$84ef8660$@kapila@huawei.com), for moving unus= ed buffer’s to freelist end,

I havi= ng implemented the idea and taken some performance data.<= /p>

 

 

In the attached patch, bgwriter/checkpointer moves unus= ed (usage_count =3D0 && refcount =3D 0) buffer’s to end of fr= eelist. I have implemented a new API StrategyMoveBufferToFreeListEnd() to <= o:p>

move buffer’s to end of = freelist.

 

Performance Data :

 

Configuration Details

O/S – Suse-11

RAM – 24GB<= o:p>

Number of Cores – 8

Server Conf – checkpoint_segments =3D 256; checkpoin= t_timeout =3D 25 min, synchronous_commit =3D 0FF, shared_buffers =3D 5GB

Pgbench – Select-only

Scalefactor – 1200

= Time – Each run is of 20 mins

&= nbsp;

Below data is for average 3 runs of 20 mi= nutes

 

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

HEAD   &nb= sp;           11997 =             &nb= sp; 8455           &= nbsp;     4989       = ;          2757

After Patch        19= 807            =    13296         &nb= sp;     8388       &= nbsp;         2821

 

Detailed each run = data is attached with mail.

 

<= span style=3D'font-size:10.0pt;font-family:"Arial","sans-serif"'>This is ju= st the initial data, I will collect more data based on different configurat= ion of shared buffers and other configurations.

 

Feedback/Suggesions?

 

= With Rega= rds,

Amit Kapila.

= ------=_NextPart_001_0031_01CE50D6.FA1003A0-- ------=_NextPart_000_0030_01CE50D6.FA1003A0 Content-Type: text/html; name="move_unused_buffers_to_freelist.htm" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="move_unused_buffers_to_freelist.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
pgbench select-only
pgbench-Scale Factor 1200
HEAD
Runs/Concurrency 8C-8T 16C-16T 32C-32T 64C-64T
Run-1 9664 7425 5106 2570
Run-2 13081 9009 4874 2713
Run-3 13246 8932 4988 2990
Average 11997 8455 4989 2757
After Patch
Runs/Concurrency 8C-8T 16C-16T 32C-32T 64C-64T
Run-1 16239 11539 7395 2512
Run-2 21408 15090 8923 2870
Run-3 21776 13261 8846 3083
Average 19807 13296 8388 2821
Diff In % 65.09 57.25 68.12 2.3
= ------=_NextPart_000_0030_01CE50D6.FA1003A0 Content-Type: application/octet-stream; name="move_unsed_buffers_to_freelist.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="move_unsed_buffers_to_freelist.patch" *** a/src/backend/storage/buffer/bufmgr.c=0A= --- b/src/backend/storage/buffer/bufmgr.c=0A= ***************=0A= *** 1674,1685 **** SyncOneBuffer(int buf_id, bool skip_recently_used)=0A= return result;=0A= }=0A= =20=20=0A= ! if (!(bufHdr->flags & BM_VALID) || !(bufHdr->flags & BM_DIRTY))=0A= {=0A= /* It's clean, so nothing to do */=0A= UnlockBufHdr(bufHdr);=0A= return result;=0A= }=0A= =20=20=0A= /*=0A= * Pin it, share-lock it, write it. (FlushBuffer will do nothing if the= =0A= --- 1674,1703 ----=0A= return result;=0A= }=0A= =20=20=0A= ! if (!(bufHdr->flags & BM_VALID))=0A= {=0A= /* It's clean, so nothing to do */=0A= UnlockBufHdr(bufHdr);=0A= return result;=0A= }=0A= + else if (!(bufHdr->flags & BM_DIRTY))=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= + {=0A= + /*=0A= + * It's clean, so nothing to flush;=0A= + * if buffer is unused then move it to freelist=0A= + */=0A= + if (bufHdr->refcount =3D=3D 0 && bufHdr->usage_count =3D=3D 0)=0A= + {=0A= + UnlockBufHdr(bufHdr);=0A= + StrategyMoveBufferToFreeListEnd (bufHdr);=0A= + }=0A= + else=0A= + {=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=0A= + UnlockBufHdr(bufHdr);=0A= + }=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= + return result;=0A= + }=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +=20=0A= =20=20=0A= /*=0A= * Pin it, share-lock it, write it. (FlushBuffer will do nothing if the= =0A= ***************=0A= *** 1693,1698 **** SyncOneBuffer(int buf_id, bool skip_recently_used)=0A= --- 1711,1730 ----=0A= LWLockRelease(bufHdr->content_lock);=0A= UnpinBuffer(bufHdr, true);=0A= =20=20=0A= + /*=0A= + * check if buffer is not used actively by any backend, then move it to= freelist=0A= + */=0A= + LockBufHdr(bufHdr);=20=20=20=20=20=20=20=20=20=20=20=20=0A= + if (bufHdr->refcount =3D=3D 0 && bufHdr->usage_count =3D=3D 0)=0A= + {=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= + UnlockBufHdr(bufHdr);=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=0A= + StrategyMoveBufferToFreeListEnd (bufHdr);=0A= + }=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=0A= + else=0A= + {=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= + UnlockBufHdr(bufHdr);=0A= + }=0A= +=20=0A= return result | BUF_WRITTEN;=0A= }=0A= =20=20=0A= *** a/src/backend/storage/buffer/freelist.c=0A= --- b/src/backend/storage/buffer/freelist.c=0A= ***************=0A= *** 259,264 **** StrategyFreeBuffer(volatile BufferDesc *buf)=0A= --- 259,298 ----=0A= }=0A= =20=20=0A= /*=0A= + * StrategyMoveBufferToFreeListEnd: put a buffer on the end of freelist= =0A= + */=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=0A= + void=0A= + StrategyMoveBufferToFreeListEnd(volatile BufferDesc *buf)=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=0A= + {=0A= + LWLockAcquire(BufFreelistLock, LW_EXCLUSIVE);=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=0A= +=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= + /*=0A= + * It is possible that we are told to put something in the freelist that= =0A= + * is already in it; don't screw up the list if so.=0A= + */=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= + if (buf->freeNext =3D=3D FREENEXT_NOT_IN_LIST)=0A= + {=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= + /*=0A= + * put the buffer on end of list and if list is empty then=0A= + * assign first and last freebuffer with this buffer id.=20=20=20=20= =20=20=20=20=20=20=20=20=20=0A= + */=20=20=20=20=20=0A= + buf->freeNext =3D FREENEXT_END_OF_LIST;=0A= + if (StrategyControl->firstFreeBuffer < 0)=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= + {=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= + StrategyControl->firstFreeBuffer =3D buf->buf_id;=0A= + StrategyControl->lastFreeBuffer =3D buf->buf_id;=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=0A= + LWLockRelease(BufFreelistLock);=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=0A= + return;=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=0A= + }=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=0A= + BufferDescriptors[StrategyControl->lastFreeBuffer].freeNext =3D buf->b= uf_id;=0A= + StrategyControl->lastFreeBuffer =3D buf->buf_id;=0A= + }=20=20=20=20=20=20=20=20=20=0A= +=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= + LWLockRelease(BufFreelistLock);=0A= + }=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +=20=0A= +=20=0A= + /*=0A= * StrategySyncStart -- tell BufferSync where to start syncing=0A= *=0A= * The result is the buffer index of the best buffer to sync first.=0A= *** a/src/include/storage/buf_internals.h=0A= --- b/src/include/storage/buf_internals.h=0A= ***************=0A= *** 185,190 **** extern BufferDesc *LocalBufferDescriptors;=0A= --- 185,191 ----=0A= extern volatile BufferDesc *StrategyGetBuffer(BufferAccessStrategy strate= gy,=0A= bool *lock_held);=0A= extern void StrategyFreeBuffer(volatile BufferDesc *buf);=0A= + extern void StrategyMoveBufferToFreeListEnd(volatile BufferDesc *buf);=0A= extern bool StrategyRejectBuffer(BufferAccessStrategy strategy,=0A= volatile BufferDesc *buf);=0A= =20=20=0A= ------=_NextPart_000_0030_01CE50D6.FA1003A0 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_0030_01CE50D6.FA1003A0--