Re: MultiXactMemberControlLock contention on a replica - Mailing list pgsql-general

From Laurenz Albe
Subject Re: MultiXactMemberControlLock contention on a replica
Date
Msg-id e2cb31b1e243222ca93bd85763467690033afd0a.camel@cybertec.at
Whole thread Raw
In response to Re: MultiXactMemberControlLock contention on a replica  (Christophe Pettus <xof@thebuild.com>)
List pgsql-general
On Mon, 2021-02-15 at 12:40 -0800, Christophe Pettus wrote:
> > On Feb 15, 2021, at 08:15, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > Right.  I cannot think of any other reason, given that the standby only
> > allows reading.  It's just an "xmax", and PostgreSQL needs to read the
> > multixact to figure out if it can see the row or not.
> 
> 
> OK, I think I see the scenario: A very large number of sessions on the primary all
>  touch or create rows which refer to a particular row in another table by foreign
>  key, but they don't modify that row.  A lot of sessions on the secondary all read
>  the row in the referred-to table, so it has to get all the members of the multixact,
>  and if the multixact structure has spilled to disk, that gets very expensive.

You also get a multixact if you run something like

BEGIN;
SELECT ... FROM tab WHERE id = 42 FOR UPDATE;
SAVEPOINT a;
UPDATE tab SET ... WHERE id = 42;
ROLLBACK;

The multixact is also created if you commit, but it won't be visible.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Set a specific database to log_statement='ddl' but others to be log_statement='all'
Next
From: Abdul Qoyyuum
Date:
Subject: Re: Set a specific database to log_statement='ddl' but others to be log_statement='all'