Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\))
Subject: Re: MultiXact\SLRU buffers configuration
From: Andrey Borodin <x4mmm@yandex-team.ru>
In-Reply-To: <20201029134933.xd4mh2cofuf6tdfz@development>
Date: Mon, 2 Nov 2020 17:45:33 +0500
Cc: Alexander Korotkov <aekorotkov@gmail.com>,
 Anastasia Lubennikova <a.lubennikova@postgrespro.ru>,
 Daniel Gustafsson <daniel@yesql.se>,
 Kyotaro Horiguchi <horikyota.ntt@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <65C1B4BA-D16F-4939-978B-AC8F370F5A5E@yandex-team.ru>
References: <a7f1c4e1-1015-92a4-2bd4-6736bd13d03e@postgrespro.ru>
 <2087E87D-44CA-4443-8E6A-5087F07443F4@yandex-team.ru>
 <CAPpHfduekT=BrM3rLqvo4ajzKYmDk+6aCoYJrNHhK0Je+v2z=Q@mail.gmail.com>
 <8A94938B-054C-4439-9866-2C220B4D0DD7@yandex-team.ru>
 <CAPpHfduT0Kb+8fMt4t0EF5G_3hig+wYgeRadQnNq20F4VxP7LA@mail.gmail.com>
 <CAPpHfdsUtSGxRrKn65F_wrE66sNztNY4TpDGDHh2kvAZZDOMqA@mail.gmail.com>
 <20201028013651.de5cj2xadgmba5nf@development>
 <13D8FD63-559A-4737-B7FD-05288D1CEF8B@yandex-team.ru>
 <20201028233243.ygm6yqlynkqpzekr@development>
 <43F3DE92-F236-4EA5-B4D6-39BEF6BD849D@yandex-team.ru>
 <20201029134933.xd4mh2cofuf6tdfz@development>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Precedence: bulk


> 29 =D0=BE=D0=BA=D1=82. 2020 =D0=B3., =D0=B2 18:49, Tomas Vondra =
<tomas.vondra@2ndquadrant.com> =D0=BD=D0=B0=D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=
=D0=B0):
>=20
> On Thu, Oct 29, 2020 at 12:08:21PM +0500, Andrey Borodin wrote:
>>=20
>>=20
>>> 29 =D0=BE=D0=BA=D1=82. 2020 =D0=B3., =D0=B2 04:32, Tomas Vondra =
<tomas.vondra@2ndquadrant.com> =D0=BD=D0=B0=D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=
=D0=B0):
>>>=20
>>> It's not my intention to be mean or anything like that, but to me =
this
>>> means we don't really understand the problem we're trying to solve. =
Had
>>> we understood it, we should be able to construct a workload =
reproducing
>>> the issue ...
>>>=20
>>> I understand what the individual patches are doing, and maybe those
>>> changes are desirable in general. But without any benchmarks from a
>>> plausible workload I find it hard to convince myself that:
>>>=20
>>> (a) it actually will help with the issue you're observing on =
production
>>>=20
>>> and
>>> (b) it's actually worth the extra complexity (e.g. the lwlock =
changes)
>>>=20
>>>=20
>>> I'm willing to invest some of my time into reviewing/testing this, =
but I
>>> think we badly need better insight into the issue, so that we can =
build
>>> a workload reproducing it. Perhaps collecting some perf profiles and =
a
>>> sample of the queries might help, but I assume you already tried =
that.
>>=20
>> Thanks, Tomas! This totally makes sense.
>>=20
>> Indeed, collecting queries did not help yet. We have loadtest =
environment equivalent to production (but with 10x less shards), copy of =
production workload queries. But the problem does not manifest there.
>> Why do I think the problem is in MultiXacts?
>> Here is a chart with number of wait events on each host
>>=20
>>=20
>> During the problem MultiXactOffsetControlLock and SLRURead dominate =
all other lock types. After primary switchover to another node SLRURead =
continued for a bit there, then disappeared.
>=20
> OK, so most of this seems to be due to SLRURead and
> MultiXactOffsetControlLock. Could it be that there were too many
> multixact members, triggering autovacuum to prevent multixact
> wraparound? That might generate a lot of IO on the SLRU. Are you
> monitoring the size of the pg_multixact directory?

Yes, we had some problems with 'multixact "members" limit exceeded' long =
time ago.
We tuned autovacuum_multixact_freeze_max_age =3D 200000000 and =
vacuum_multixact_freeze_table_age =3D 75000000 (half of defaults) and =
since then did not ever encounter this problem (~5 months).
But the MultiXactOffsetControlLock problem persists. Partially the =
problem was solved by adding more shards. But when one of shards =
encounters a problem it's either MultiXacts or vacuum causing relation =
truncation (unrelated to this thread).

Thanks!

Best regards, Andrey Borodin.=