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(dot)albe(at)cybertec(dot)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