public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andrey Borodin <[email protected]>
To: Tomas Vondra <[email protected]>
Cc: Alexander Korotkov <[email protected]>
Cc: Anastasia Lubennikova <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: Kyotaro Horiguchi <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: MultiXact\SLRU buffers configuration
Date: Mon, 2 Nov 2020 17:45:33 +0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <20201029134933.xd4mh2cofuf6tdfz@development>
References: <[email protected]>
	<[email protected]>
	<CAPpHfduekT=BrM3rLqvo4ajzKYmDk+6aCoYJrNHhK0Je+v2z=Q@mail.gmail.com>
	<[email protected]>
	<CAPpHfduT0Kb+8fMt4t0EF5G_3hig+wYgeRadQnNq20F4VxP7LA@mail.gmail.com>
	<CAPpHfdsUtSGxRrKn65F_wrE66sNztNY4TpDGDHh2kvAZZDOMqA@mail.gmail.com>
	<20201028013651.de5cj2xadgmba5nf@development>
	<[email protected]>
	<20201028233243.ygm6yqlynkqpzekr@development>
	<[email protected]>
	<20201029134933.xd4mh2cofuf6tdfz@development>



> 29 окт. 2020 г., в 18:49, Tomas Vondra <[email protected]> написал(а):
> 
> On Thu, Oct 29, 2020 at 12:08:21PM +0500, Andrey Borodin wrote:
>> 
>> 
>>> 29 окт. 2020 г., в 04:32, Tomas Vondra <[email protected]> написал(а):
>>> 
>>> 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?

Yes, we had some problems with 'multixact "members" limit exceeded' long time ago.
We tuned autovacuum_multixact_freeze_max_age = 200000000 and vacuum_multixact_freeze_table_age = 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.

view thread (81+ messages)  latest in thread

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: MultiXact\SLRU buffers configuration
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox