Re: MultiXactMemberControlLock contention on a replica

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