Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kXfz8-00012E-VK for pgsql-hackers@arkaria.postgresql.org; Wed, 28 Oct 2020 07:35:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kXfz7-0007A6-Pf for pgsql-hackers@arkaria.postgresql.org; Wed, 28 Oct 2020 07:35:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kXfz7-00079y-Al for pgsql-hackers@lists.postgresql.org; Wed, 28 Oct 2020 07:35:05 +0000 Received: from forwardcorp1o.mail.yandex.net ([95.108.205.193]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kXfz4-00072A-1Q for pgsql-hackers@postgresql.org; Wed, 28 Oct 2020 07:35:04 +0000 Received: from myt5-23f0be3aa648.qloud-c.yandex.net (myt5-23f0be3aa648.qloud-c.yandex.net [IPv6:2a02:6b8:c12:3e29:0:640:23f0:be3a]) by forwardcorp1o.mail.yandex.net (Yandex) with ESMTP id 5A76E2E174D; Wed, 28 Oct 2020 10:35:00 +0300 (MSK) Received: from myt5-70c90f7d6d7d.qloud-c.yandex.net (myt5-70c90f7d6d7d.qloud-c.yandex.net [2a02:6b8:c12:3e2c:0:640:70c9:f7d]) by myt5-23f0be3aa648.qloud-c.yandex.net (mxbackcorp/Yandex) with ESMTP id elJPBKSNkd-Yxw8drmQ; Wed, 28 Oct 2020 10:35:00 +0300 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yandex-team.ru; s=default; t=1603870500; bh=EdhPGTTOsQn6o2GMoGtDfxuSZ9GEYCeRIz2tITkEd9o=; h=To:In-Reply-To:References:Date:Subject:Cc:Message-Id:From; b=aJpLh7msSucJwCd9677+insqlsNMbgCednp0mbkgMHrfzz7HRPInyZN+4XVHNW/lA qr4BsPZP82ds/b+7DT2E6vaZUk7EPW+6/m+zyls6cQigxuhLBuLZYJaMCQQf4CksFF o6JeaK6zihxQ/mSWPLLO6SQkeVyBLFqfCrEMVJtc= Authentication-Results: myt5-23f0be3aa648.qloud-c.yandex.net; dkim=pass header.i=@yandex-team.ru Received: from dynamic-ekb.dhcp.yndx.net (dynamic-ekb.dhcp.yndx.net [2a02:6b8:0:2807:4158:b938:7981:94db]) by myt5-70c90f7d6d7d.qloud-c.yandex.net (smtpcorp/Yandex) with ESMTPSA id CeXyqVDNsb-Ywmihrqm; Wed, 28 Oct 2020 10:34:59 +0300 (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client certificate not present) From: Andrey Borodin Message-Id: <13D8FD63-559A-4737-B7FD-05288D1CEF8B@yandex-team.ru> Content-Type: multipart/mixed; boundary="Apple-Mail=_0C03404A-C1B1-420C-A748-E676B2005EC2" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\)) Subject: Re: MultiXact\SLRU buffers configuration Date: Wed, 28 Oct 2020 12:34:58 +0500 In-Reply-To: <20201028013651.de5cj2xadgmba5nf@development> Cc: Alexander Korotkov , Anastasia Lubennikova , Daniel Gustafsson , Kyotaro Horiguchi , pgsql-hackers To: Tomas Vondra References: <102F673B-C9C5-4823-A92F-96799FC61ED0@yandex-team.ru> <20200515.090333.24867479329066911.horikyota.ntt@gmail.com> <2ECE132B-C042-4489-930E-DBC5D0DAB84A@yandex-team.ru> <2087E87D-44CA-4443-8E6A-5087F07443F4@yandex-team.ru> <8A94938B-054C-4439-9866-2C220B4D0DD7@yandex-team.ru> <20201028013651.de5cj2xadgmba5nf@development> X-Mailer: Apple Mail (2.3608.120.23.2.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_0C03404A-C1B1-420C-A748-E676B2005EC2 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Tomas, thanks for looking into this! > 28 =D0=BE=D0=BA=D1=82. 2020 =D0=B3., =D0=B2 06:36, Tomas Vondra = =D0=BD=D0=B0=D0=BF=D0=B8=D1=81=D0=B0=D0=BB(= =D0=B0): >=20 >=20 > This thread started with a discussion about making the SLRU sizes > configurable, but this patch version only adds a local cache. Does = this > achieve the same goal, or would we still gain something by having GUCs > for the SLRUs? >=20 > If we're claiming this improves performance, it'd be good to have some > workload demonstrating that and measurements. I don't see anything = like > that in this thread, so it's a bit hand-wavy. Can someone share = details > of such workload (even synthetic one) and some basic measurements? All patches in this thread aim at the same goal: improve performance in = presence of MultiXact locks contention. I could not build synthetical reproduction of the problem, however I did = some MultiXact stressing here [0]. It's a clumsy test program, because = it still is not clear to me which parameters of workload trigger = MultiXact locks contention. In generic case I was encountering other = locks like *GenLock: XidGenLock, MultixactGenLock etc. Yet our = production system encounters this problem approximately once in a month = through this year. Test program locks for share different set of tuples in presence of = concurrent full scans. To produce a set of locks we choose one of 14 bits. If a row number has = this bit set to 0 we add lock it. I've been measuring time to lock all rows 3 time for each of 14 bits, = observing total time to set all locks. During the test I was observing locks in pg_stat_activity, if they did = not contain enough MultiXact locks I was tuning parameters further = (number of concurrent clients, number of bits, select queries etc). Why is it so complicated? It seems that other reproductions of a problem = were encountering other locks. Lets describe patches in this thread from the POV of these test. *** Configurable SLRU buffers for MultiXact members and offsets. =46rom tests it is clear that high and low values for these buffers = affect the test time. Here are time for a one test run with different = offsets and members sizes [1] Our production currently runs with (numbers are pages of buffers) +#define NUM_MXACTOFFSET_BUFFERS 32 +#define NUM_MXACTMEMBER_BUFFERS 64 And, looking back to incidents in summer and fall 2020, seems like it = helped mostly. But it's hard to give some tuning advises based on test results. Values = (32,64) produce 10% better result than current hardcoded values (8,16). = In generic case this is not what someone should tune first. *** Configurable caches of MultiXacts. Tests were specifically designed to beat caches. So, according to test = the bigger cache is - the more time it takes to accomplish the test [2]. Anyway cache is local for backend and it's purpose is deduplication of = written MultiXacts, not enhancing reads. *** Using advantage of SimpleLruReadPage_ReadOnly() in MultiXacts. This simply aligns MultiXact with Subtransactions and CLOG. Other SLRUs = already take advantage of reading SLRU with shared lock. On synthetical tests without background selects this patch adds another = ~4.7% of performance [3] against [4]. This improvement seems consistent = between different parameter values, yet within measurements deviation = (see difference between warmup run [5] and closing run [6]). All in all, these attempts to measure impact are hand-wavy too. But it = makes sense to use consistent approach among similar subsystems = (MultiXacts, Subtrans, CLOG etc). *** Reduce sleep in GetMultiXactIdMembers() on standby. The problem with pg_usleep(1000L) within GetMultiXactIdMembers() = manifests on standbys during contention of MultiXactOffsetControlLock. = It's even harder to reproduce. Yet it seems obvious that reducing sleep to shorter time frame will make = count of sleeping backend smaller. For consistency I've returned patch with SLRU buffer configs to patchset = (other patches are intact). But I'm mostly concerned about patches 1 and = 3. Thanks! Best regards, Andrey Borodin. [0] https://github.com/x4m/multixact_stress [1] = https://github.com/x4m/multixact_stress/blob/master/testresults.txt#L22-L3= 9 [2] = https://github.com/x4m/multixact_stress/blob/master/testresults.txt#L83-L9= 9 [3] = https://github.com/x4m/multixact_stress/blob/master/testresults.txt#L9 [4] = https://github.com/x4m/multixact_stress/blob/master/testresults.txt#L29 [5] = https://github.com/x4m/multixact_stress/blob/master/testresults.txt#L3 [6] = https://github.com/x4m/multixact_stress/blob/master/testresults.txt#L19 --Apple-Mail=_0C03404A-C1B1-420C-A748-E676B2005EC2 Content-Disposition: attachment; filename=v6-0001-Use-shared-lock-in-GetMultiXactIdMembers-for-offs.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v6-0001-Use-shared-lock-in-GetMultiXactIdMembers-for-offs.patch" Content-Transfer-Encoding: quoted-printable =46rom=20bcbd4f6d88d7b6dd9cf779b62e0e0261edadc71b=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Alexander=20Korotkov=20= =0ADate:=20Tue,=2027=20Oct=202020=2019:29:56=20= +0300=0ASubject:=20[PATCH=20v6=201/4]=20Use=20shared=20lock=20in=20= GetMultiXactIdMembers=20for=20offsets=0A=20and=20members=0A=0APreviously=20= the=20read=20of=20multixact=20required=20exclusive=20control=20locks=20= for=20both=0Aoffsets=20and=20members=20SLRUs.=20=20This=20could=20lead=20= to=20the=20excessive=20lock=20contention.=0AThis=20commit=20we=20makes=20= multixacts=20SLRU=20take=20advantage=20of=20SimpleLruReadPage_ReadOnly=0A= similar=20to=20clog,=20commit_ts=20and=20subtrans.=0A=0AIn=20order=20to=20= evade=20extra=20reacquiring=20of=20CLRU=20lock,=20we=20teach=0A= SimpleLruReadPage_ReadOnly()=20to=20take=20into=20account=20the=20= current=20lock=20mode=20and=0Areport=20resulting=20lock=20mode=20back.=0A= =0ADiscussion:=20= https://postgr.es/m/a7f1c4e1-1015-92a4-2bd4-6736bd13d03e%40postgrespro.ru#= c496c4e75fc0605094a0e1f763e6a6ec=0AAuthor:=20Andrey=20Borodin=0A= Reviewed-by:=20Kyotaro=20Horiguchi,=20Daniel=20Gustafsson=0AReviewed-by:=20= Anastasia=20Lubennikova,=20Alexander=20Korotkov=0A---=0A=20= src/backend/access/transam/clog.c=20=20=20=20=20=20|=20=204=20+++-=0A=20= src/backend/access/transam/commit_ts.c=20|=20=204=20+++-=0A=20= src/backend/access/transam/multixact.c=20|=2022=20+++++++++++++++-------=0A= =20src/backend/access/transam/slru.c=20=20=20=20=20=20|=2023=20= +++++++++++++++++------=0A=20src/backend/access/transam/subtrans.c=20=20= |=20=204=20+++-=0A=20src/backend/commands/async.c=20=20=20=20=20=20=20=20= =20=20=20|=20=204=20+++-=0A=20src/backend/storage/lmgr/lwlock.c=20=20=20=20= =20=20|=20=203=20+++=0A=20src/backend/storage/lmgr/predicate.c=20=20=20|=20= =204=20+++-=0A=20src/include/access/slru.h=20=20=20=20=20=20=20=20=20=20=20= =20=20=20|=20=202=20+-=0A=20src/include/storage/lwlock.h=20=20=20=20=20=20= =20=20=20=20=20|=20=202=20++=0A=2010=20files=20changed,=2053=20= insertions(+),=2019=20deletions(-)=0A=0Adiff=20--git=20= a/src/backend/access/transam/clog.c=20= b/src/backend/access/transam/clog.c=0Aindex=20034349aa7b..4c372065de=20= 100644=0A---=20a/src/backend/access/transam/clog.c=0A+++=20= b/src/backend/access/transam/clog.c=0A@@=20-640,10=20+640,11=20@@=20= TransactionIdGetStatus(TransactionId=20xid,=20XLogRecPtr=20*lsn)=0A=20=09= int=09=09=09lsnindex;=0A=20=09char=09=20=20=20*byteptr;=0A=20=09= XidStatus=09status;=0A+=09LWLockMode=09lockmode=20=3D=20LW_NONE;=0A=20=0A= =20=09/*=20lock=20is=20acquired=20by=20SimpleLruReadPage_ReadOnly=20*/=0A= =20=0A-=09slotno=20=3D=20SimpleLruReadPage_ReadOnly(XactCtl,=20pageno,=20= xid);=0A+=09slotno=20=3D=20SimpleLruReadPage_ReadOnly(XactCtl,=20pageno,=20= xid,=20&lockmode);=0A=20=09byteptr=20=3D=20= XactCtl->shared->page_buffer[slotno]=20+=20byteno;=0A=20=0A=20=09status=20= =3D=20(*byteptr=20>>=20bshift)=20&=20CLOG_XACT_BITMASK;=0A@@=20-651,6=20= +652,7=20@@=20TransactionIdGetStatus(TransactionId=20xid,=20XLogRecPtr=20= *lsn)=0A=20=09lsnindex=20=3D=20GetLSNIndex(slotno,=20xid);=0A=20=09*lsn=20= =3D=20XactCtl->shared->group_lsn[lsnindex];=0A=20=0A+=09Assert(lockmode=20= !=3D=20LW_NONE);=0A=20=09LWLockRelease(XactSLRULock);=0A=20=0A=20=09= return=20status;=0Adiff=20--git=20= a/src/backend/access/transam/commit_ts.c=20= b/src/backend/access/transam/commit_ts.c=0Aindex=20= cb8a968801..c19b0b5399=20100644=0A---=20= a/src/backend/access/transam/commit_ts.c=0A+++=20= b/src/backend/access/transam/commit_ts.c=0A@@=20-288,6=20+288,7=20@@=20= TransactionIdGetCommitTsData(TransactionId=20xid,=20TimestampTz=20*ts,=0A= =20=09CommitTimestampEntry=20entry;=0A=20=09TransactionId=20= oldestCommitTsXid;=0A=20=09TransactionId=20newestCommitTsXid;=0A+=09= LWLockMode=09lockmode=20=3D=20LW_NONE;=0A=20=0A=20=09if=20= (!TransactionIdIsValid(xid))=0A=20=09=09ereport(ERROR,=0A@@=20-342,7=20= +343,7=20@@=20TransactionIdGetCommitTsData(TransactionId=20xid,=20= TimestampTz=20*ts,=0A=20=09}=0A=20=0A=20=09/*=20lock=20is=20acquired=20= by=20SimpleLruReadPage_ReadOnly=20*/=0A-=09slotno=20=3D=20= SimpleLruReadPage_ReadOnly(CommitTsCtl,=20pageno,=20xid);=0A+=09slotno=20= =3D=20SimpleLruReadPage_ReadOnly(CommitTsCtl,=20pageno,=20xid,=20= &lockmode);=0A=20=09memcpy(&entry,=0A=20=09=09=20=20=20= CommitTsCtl->shared->page_buffer[slotno]=20+=0A=20=09=09=20=20=20= SizeOfCommitTimestampEntry=20*=20entryno,=0A@@=20-352,6=20+353,7=20@@=20= TransactionIdGetCommitTsData(TransactionId=20xid,=20TimestampTz=20*ts,=0A= =20=09if=20(nodeid)=0A=20=09=09*nodeid=20=3D=20entry.nodeid;=0A=20=0A+=09= Assert(lockmode=20!=3D=20LW_NONE);=0A=20=09= LWLockRelease(CommitTsSLRULock);=0A=20=09return=20*ts=20!=3D=200;=0A=20}=0A= diff=20--git=20a/src/backend/access/transam/multixact.c=20= b/src/backend/access/transam/multixact.c=0Aindex=20= 43653fe572..ccbce90f0e=20100644=0A---=20= a/src/backend/access/transam/multixact.c=0A+++=20= b/src/backend/access/transam/multixact.c=0A@@=20-1237,6=20+1237,7=20@@=20= GetMultiXactIdMembers(MultiXactId=20multi,=20MultiXactMember=20= **members,=0A=20=09MultiXactId=20tmpMXact;=0A=20=09MultiXactOffset=20= nextOffset;=0A=20=09MultiXactMember=20*ptr;=0A+=09LWLockMode=09lockmode=20= =3D=20LW_NONE;=0A=20=0A=20=09debug_elog3(DEBUG2,=20"GetMembers:=20asked=20= for=20%u",=20multi);=0A=20=0A@@=20-1340,12=20+1341,13=20@@=20= GetMultiXactIdMembers(MultiXactId=20multi,=20MultiXactMember=20= **members,=0A=20=09=20*=20time=20on=20every=20multixact=20creation.=0A=20= =09=20*/=0A=20retry:=0A-=09LWLockAcquire(MultiXactOffsetSLRULock,=20= LW_EXCLUSIVE);=0A=20=0A=20=09pageno=20=3D=20= MultiXactIdToOffsetPage(multi);=0A=20=09entryno=20=3D=20= MultiXactIdToOffsetEntry(multi);=0A=20=0A-=09slotno=20=3D=20= SimpleLruReadPage(MultiXactOffsetCtl,=20pageno,=20true,=20multi);=0A+=09= /*=20lock=20is=20acquired=20by=20SimpleLruReadPage_ReadOnly=20*/=0A+=09= slotno=20=3D=20SimpleLruReadPage_ReadOnly(MultiXactOffsetCtl,=20pageno,=0A= +=09=09=09=09=09=09=09=09=09=09multi,=20&lockmode);=0A=20=09offptr=20=3D=20= (MultiXactOffset=20*)=20MultiXactOffsetCtl->shared->page_buffer[slotno];=0A= =20=09offptr=20+=3D=20entryno;=0A=20=09offset=20=3D=20*offptr;=0A@@=20= -1377,7=20+1379,8=20@@=20retry:=0A=20=09=09entryno=20=3D=20= MultiXactIdToOffsetEntry(tmpMXact);=0A=20=0A=20=09=09if=20(pageno=20!=3D=20= prev_pageno)=0A-=09=09=09slotno=20=3D=20= SimpleLruReadPage(MultiXactOffsetCtl,=20pageno,=20true,=20tmpMXact);=0A+=09= =09=09slotno=20=3D=20SimpleLruReadPage_ReadOnly(MultiXactOffsetCtl,=20= pageno,=0A+=09=09=09=09=09=09=09=09=09=09=09=09tmpMXact,=20&lockmode);=0A= =20=0A=20=09=09offptr=20=3D=20(MultiXactOffset=20*)=20= MultiXactOffsetCtl->shared->page_buffer[slotno];=0A=20=09=09offptr=20+=3D=20= entryno;=0A@@=20-1395,14=20+1398,14=20@@=20retry:=0A=20=09=09length=20=3D=20= nextMXOffset=20-=20offset;=0A=20=09}=0A=20=0A+=09Assert(lockmode=20!=3D=20= LW_NONE);=0A=20=09LWLockRelease(MultiXactOffsetSLRULock);=0A=20=0A=20=09= ptr=20=3D=20(MultiXactMember=20*)=20palloc(length=20*=20= sizeof(MultiXactMember));=0A=20=09*members=20=3D=20ptr;=0A=20=0A=20=09/*=20= Now=20get=20the=20members=20themselves.=20*/=0A-=09= LWLockAcquire(MultiXactMemberSLRULock,=20LW_EXCLUSIVE);=0A-=0A+=09= lockmode=20=3D=20LW_NONE;=0A=20=09truelength=20=3D=200;=0A=20=09= prev_pageno=20=3D=20-1;=0A=20=09for=20(i=20=3D=200;=20i=20<=20length;=20= i++,=20offset++)=0A@@=20-1418,7=20+1421,8=20@@=20retry:=0A=20=0A=20=09=09= if=20(pageno=20!=3D=20prev_pageno)=0A=20=09=09{=0A-=09=09=09slotno=20=3D=20= SimpleLruReadPage(MultiXactMemberCtl,=20pageno,=20true,=20multi);=0A+=09=09= =09slotno=20=3D=20SimpleLruReadPage_ReadOnly(MultiXactMemberCtl,=20= pageno,=0A+=09=09=09=09=09=09=09=09=09=09=09=09multi,=20&lockmode);=0A=20= =09=09=09prev_pageno=20=3D=20pageno;=0A=20=09=09}=0A=20=0A@@=20-1441,6=20= +1445,7=20@@=20retry:=0A=20=09=09truelength++;=0A=20=09}=0A=20=0A+=09= Assert(lockmode=20!=3D=20LW_NONE);=0A=20=09= LWLockRelease(MultiXactMemberSLRULock);=0A=20=0A=20=09/*=0A@@=20-2733,6=20= +2738,7=20@@=20find_multixact_start(MultiXactId=20multi,=20= MultiXactOffset=20*result)=0A=20=09int=09=09=09entryno;=0A=20=09int=09=09= =09slotno;=0A=20=09MultiXactOffset=20*offptr;=0A+=09LWLockMode=09= lockmode=20=3D=20LW_NONE;=0A=20=0A=20=09= Assert(MultiXactState->finishedStartup);=0A=20=0A@@=20-2749,10=20= +2755,12=20@@=20find_multixact_start(MultiXactId=20multi,=20= MultiXactOffset=20*result)=0A=20=09=09return=20false;=0A=20=0A=20=09/*=20= lock=20is=20acquired=20by=20SimpleLruReadPage_ReadOnly=20*/=0A-=09slotno=20= =3D=20SimpleLruReadPage_ReadOnly(MultiXactOffsetCtl,=20pageno,=20multi);=0A= +=09slotno=20=3D=20SimpleLruReadPage_ReadOnly(MultiXactOffsetCtl,=20= pageno,=0A+=09=09=09=09=09=09=09=09=09=09multi,=20&lockmode);=0A=20=09= offptr=20=3D=20(MultiXactOffset=20*)=20= MultiXactOffsetCtl->shared->page_buffer[slotno];=0A=20=09offptr=20+=3D=20= entryno;=0A=20=09offset=20=3D=20*offptr;=0A+=09Assert(lockmode=20!=3D=20= LW_NONE);=0A=20=09LWLockRelease(MultiXactOffsetSLRULock);=0A=20=0A=20=09= *result=20=3D=20offset;=0Adiff=20--git=20= a/src/backend/access/transam/slru.c=20= b/src/backend/access/transam/slru.c=0Aindex=2016a7898697..ca0a23ab87=20= 100644=0A---=20a/src/backend/access/transam/slru.c=0A+++=20= b/src/backend/access/transam/slru.c=0A@@=20-487,17=20+487,23=20@@=20= SimpleLruReadPage(SlruCtl=20ctl,=20int=20pageno,=20bool=20write_ok,=0A=20= =20*=20Return=20value=20is=20the=20shared-buffer=20slot=20number=20now=20= holding=20the=20page.=0A=20=20*=20The=20buffer's=20LRU=20access=20info=20= is=20updated.=0A=20=20*=0A-=20*=20Control=20lock=20must=20NOT=20be=20= held=20at=20entry,=20but=20will=20be=20held=20at=20exit.=0A-=20*=20It=20= is=20unspecified=20whether=20the=20lock=20will=20be=20shared=20or=20= exclusive.=0A+=20*=20Control=20lock=20must=20be=20held=20in=20*lock_mode=20= mode,=20which=20may=20be=20LW_NONE.=20=20Control=0A+=20*=20lock=20will=20= be=20held=20at=20exit=20in=20at=20least=20shared=20mode.=20=20Resulting=20= control=20lock=0A+=20*=20mode=20is=20set=20to=20*lock_mode.=0A=20=20*/=0A= =20int=0A-SimpleLruReadPage_ReadOnly(SlruCtl=20ctl,=20int=20pageno,=20= TransactionId=20xid)=0A+SimpleLruReadPage_ReadOnly(SlruCtl=20ctl,=20int=20= pageno,=20TransactionId=20xid,=0A+=09=09=09=09=09=09=20=20=20LWLockMode=20= *lock_mode)=0A=20{=0A=20=09SlruShared=09shared=20=3D=20ctl->shared;=0A=20= =09int=09=09=09slotno;=0A=20=0A=20=09/*=20Try=20to=20find=20the=20page=20= while=20holding=20only=20shared=20lock=20*/=0A-=09= LWLockAcquire(shared->ControlLock,=20LW_SHARED);=0A+=09if=20(*lock_mode=20= =3D=3D=20LW_NONE)=0A+=09{=0A+=09=09LWLockAcquire(shared->ControlLock,=20= LW_SHARED);=0A+=09=09*lock_mode=20=3D=20LW_SHARED;=0A+=09}=0A=20=0A=20=09= /*=20See=20if=20page=20is=20already=20in=20a=20buffer=20*/=0A=20=09for=20= (slotno=20=3D=200;=20slotno=20<=20shared->num_slots;=20slotno++)=0A@@=20= -517,8=20+523,13=20@@=20SimpleLruReadPage_ReadOnly(SlruCtl=20ctl,=20int=20= pageno,=20TransactionId=20xid)=0A=20=09}=0A=20=0A=20=09/*=20No=20luck,=20= so=20switch=20to=20normal=20exclusive=20lock=20and=20do=20regular=20read=20= */=0A-=09LWLockRelease(shared->ControlLock);=0A-=09= LWLockAcquire(shared->ControlLock,=20LW_EXCLUSIVE);=0A+=09if=20= (*lock_mode=20!=3D=20LW_EXCLUSIVE)=0A+=09{=0A+=09=09Assert(*lock_mode=20= =3D=3D=20LW_NONE);=0A+=09=09LWLockRelease(shared->ControlLock);=0A+=09=09= LWLockAcquire(shared->ControlLock,=20LW_EXCLUSIVE);=0A+=09=09*lock_mode=20= =3D=20LW_EXCLUSIVE;=0A+=09}=0A=20=0A=20=09return=20= SimpleLruReadPage(ctl,=20pageno,=20true,=20xid);=0A=20}=0Adiff=20--git=20= a/src/backend/access/transam/subtrans.c=20= b/src/backend/access/transam/subtrans.c=0Aindex=200111e867c7..7bb1543189=20= 100644=0A---=20a/src/backend/access/transam/subtrans.c=0A+++=20= b/src/backend/access/transam/subtrans.c=0A@@=20-113,6=20+113,7=20@@=20= SubTransGetParent(TransactionId=20xid)=0A=20=09int=09=09=09slotno;=0A=20=09= TransactionId=20*ptr;=0A=20=09TransactionId=20parent;=0A+=09LWLockMode=09= lockmode=20=3D=20LW_NONE;=0A=20=0A=20=09/*=20Can't=20ask=20about=20stuff=20= that=20might=20not=20be=20around=20anymore=20*/=0A=20=09= Assert(TransactionIdFollowsOrEquals(xid,=20TransactionXmin));=0A@@=20= -123,12=20+124,13=20@@=20SubTransGetParent(TransactionId=20xid)=0A=20=0A=20= =09/*=20lock=20is=20acquired=20by=20SimpleLruReadPage_ReadOnly=20*/=0A=20= =0A-=09slotno=20=3D=20SimpleLruReadPage_ReadOnly(SubTransCtl,=20pageno,=20= xid);=0A+=09slotno=20=3D=20SimpleLruReadPage_ReadOnly(SubTransCtl,=20= pageno,=20xid,=20&lockmode);=0A=20=09ptr=20=3D=20(TransactionId=20*)=20= SubTransCtl->shared->page_buffer[slotno];=0A=20=09ptr=20+=3D=20entryno;=0A= =20=0A=20=09parent=20=3D=20*ptr;=0A=20=0A+=09Assert(lockmode=20!=3D=20= LW_NONE);=0A=20=09LWLockRelease(SubtransSLRULock);=0A=20=0A=20=09return=20= parent;=0Adiff=20--git=20a/src/backend/commands/async.c=20= b/src/backend/commands/async.c=0Aindex=208dbcace3f9..8b41957559=20100644=0A= ---=20a/src/backend/commands/async.c=0A+++=20= b/src/backend/commands/async.c=0A@@=20-2002,6=20+2002,7=20@@=20= asyncQueueReadAllNotifications(void)=0A=20=09=09=09int=09=09=09curoffset=20= =3D=20QUEUE_POS_OFFSET(pos);=0A=20=09=09=09int=09=09=09slotno;=0A=20=09=09= =09int=09=09=09copysize;=0A+=09=09=09LWLockMode=09lockmode=20=3D=20= LW_NONE;=0A=20=0A=20=09=09=09/*=0A=20=09=09=09=20*=20We=20copy=20the=20= data=20from=20SLRU=20into=20a=20local=20buffer,=20so=20as=20to=20avoid=0A= @@=20-2010,7=20+2011,7=20@@=20asyncQueueReadAllNotifications(void)=0A=20=09= =09=09=20*=20part=20of=20the=20page=20we=20will=20actually=20inspect.=0A=20= =09=09=09=20*/=0A=20=09=09=09slotno=20=3D=20= SimpleLruReadPage_ReadOnly(NotifyCtl,=20curpage,=0A-=09=09=09=09=09=09=09= =09=09=09=09=09InvalidTransactionId);=0A+=09=09=09=09=09=09=09=09=09=09=09= =09InvalidTransactionId,=20&lockmode);=0A=20=09=09=09if=20(curpage=20=3D=3D= =20QUEUE_POS_PAGE(head))=0A=20=09=09=09{=0A=20=09=09=09=09/*=20we=20only=20= want=20to=20read=20as=20far=20as=20head=20*/=0A@@=20-2027,6=20+2028,7=20= @@=20asyncQueueReadAllNotifications(void)=0A=20=09=09=09=09=20=20=20= NotifyCtl->shared->page_buffer[slotno]=20+=20curoffset,=0A=20=09=09=09=09= =20=20=20copysize);=0A=20=09=09=09/*=20Release=20lock=20that=20we=20got=20= from=20SimpleLruReadPage_ReadOnly()=20*/=0A+=09=09=09Assert(lockmode=20= !=3D=20LW_NONE);=0A=20=09=09=09LWLockRelease(NotifySLRULock);=0A=20=0A=20= =09=09=09/*=0Adiff=20--git=20a/src/backend/storage/lmgr/lwlock.c=20= b/src/backend/storage/lmgr/lwlock.c=0Aindex=202fa90cc095..8a7726f11c=20= 100644=0A---=20a/src/backend/storage/lmgr/lwlock.c=0A+++=20= b/src/backend/storage/lmgr/lwlock.c=0A@@=20-1068,6=20+1068,8=20@@=20= LWLockWakeup(LWLock=20*lock)=0A=20static=20void=0A=20= LWLockQueueSelf(LWLock=20*lock,=20LWLockMode=20mode)=0A=20{=0A+=09= Assert(mode=20!=3D=20LW_NONE);=0A+=0A=20=09/*=0A=20=09=20*=20If=20we=20= don't=20have=20a=20PGPROC=20structure,=20there's=20no=20way=20to=20wait.=20= This=0A=20=09=20*=20should=20never=20occur,=20since=20MyProc=20should=20= only=20be=20null=20during=20shared=0A@@=20-1828,6=20+1830,7=20@@=20= LWLockRelease(LWLock=20*lock)=0A=20=09=09elog(ERROR,=20"lock=20%s=20is=20= not=20held",=20T_NAME(lock));=0A=20=0A=20=09mode=20=3D=20= held_lwlocks[i].mode;=0A+=09Assert(mode=20=3D=3D=20LW_EXCLUSIVE=20||=20= mode=20=3D=3D=20LW_SHARED);=0A=20=0A=20=09num_held_lwlocks--;=0A=20=09= for=20(;=20i=20<=20num_held_lwlocks;=20i++)=0Adiff=20--git=20= a/src/backend/storage/lmgr/predicate.c=20= b/src/backend/storage/lmgr/predicate.c=0Aindex=208a365b400c..a4df90a8ae=20= 100644=0A---=20a/src/backend/storage/lmgr/predicate.c=0A+++=20= b/src/backend/storage/lmgr/predicate.c=0A@@=20-924,6=20+924,7=20@@=20= SerialGetMinConflictCommitSeqNo(TransactionId=20xid)=0A=20=09= TransactionId=20tailXid;=0A=20=09SerCommitSeqNo=20val;=0A=20=09int=09=09=09= slotno;=0A+=09LWLockMode=09lockmode=20=3D=20LW_NONE;=0A=20=0A=20=09= Assert(TransactionIdIsValid(xid));=0A=20=0A@@=20-946,8=20+947,9=20@@=20= SerialGetMinConflictCommitSeqNo(TransactionId=20xid)=0A=20=09=20*=20but=20= will=20return=20with=20that=20lock=20held,=20which=20must=20then=20be=20= released.=0A=20=09=20*/=0A=20=09slotno=20=3D=20= SimpleLruReadPage_ReadOnly(SerialSlruCtl,=0A-=09=09=09=09=09=09=09=09=09=09= SerialPage(xid),=20xid);=0A+=09=09=09=09=09=09=09=09=09=09= SerialPage(xid),=20xid,=20&lockmode);=0A=20=09val=20=3D=20= SerialValue(slotno,=20xid);=0A+=09Assert(lockmode=20!=3D=20LW_NONE);=0A=20= =09LWLockRelease(SerialSLRULock);=0A=20=09return=20val;=0A=20}=0Adiff=20= --git=20a/src/include/access/slru.h=20b/src/include/access/slru.h=0A= index=20b39b43504d..4b66d3b592=20100644=0A---=20= a/src/include/access/slru.h=0A+++=20b/src/include/access/slru.h=0A@@=20= -142,7=20+142,7=20@@=20extern=20int=09SimpleLruZeroPage(SlruCtl=20ctl,=20= int=20pageno);=0A=20extern=20int=09SimpleLruReadPage(SlruCtl=20ctl,=20= int=20pageno,=20bool=20write_ok,=0A=20=09=09=09=09=09=09=09=20=20= TransactionId=20xid);=0A=20extern=20int=09= SimpleLruReadPage_ReadOnly(SlruCtl=20ctl,=20int=20pageno,=0A-=09=09=09=09= =09=09=09=09=09=20=20=20TransactionId=20xid);=0A+=09=09=09=09=09=09=09=09= =09=20=20=20TransactionId=20xid,=20LWLockMode=20*lock_mode);=0A=20extern=20= void=20SimpleLruWritePage(SlruCtl=20ctl,=20int=20slotno);=0A=20extern=20= void=20SimpleLruWriteAll(SlruCtl=20ctl,=20bool=20allow_redirtied);=0A=20= extern=20void=20SimpleLruTruncate(SlruCtl=20ctl,=20int=20cutoffPage);=0A= diff=20--git=20a/src/include/storage/lwlock.h=20= b/src/include/storage/lwlock.h=0Aindex=20af9b41795d..e680c6397a=20100644=0A= ---=20a/src/include/storage/lwlock.h=0A+++=20= b/src/include/storage/lwlock.h=0A@@=20-129,6=20+129,8=20@@=20extern=20= PGDLLIMPORT=20int=20NamedLWLockTrancheRequests;=0A=20=0A=20typedef=20= enum=20LWLockMode=0A=20{=0A+=09LW_NONE,=09=09=09=09=09/*=20Not=20a=20= lock=20mode.=20Indicates=20that=20there=20is=20no=0A+=09=09=09=09=09=09=09= =09=20*=20lock.=20*/=0A=20=09LW_EXCLUSIVE,=0A=20=09LW_SHARED,=0A=20=09= LW_WAIT_UNTIL_FREE=09=09=09/*=20A=20special=20mode=20used=20in=20= PGPROC->lwWaitMode,=0A--=20=0A2.24.3=20(Apple=20Git-128)=0A=0A= --Apple-Mail=_0C03404A-C1B1-420C-A748-E676B2005EC2 Content-Disposition: attachment; filename=v6-0004-Add-GUCs-to-tune-MultiXact-SLRUs.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v6-0004-Add-GUCs-to-tune-MultiXact-SLRUs.patch" Content-Transfer-Encoding: quoted-printable =46rom=20a5e15ae42bd6c99d39bac7a9988f586c48ceba13=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Andrey=20Borodin=20=0ADate:=20= Sat,=209=20May=202020=2016:42:07=20+0500=0ASubject:=20[PATCH=20v6=204/4]=20= Add=20GUCs=20to=20tune=20MultiXact=20SLRUs=0A=0A---=0A=20= doc/src/sgml/config.sgml=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20|=20= 31=20++++++++++++++++++++++++++=0A=20= src/backend/access/transam/multixact.c=20|=20=208=20+++----=0A=20= src/backend/utils/init/globals.c=20=20=20=20=20=20=20|=20=202=20++=0A=20= src/backend/utils/misc/guc.c=20=20=20=20=20=20=20=20=20=20=20|=2022=20= ++++++++++++++++++=0A=20src/include/access/multixact.h=20=20=20=20=20=20=20= =20=20|=20=204=20----=0A=20src/include/miscadmin.h=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20|=20=202=20++=0A=206=20files=20changed,=2061=20= insertions(+),=208=20deletions(-)=0A=0Adiff=20--git=20= a/doc/src/sgml/config.sgml=20b/doc/src/sgml/config.sgml=0Aindex=20= fd4ca29347..d2ca4934de=20100644=0A---=20a/doc/src/sgml/config.sgml=0A+++=20= b/doc/src/sgml/config.sgml=0A@@=20-1838,6=20+1838,37=20@@=20include_dir=20= 'conf.d'=0A=20=20=20=20=20=20=20=20=0A=20=20=20=20=20=20=20= =0A=20=20=20=20=20=20=0A+=20=20=20=20=20=0A+=20= =20=20=20=0A+=20=20=20=20=20=20= multixact_offsets_slru_buffers=20= (integer)=0A+=20=20=20=20=20=20=0A+=20=20=20=20=20= =20=20multixact_offsets_slru_buffers=20= configuration=20parameter=0A+=20=20=20=20=20=20=0A= +=20=20=20=20=20=20=0A+=20=20=20=20=20=20=0A+=20=20=20=20= =20=20=20=0A+=20=20=20=20=20=20=20=20Specifies=20the=20amount=20of=20= memory=20to=20be=20used=20for=20MultiXact=20offsets.=20MultiXact=20= offsets=0A+=20=20=20=20=20=20=20=20are=20used=20to=20store=20informaion=20= about=20offsets=20of=20multiple=20row=20lockers=20(caused=20by=20SELECT=20= FOR=20UPDATE=20and=20others).=0A+=20=20=20=20=20=20=20=20It=20defaults=20= to=2064=20kilobytes=20(64KB).=0A+=20=20=20=20=20=20=20= =0A+=20=20=20=20=20=20=0A+=20=20=20=20=20= =0A+=0A+=20=20=20=20=0A+=20=20=20=20=20=20= multixact_members_slru_buffers=20= (integer)=0A+=20=20=20=20=20=20=0A+=20=20=20=20=20= =20=20multixact_members_slru_buffers=20= configuration=20parameter=0A+=20=20=20=20=20=20=0A= +=20=20=20=20=20=20=0A+=20=20=20=20=20=20=0A+=20=20=20=20= =20=20=20=0A+=20=20=20=20=20=20=20=20Specifies=20the=20amount=20of=20= memory=20to=20be=20used=20for=20MultiXact=20members.=20MultiXact=20= members=0A+=20=20=20=20=20=20=20=20are=20used=20to=20store=20informaion=20= about=20XIDs=20of=20multiple=20row=20lockers.=20Tipically=20= multixact_members_slru_buffers=0A+=20=20=20=20=20=20=20= =20is=20twice=20more=20than=20= multixact_offsets_slru_buffers.=0A+=20=20=20=20=20=20=20= =20It=20defaults=20to=20128=20kilobytes=20(128KB).=0A= +=20=20=20=20=20=20=20=0A+=20=20=20=20=20=20=0A+=20=20=20= =20=20=0A=20=0A=20=20=20=20=20=20=0A=20=20=20=20= =20=20=20max_stack_depth=20= (integer)=0Adiff=20--git=20= a/src/backend/access/transam/multixact.c=20= b/src/backend/access/transam/multixact.c=0Aindex=20= 70d977cba3..1c177242c5=20100644=0A---=20= a/src/backend/access/transam/multixact.c=0A+++=20= b/src/backend/access/transam/multixact.c=0A@@=20-1866,8=20+1866,8=20@@=20= MultiXactShmemSize(void)=0A=20=09=09=09=20mul_size(sizeof(MultiXactId)=20= *=202,=20MaxOldestSlot))=0A=20=0A=20=09size=20=3D=20= SHARED_MULTIXACT_STATE_SIZE;=0A-=09size=20=3D=20add_size(size,=20= SimpleLruShmemSize(NUM_MULTIXACTOFFSET_BUFFERS,=200));=0A-=09size=20=3D=20= add_size(size,=20SimpleLruShmemSize(NUM_MULTIXACTMEMBER_BUFFERS,=200));=0A= +=09size=20=3D=20add_size(size,=20= SimpleLruShmemSize(multixact_offsets_slru_buffers,=200));=0A+=09size=20=3D= =20add_size(size,=20SimpleLruShmemSize(multixact_members_slru_buffers,=20= 0));=0A=20=0A=20=09return=20size;=0A=20}=0A@@=20-1883,12=20+1883,12=20@@=20= MultiXactShmemInit(void)=0A=20=09MultiXactMemberCtl->PagePrecedes=20=3D=20= MultiXactMemberPagePrecedes;=0A=20=0A=20=09= SimpleLruInit(MultiXactOffsetCtl,=0A-=09=09=09=09=20=20= "MultiXactOffset",=20NUM_MULTIXACTOFFSET_BUFFERS,=200,=0A+=09=09=09=09=20= =20"MultiXactOffset",=20multixact_offsets_slru_buffers,=200,=0A=20=09=09=09= =09=20=20MultiXactOffsetSLRULock,=20"pg_multixact/offsets",=0A=20=09=09=09= =09=20=20LWTRANCHE_MULTIXACTOFFSET_BUFFER,=0A=20=09=09=09=09=20=20= SYNC_HANDLER_MULTIXACT_OFFSET);=0A=20=09= SimpleLruInit(MultiXactMemberCtl,=0A-=09=09=09=09=20=20= "MultiXactMember",=20NUM_MULTIXACTMEMBER_BUFFERS,=200,=0A+=09=09=09=09=20= =20"MultiXactMember",=20multixact_members_slru_buffers,=200,=0A=20=09=09=09= =09=20=20MultiXactMemberSLRULock,=20"pg_multixact/members",=0A=20=09=09=09= =09=20=20LWTRANCHE_MULTIXACTMEMBER_BUFFER,=0A=20=09=09=09=09=20=20= SYNC_HANDLER_MULTIXACT_MEMBER);=0Adiff=20--git=20= a/src/backend/utils/init/globals.c=20b/src/backend/utils/init/globals.c=0A= index=209ca71933dc..a5ec7bfe88=20100644=0A---=20= a/src/backend/utils/init/globals.c=0A+++=20= b/src/backend/utils/init/globals.c=0A@@=20-151,3=20+151,5=20@@=20bool=09=09= VacuumCostActive=20=3D=20false;=0A=20double=09=09= vacuum_cleanup_index_scale_factor;=0A=20=0A=20int=20=20=20=20=20=20=20=20= =20multixact_local_cache_entries=20=3D=20256;=0A+int=09=09=09= multixact_offsets_slru_buffers=20=3D=208;=0A+int=09=09=09= multixact_members_slru_buffers=20=3D=2016;=0Adiff=20--git=20= a/src/backend/utils/misc/guc.c=20b/src/backend/utils/misc/guc.c=0Aindex=20= d667578cc3..7682c8be1e=20100644=0A---=20a/src/backend/utils/misc/guc.c=0A= +++=20b/src/backend/utils/misc/guc.c=0A@@=20-2267,6=20+2267,28=20@@=20= static=20struct=20config_int=20ConfigureNamesInt[]=20=3D=0A=20=09=09= NULL,=20NULL,=20NULL=0A=20=09},=0A=20=0A+=09{=0A+=09=09= {"multixact_offsets_slru_buffers",=20PGC_POSTMASTER,=20RESOURCES_MEM,=0A= +=09=09=09gettext_noop("Sets=20the=20number=20of=20shared=20memory=20= buffers=20used=20for=20MultiXact=20offsets=20SLRU."),=0A+=09=09=09NULL,=0A= +=09=09=09GUC_UNIT_BLOCKS=0A+=09=09},=0A+=09=09= &multixact_offsets_slru_buffers,=0A+=09=098,=202,=20INT_MAX=20/=202,=0A+=09= =09NULL,=20NULL,=20NULL=0A+=09},=0A+=0A+=09{=0A+=09=09= {"multixact_members_slru_buffers",=20PGC_POSTMASTER,=20RESOURCES_MEM,=0A= +=09=09=09gettext_noop("Sets=20the=20number=20of=20shared=20memory=20= buffers=20used=20for=20MultiXact=20members=20SLRU."),=0A+=09=09=09NULL,=0A= +=09=09=09GUC_UNIT_BLOCKS=0A+=09=09},=0A+=09=09= &multixact_members_slru_buffers,=0A+=09=0916,=202,=20INT_MAX=20/=202,=0A= +=09=09NULL,=20NULL,=20NULL=0A+=09},=0A+=0A=20=09{=0A=20=09=09= {"temp_buffers",=20PGC_USERSET,=20RESOURCES_MEM,=0A=20=09=09=09= gettext_noop("Sets=20the=20maximum=20number=20of=20temporary=20buffers=20= used=20by=20each=20session."),=0Adiff=20--git=20= a/src/include/access/multixact.h=20b/src/include/access/multixact.h=0A= index=209a30380901..630ceaea4d=20100644=0A---=20= a/src/include/access/multixact.h=0A+++=20= b/src/include/access/multixact.h=0A@@=20-29,10=20+29,6=20@@=0A=20=0A=20= #define=20MaxMultiXactOffset=09((MultiXactOffset)=200xFFFFFFFF)=0A=20=0A= -/*=20Number=20of=20SLRU=20buffers=20to=20use=20for=20multixact=20*/=0A= -#define=20NUM_MULTIXACTOFFSET_BUFFERS=09=098=0A-#define=20= NUM_MULTIXACTMEMBER_BUFFERS=09=0916=0A-=0A=20/*=0A=20=20*=20Possible=20= multixact=20lock=20modes=20("status").=20=20The=20first=20four=20modes=20= are=20for=0A=20=20*=20tuple=20locks=20(FOR=20KEY=20SHARE,=20FOR=20SHARE,=20= FOR=20NO=20KEY=20UPDATE,=20FOR=20UPDATE);=20the=0Adiff=20--git=20= a/src/include/miscadmin.h=20b/src/include/miscadmin.h=0Aindex=20= 01af61c963..ef8abea84d=20100644=0A---=20a/src/include/miscadmin.h=0A+++=20= b/src/include/miscadmin.h=0A@@=20-161,6=20+161,8=20@@=20extern=20= PGDLLIMPORT=20int=20MaxBackends;=0A=20extern=20PGDLLIMPORT=20int=20= MaxConnections;=0A=20extern=20PGDLLIMPORT=20int=20max_worker_processes;=0A= =20extern=20PGDLLIMPORT=20int=20max_parallel_workers;=0A+extern=20= PGDLLIMPORT=20int=20multixact_offsets_slru_buffers;=0A+extern=20= PGDLLIMPORT=20int=20multixact_members_slru_buffers;=0A=20=0A=20extern=20= PGDLLIMPORT=20int=20multixact_local_cache_entries;=0A=20=0A--=20=0A= 2.24.3=20(Apple=20Git-128)=0A=0A= --Apple-Mail=_0C03404A-C1B1-420C-A748-E676B2005EC2 Content-Disposition: attachment; filename=v6-0003-Add-conditional-variable-to-wait-for-next-MultXac.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v6-0003-Add-conditional-variable-to-wait-for-next-MultXac.patch" Content-Transfer-Encoding: quoted-printable =46rom=20d25e7d68a7979fc391c5d988b256a49cdd916bfb=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Alexander=20Korotkov=20= =0ADate:=20Mon,=2026=20Oct=202020=2001:55:31=20= +0300=0ASubject:=20[PATCH=20v6=203/4]=20Add=20conditional=20variable=20= to=20wait=20for=20next=20MultXact=0A=20offset=20in=20corner=20case=0A=0A= GetMultiXactIdMembers()=20has=20a=20corner=20case,=20when=20the=20next=20= multixact=20offset=20is=0Anot=20yet=20set.=20=20In=20this=20case=20= GetMultiXactIdMembers()=20has=20to=20sleep=20till=20this=20offset=0Ais=20= set.=20=20Currently=20the=20sleeping=20is=20implemented=20in=20naive=20= way=20using=20pg_sleep()=0Aand=20retry.=20=20This=20commit=20implements=20= sleeping=20with=20conditional=20variable,=20which=0Aprovides=20more=20= efficient=20way=20for=20waiting=20till=20the=20event.=0A=0ADiscussion:=20= https://postgr.es/m/a7f1c4e1-1015-92a4-2bd4-6736bd13d03e%40postgrespro.ru#= c496c4e75fc0605094a0e1f763e6a6ec=0AAuthor:=20Andrey=20Borodin=0A= Reviewed-by:=20Kyotaro=20Horiguchi,=20Daniel=20Gustafsson=0AReviewed-by:=20= Anastasia=20Lubennikova,=20Alexander=20Korotkov=0A---=0A=20= src/backend/access/transam/multixact.c=20|=2035=20= ++++++++++++++++++++++++--=0A=20src/backend/postmaster/pgstat.c=20=20=20=20= =20=20=20=20|=20=202=20++=0A=20src/include/pgstat.h=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20|=20=201=20+=0A=203=20files=20changed,=20= 36=20insertions(+),=202=20deletions(-)=0A=0Adiff=20--git=20= a/src/backend/access/transam/multixact.c=20= b/src/backend/access/transam/multixact.c=0Aindex=20= 57be24c0cc..70d977cba3=20100644=0A---=20= a/src/backend/access/transam/multixact.c=0A+++=20= b/src/backend/access/transam/multixact.c=0A@@=20-82,6=20+82,7=20@@=0A=20= #include=20"lib/ilist.h"=0A=20#include=20"miscadmin.h"=0A=20#include=20= "pg_trace.h"=0A+#include=20"pgstat.h"=0A=20#include=20= "postmaster/autovacuum.h"=0A=20#include=20"storage/lmgr.h"=0A=20#include=20= "storage/pmsignal.h"=0A@@=20-233,6=20+234,13=20@@=20typedef=20struct=20= MultiXactStateData=0A=20=09/*=20support=20for=20members=20= anti-wraparound=20measures=20*/=0A=20=09MultiXactOffset=20= offsetStopLimit;=09/*=20known=20if=20oldestOffsetKnown=20*/=0A=20=0A+=09= /*=0A+=09=20*=20Conditional=20variable=20for=20waiting=20till=20the=20= filling=20of=20the=20next=20multixact=0A+=09=20*=20will=20be=20finished.=20= =20See=20GetMultiXactIdMembers()=20and=20RecordNewMultiXact()=0A+=09=20*=20= for=20details.=0A+=09=20*/=0A+=09ConditionVariable=20nextoffCV;=0A+=0A=20= =09/*=0A=20=09=20*=20Per-backend=20data=20starts=20here.=20=20We=20have=20= two=20arrays=20stored=20in=20the=20area=0A=20=09=20*=20immediately=20= following=20the=20MultiXactStateData=20struct.=20Each=20is=20indexed=20= by=0A@@=20-892,6=20+900,14=20@@=20RecordNewMultiXact(MultiXactId=20= multi,=20MultiXactOffset=20offset,=0A=20=09/*=20Exchange=20our=20lock=20= */=0A=20=09LWLockRelease(MultiXactOffsetSLRULock);=0A=20=0A+=09/*=0A+=09=20= *=20Let=20everybody=20know=20the=20offset=20of=20this=20mxid=20is=20= recorded=20now.=20=20The=0A+=09=20*=20waiters=20are=20waiting=20for=20= the=20offset=20of=20the=20mxid=20next=20of=20the=20target=20to=0A+=09=20= *=20know=20the=20number=20of=20members=20of=20the=20target=20mxid,=20so=20= we=20don't=20need=20to=20wait=0A+=09=20*=20for=20members=20of=20this=20= mxid=20are=20recorded.=0A+=09=20*/=0A+=09= ConditionVariableBroadcast(&MultiXactState->nextoffCV);=0A+=0A=20=09= LWLockAcquire(MultiXactMemberSLRULock,=20LW_EXCLUSIVE);=0A=20=0A=20=09= prev_pageno=20=3D=20-1;=0A@@=20-1389,9=20+1405,23=20@@=20retry:=0A=20=09=09= if=20(nextMXOffset=20=3D=3D=200)=0A=20=09=09{=0A=20=09=09=09/*=20Corner=20= case=202:=20next=20multixact=20is=20still=20being=20filled=20in=20*/=0A+=0A= +=09=09=09/*=0A+=09=09=09=20*=20The=20recorder=20of=20the=20next=20mxid=20= is=20just=20before=20writing=20the=0A+=09=09=09=20*=20offset.=20Wait=20= for=20the=20offset=20to=20be=20written.=0A+=09=09=09=20*/=0A+=09=09=09= ConditionVariablePrepareToSleep(&MultiXactState->nextoffCV);=0A+=0A+=09=09= =09/*=0A+=09=09=09=20*=20We=20don't=20have=20to=20recheck=20if=20= multixact=20was=20filled=20in=20during=0A+=09=09=09=20*=20= ConditionVariablePrepareToSleep(),=20because=20we=20were=20holding=0A+=09= =09=09=20*=20MultiXactOffsetSLRULock.=0A+=09=09=09=20*/=0A=20=09=09=09= LWLockRelease(MultiXactOffsetSLRULock);=0A-=09=09=09= CHECK_FOR_INTERRUPTS();=0A-=09=09=09pg_usleep(1000L);=0A+=0A+=09=09=09= ConditionVariableSleep(&MultiXactState->nextoffCV,=0A+=09=09=09=09=09=09=09= =09=20=20=20WAIT_EVENT_WAIT_NEXT_MXMEMBERS);=0A+=09=09=09= ConditionVariableCancelSleep();=0A=20=09=09=09goto=20retry;=0A=20=09=09}=0A= =20=0A@@=20-1873,6=20+1903,7=20@@=20MultiXactShmemInit(void)=0A=20=0A=20=09= =09/*=20Make=20sure=20we=20zero=20out=20the=20per-backend=20state=20*/=0A= =20=09=09MemSet(MultiXactState,=200,=20SHARED_MULTIXACT_STATE_SIZE);=0A+=09= =09ConditionVariableInit(&MultiXactState->nextoffCV);=0A=20=09}=0A=20=09= else=0A=20=09=09Assert(found);=0Adiff=20--git=20= a/src/backend/postmaster/pgstat.c=20b/src/backend/postmaster/pgstat.c=0A= index=20822f0ebc62..b99398a97e=20100644=0A---=20= a/src/backend/postmaster/pgstat.c=0A+++=20= b/src/backend/postmaster/pgstat.c=0A@@=20-4020,6=20+4020,8=20@@=20= pgstat_get_wait_ipc(WaitEventIPC=20w)=0A=20=09=09=09break;=0A=20=09=09= case=20WAIT_EVENT_XACT_GROUP_UPDATE:=0A=20=09=09=09event_name=20=3D=20= "XactGroupUpdate";=0A+=09=09case=20WAIT_EVENT_WAIT_NEXT_MXMEMBERS:=0A+=09= =09=09event_name=20=3D=20"MultiXactWaitNextMembers";=0A=20=09=09=09= break;=0A=20=09=09=09/*=20no=20default=20case,=20so=20that=20compiler=20= will=20warn=20*/=0A=20=09}=0Adiff=20--git=20a/src/include/pgstat.h=20= b/src/include/pgstat.h=0Aindex=20a821ff4f15..75ede141ab=20100644=0A---=20= a/src/include/pgstat.h=0A+++=20b/src/include/pgstat.h=0A@@=20-952,6=20= +952,7=20@@=20typedef=20enum=0A=20=09WAIT_EVENT_REPLICATION_SLOT_DROP,=0A= =20=09WAIT_EVENT_SAFE_SNAPSHOT,=0A=20=09WAIT_EVENT_SYNC_REP,=0A+=09= WAIT_EVENT_WAIT_NEXT_MXMEMBERS,=0A=20=09WAIT_EVENT_XACT_GROUP_UPDATE=0A=20= }=20WaitEventIPC;=0A=20=0A--=20=0A2.24.3=20(Apple=20Git-128)=0A=0A= --Apple-Mail=_0C03404A-C1B1-420C-A748-E676B2005EC2 Content-Disposition: attachment; filename=v6-0002-Make-MultiXact-local-cache-size-configurable.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v6-0002-Make-MultiXact-local-cache-size-configurable.patch" Content-Transfer-Encoding: quoted-printable =46rom=20d0cd1ce556ad3f4e7bbebf0468dc6e00765d79ae=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Alexander=20Korotkov=20= =0ADate:=20Mon,=2026=20Oct=202020=2003:44:50=20= +0300=0ASubject:=20[PATCH=20v6=202/4]=20Make=20MultiXact=20local=20cache=20= size=20configurable=0A=0A---=0A=20doc/src/sgml/config.sgml=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20|=2016=20++++++++++++++++=0A=20= src/backend/access/transam/multixact.c=20|=20=202=20+-=0A=20= src/backend/utils/init/globals.c=20=20=20=20=20=20=20|=20=202=20++=0A=20= src/backend/utils/misc/guc.c=20=20=20=20=20=20=20=20=20=20=20|=2010=20= ++++++++++=0A=20src/include/miscadmin.h=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20|=20=202=20++=0A=205=20files=20changed,=2031=20= insertions(+),=201=20deletion(-)=0A=0Adiff=20--git=20= a/doc/src/sgml/config.sgml=20b/doc/src/sgml/config.sgml=0Aindex=20= f043433e31..fd4ca29347=20100644=0A---=20a/doc/src/sgml/config.sgml=0A+++=20= b/doc/src/sgml/config.sgml=0A@@=20-1823,6=20+1823,22=20@@=20include_dir=20= 'conf.d'=0A=20=20=20=20=20=20=20=0A=20=20=20=20=20=20= =0A=20=0A+=20=20=20=20=0A+=20=20=20=20=20=20= multixact_local_cache_entries=20= (integer)=0A+=20=20=20=20=20=20=0A+=20=20=20=20=20= =20=20multixact_local_cache_entries=20= configuration=20parameter=0A+=20=20=20=20=20=20=0A= +=20=20=20=20=20=20=0A+=20=20=20=20=20=20=0A+=20=20=20=20= =20=20=20=0A+=20=20=20=20=20=20=20=20Specifies=20the=20number=20= cached=20MultiXact=20by=20backend.=20Any=20SLRU=20lookup=20is=20= preceeded=0A+=20=20=20=20=20=20=20=20by=20cache=20lookup.=20Higher=20= numbers=20of=20cache=20size=20help=20to=20deduplicate=20lock=20sets,=20= while=0A+=20=20=20=20=20=20=20=20lower=20values=20make=20cache=20lookup=20= faster.=0A+=20=20=20=20=20=20=20=20It=20defaults=20to=20256.=0A+=20=20=20= =20=20=20=20=0A+=20=20=20=20=20=20=0A+=20=20=20=20=20= =0A+=0A=20=20=20=20=20=20=0A=20=20=20=20= =20=20=20max_stack_depth=20= (integer)=0A=20=20=20=20=20=20=20=0Adiff=20--git=20= a/src/backend/access/transam/multixact.c=20= b/src/backend/access/transam/multixact.c=0Aindex=20= ccbce90f0e..57be24c0cc=20100644=0A---=20= a/src/backend/access/transam/multixact.c=0A+++=20= b/src/backend/access/transam/multixact.c=0A@@=20-1613,7=20+1613,7=20@@=20= mXactCachePut(MultiXactId=20multi,=20int=20nmembers,=20MultiXactMember=20= *members)=0A=20=09qsort(entry->members,=20nmembers,=20= sizeof(MultiXactMember),=20mxactMemberComparator);=0A=20=0A=20=09= dlist_push_head(&MXactCache,=20&entry->node);=0A-=09if=20= (MXactCacheMembers++=20>=3D=20MAX_CACHE_ENTRIES)=0A+=09if=20= (MXactCacheMembers++=20>=3D=20multixact_local_cache_entries)=0A=20=09{=0A= =20=09=09dlist_node=20*node;=0A=20=09=09mXactCacheEnt=20*entry;=0Adiff=20= --git=20a/src/backend/utils/init/globals.c=20= b/src/backend/utils/init/globals.c=0Aindex=206ab8216839..9ca71933dc=20= 100644=0A---=20a/src/backend/utils/init/globals.c=0A+++=20= b/src/backend/utils/init/globals.c=0A@@=20-149,3=20+149,5=20@@=20int=09=09= =09VacuumCostBalance=20=3D=200;=09/*=20working=20state=20for=20vacuum=20= */=0A=20bool=09=09VacuumCostActive=20=3D=20false;=0A=20=0A=20double=09=09= vacuum_cleanup_index_scale_factor;=0A+=0A+int=20=20=20=20=20=20=20=20=20= multixact_local_cache_entries=20=3D=20256;=0Adiff=20--git=20= a/src/backend/utils/misc/guc.c=20b/src/backend/utils/misc/guc.c=0Aindex=20= a62d64eaa4..d667578cc3=20100644=0A---=20a/src/backend/utils/misc/guc.c=0A= +++=20b/src/backend/utils/misc/guc.c=0A@@=20-2257,6=20+2257,16=20@@=20= static=20struct=20config_int=20ConfigureNamesInt[]=20=3D=0A=20=09=09= NULL,=20NULL,=20NULL=0A=20=09},=0A=20=0A+=09{=0A+=09=09= {"multixact_local_cache_entries",=20PGC_SUSET,=20RESOURCES_MEM,=0A+=09=09= =09gettext_noop("Sets=20the=20number=20of=20cached=20MultiXact=20by=20= backend."),=0A+=09=09=09NULL=0A+=09=09},=0A+=09=09= &multixact_local_cache_entries,=0A+=09=09256,=202,=20INT_MAX=20/=202,=0A= +=09=09NULL,=20NULL,=20NULL=0A+=09},=0A+=0A=20=09{=0A=20=09=09= {"temp_buffers",=20PGC_USERSET,=20RESOURCES_MEM,=0A=20=09=09=09= gettext_noop("Sets=20the=20maximum=20number=20of=20temporary=20buffers=20= used=20by=20each=20session."),=0Adiff=20--git=20= a/src/include/miscadmin.h=20b/src/include/miscadmin.h=0Aindex=20= 72e3352398..01af61c963=20100644=0A---=20a/src/include/miscadmin.h=0A+++=20= b/src/include/miscadmin.h=0A@@=20-162,6=20+162,8=20@@=20extern=20= PGDLLIMPORT=20int=20MaxConnections;=0A=20extern=20PGDLLIMPORT=20int=20= max_worker_processes;=0A=20extern=20PGDLLIMPORT=20int=20= max_parallel_workers;=0A=20=0A+extern=20PGDLLIMPORT=20int=20= multixact_local_cache_entries;=0A+=0A=20extern=20PGDLLIMPORT=20int=20= MyProcPid;=0A=20extern=20PGDLLIMPORT=20pg_time_t=20MyStartTime;=0A=20= extern=20PGDLLIMPORT=20TimestampTz=20MyStartTimestamp;=0A--=20=0A2.24.3=20= (Apple=20Git-128)=0A=0A= --Apple-Mail=_0C03404A-C1B1-420C-A748-E676B2005EC2--