Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UkUCj-0002Cq-7C for pgsql-hackers@arkaria.postgresql.org; Thu, 06 Jun 2013 07:01:49 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UkUCh-0000c0-K8 for pgsql-hackers@arkaria.postgresql.org; Thu, 06 Jun 2013 07:01:47 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UkUCe-0000bs-6c for pgsql-hackers@postgresql.org; Thu, 06 Jun 2013 07:01:44 +0000 Received: from szxga02-in.huawei.com ([119.145.14.65]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UkUCV-0006nv-Gq for pgsql-hackers@postgresql.org; Thu, 06 Jun 2013 07:01:43 +0000 Received: from 172.24.2.119 (EHLO szxeml212-edg.china.huawei.com) ([172.24.2.119]) by szxrg02-dlp.huawei.com (MOS 4.3.4-GA FastPath queued) with ESMTP id BCW79646; Thu, 06 Jun 2013 15:01:29 +0800 (CST) Received: from SZXEML417-HUB.china.huawei.com (10.82.67.156) by szxeml212-edg.china.huawei.com (172.24.2.181) with Microsoft SMTP Server (TLS) id 14.1.323.7; Thu, 6 Jun 2013 15:01:28 +0800 Received: from blrprnc09ns (10.18.96.98) by szxeml417-hub.china.huawei.com (10.82.67.156) with Microsoft SMTP Server id 14.1.323.7; Thu, 6 Jun 2013 15:01:23 +0800 From: Amit Kapila To: "'Robert Haas'" CC: "'Greg Smith'" , "'PostgreSQL-development'" References: <51928CDA.7030009@2ndQuadrant.com> <5194eb32.85d60e0a.1c17.2253SMTPIN_ADDED_BROKEN@mx.google.com> <519b1d5f.9025b40a.2d07.ffffed08SMTPIN_ADDED_BROKEN@mx.google.com> <519f060c.cade440a.493f.ffffb080SMTPIN_ADDED_BROKEN@mx.google.com> In-Reply-To: Subject: Re: Move unused buffers to freelist Date: Thu, 6 Jun 2013 12:31:21 +0530 Message-ID: <005601ce6283$a814b410$f83e1c30$@kapila@huawei.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0057_01CE62B1.C1CCF010" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Ac5bppJykbagBGo4SY20HX8niyXE6AGy/UhA Content-Language: en-us X-Originating-IP: [10.18.96.98] X-CFilter-Loop: Reflected X-Pg-Spam-Score: 0.6 (/) 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_0057_01CE62B1.C1CCF010 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit On Tuesday, May 28, 2013 6:54 PM Robert Haas wrote: > >> Instead, I suggest modifying BgBufferSync, specifically this part > right > >> here: > >> > >> else if (buffer_state & BUF_REUSABLE) > >> reusable_buffers++; > >> > >> What I would suggest is that if the BUF_REUSABLE flag is set here, > use > >> that as the trigger to do StrategyMoveBufferToFreeListEnd(). > > > > I think at this point also we need to lock buffer header to check > refcount > > and usage_count before moving to freelist, or do you think it is not > > required? > > If BUF_REUSABLE is set, that means we just did exactly what you're > saying. Why do it twice? Even if we just did it, but we have released the buf header lock, so theoretically chances are there that backend can increase the count, however still it will be protected by check in StrategyGetBuffer(). As there is a very rare chance of it, so doing without buffer header lock might not cause any harm. Modified patch to address the same is attached with mail. Performance Data ------------------- As far as I have noticed, performance data for this patch depends on 3 factors 1. Pre-loading of data in buffers, so that buffers holding pages should have some usage count before running pgbench. Reason is it might be creating difference in performance of clock-sweep 2. Clearing of pages in OS cache before running pgbench with different patch, it can create difference because when we run pgbench with or without patch, it can access pages already cached due to previous runs which causes variation in performance. 3. Scale factor and shared buffer configuration To avoid above 3 factors in test readings, I used below steps: 1. Initialize the database with scale factor such that database size + shared_buffers = RAM (shared_buffers = 1/4 of RAM). For example: Example -1 if RAM = 128G, then initialize db with scale factor = 6700 and shared_buffers = 32GB. Database size (98 GB) + shared_buffers (32GB) = 130 (which is approximately equal to total RAM) Example -2 (this is based on your test m/c) If RAM = 64GB, then initialize db with scale factor = 3400 and shared_buffers = 16GB. 2. reboot m/c 3. Load all buffers with data (tables/indexes of pgbench) using pg_prewarm. I had loaded 3 times, so that usage count of buffers will be approximately 3. Used file load_all_buffers.sql attached with this mail 4. run 3 times pgbench select-only case for 10 or 15 minutes without patch 5. reboot m/c 6. Load all buffers with data (tables/indexes of pgbench) using pg_prewarm. I had loaded 3 times, so that usage count of buffers will be approximately 3. Used file load_all_buffers.sql attached with this mail 7. run 3 times pgbench select-only case for 10 or 15 minutes with patch Using above steps, I had taken performance data on 2 different m/c's Configuration Details O/S - Suse-11 RAM - 128GB Number of Cores - 16 Server Conf - checkpoint_segments = 300; checkpoint_timeout = 15 min, synchronous_commit = 0FF, shared_buffers = 32GB, AutoVacuum=off Pgbench - Select-only Scalefactor - 1200 Time - Each run is of 15 mins Below data is for average of 3 runs 16C-16T 32C-32T 64C-64T HEAD 4391 3971 3464 After Patch 6147 5093 3944 Detailed data of each run is attached with mail in file move_unused_buffers_to_freelist_v2.htm Below data is for 1 run of half hour on same configuration 16C-16T 32C-32T 64C-64T HEAD 4377 3861 3295 After Patch 6542 4770 3504 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 10 mins Below data is for average 3 runs of 10 minutes 8C-8T 16C-16T 32C-32T 64C-64T 128C-128T 256C-256T HEAD 58837 56740 19390 5681 3191 2160 After Patch 59482 56936 25070 7655 4166 2704 Detailed data of each run is attached with mail in file move_unused_buffers_to_freelist_v2.htm Below data is for 1 run of half hour on same configuration 32C-32T HEAD 17703 After Patch 20586 I had run these tests multiple times to ensure the correctness. I think last time why it didn't show performance improvement in your runs is because the way we both are running pgbench is different. This time, I have detailed the steps I have used to collect performance data. With Regards, Amit Kapila. ------=_NextPart_000_0057_01CE62B1.C1CCF010 Content-Type: application/octet-stream; name="move_unused_buffers_to_freelist_v2.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="move_unused_buffers_to_freelist_v2.patch" *** a/src/backend/storage/buffer/bufmgr.c=0A= --- b/src/backend/storage/buffer/bufmgr.c=0A= ***************=0A= *** 1390,1395 **** BgBufferSync(void)=0A= --- 1390,1396 ----=0A= /* Variables for final smoothed_density update */=0A= long new_strategy_delta;=0A= uint32 new_recent_alloc;=0A= + volatile BufferDesc *bufHdr;=0A= =20=20=0A= /*=0A= * Find out where the freelist clock sweep currently is, and how many=0A= ***************=0A= *** 1575,1580 **** BgBufferSync(void)=0A= --- 1576,1582 ----=0A= {=0A= int buffer_state =3D SyncOneBuffer(next_to_clean, true);=0A= =20=20=0A= + bufHdr =3D &BufferDescriptors[next_to_clean];=0A= if (++next_to_clean >=3D NBuffers)=0A= {=0A= next_to_clean =3D 0;=0A= ***************=0A= *** 1592,1598 **** BgBufferSync(void)=0A= --- 1594,1603 ----=0A= }=0A= }=0A= else if (buffer_state & BUF_REUSABLE)=0A= + {=0A= reusable_buffers++;=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=0A= }=0A= =20=20=0A= BgWriterStats.m_buf_written_clean +=3D num_written;=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,296 ----=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= + /*=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= + }=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_0057_01CE62B1.C1CCF010 Content-Type: text/html; name="move_unused_buffers_to_freelist_v2.htm" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="move_unused_buffers_to_freelist_v2.htm"
Configuration Used
O/S Suse-11
RAM 128GB
Number of Cores<= /td> 16
Server Conf: checkpoint_segments - 300, checkpoint_timeout =3D 15 min, checkpoint_completion_target=3D0.9 synchronous_commit=3Doff, AutoVacuum=3Doff
shared_buffers 32GB
pgbench select-only
pgbench-Scale Factor 6700
HEAD
Runs/Concurrency 16C-16T 32C-32T 64C-64T
Run-= 1 5354 4485 3607
Run-= 2 4672 4376 3461
Run-= 3 3147 3052 3325
Aver= age 4391 3971 3464
After Patch
Runs/Concurrency 16C-16T 32C-32T 64C-64T
Run-= 1 5376 4449 3606
Run-= 2 5227 4521 3474
Run-= 3 7838 6310 4753
Aver= age 6147 5093 3944
Diff In % 39.99 28.25 13.85
Configuration Used
O/S Suse-11
RAM 24GB
Number of Cores<= /td> 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 128C-128T 256C-256T
Run-= 1 59034 56109 15374 7220 4441 3110
Run-= 2 58604 57056 17698 5067 2621 1730
Run-= 3 58874 57057 25098 4756 2511 1640
Aver= age 58837 56740 19390 5681 3191 2160
After Patch
Runs/Concurrency 8C-8T 16C-16T 32C-32T 64C-64T 128C-128T 256C-256T
Run-= 1 59285 55970 20894 9263 5655 3665
Run-= 2 59521 57246 23380 6841 3770 2351
Run-= 3 59642 57592 30937 6863 3074 2097
Aver= age 59482 56936 25070 7655 4166 2704
Diff In % 1.09 0.35 29.29 34.74 30.55 25.18
= ------=_NextPart_000_0057_01CE62B1.C1CCF010 Content-Type: application/octet-stream; name="load_all_buffers.sql" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="load_all_buffers.sql" -- create extension pg_prewarm; select pg_prewarm('pgbench_accounts', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_accounts', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_accounts', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_branches', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_branches', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_branches', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_tellers', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_tellers', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_tellers', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_accounts_pkey', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_accounts_pkey', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_accounts_pkey', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_branches_pkey', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_branches_pkey', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_branches_pkey', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_tellers_pkey', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_tellers_pkey', 'main', 'buffer', NULL, NULL); select pg_prewarm('pgbench_tellers_pkey', 'main', 'buffer', NULL, NULL); ------=_NextPart_000_0057_01CE62B1.C1CCF010 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_0057_01CE62B1.C1CCF010--