From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Andrey Borodin <x4mmm@yandex-team.ru>
Cc: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Cc: Daniel Gustafsson <daniel@yesql.se>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: MultiXact\SLRU buffers configuration
Date: Thu, 29 Oct 2020 14:49:33 +0100
Message-ID: <20201029134933.xd4mh2cofuf6tdfz@development> (raw)
In-Reply-To: <43F3DE92-F236-4EA5-B4D6-39BEF6BD849D@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>
On Thu, Oct 29, 2020 at 12:08:21PM +0500, Andrey Borodin wrote:
>
>
>> 29 окт. 2020 г., в 04:32, Tomas Vondra <tomas.vondra@2ndquadrant.com> написал(а):
>>
>> 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 ...
>>
>> 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:
>>
>> (a) it actually will help with the issue you're observing on production
>>
>> and
>> (b) it's actually worth the extra complexity (e.g. the lwlock changes)
>>
>>
>> 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.
>
>Thanks, Tomas! This totally makes sense.
>
>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
>
>
>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.
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?
>Backtraces on standbys during the problem show that most of backends are sleeping in pg_sleep(1000L) and are not included into wait stats on these charts.
>
>Currently I'm considering writing test that directly calls MultiXactIdExpand(), MultiXactIdCreate(), and GetMultiXactIdMembers() from an extension. How do you think, would benchmarks in such tests be meaningful?
>
I don't know. I'd much rather have a SQL-level benchmark than an
extension doing this kind of stuff.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, x4mmm@yandex-team.ru, aekorotkov@gmail.com, a.lubennikova@postgrespro.ru, daniel@yesql.se, horikyota.ntt@gmail.com
Subject: Re: MultiXact\SLRU buffers configuration
In-Reply-To: <20201029134933.xd4mh2cofuf6tdfz@development>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox