Обсуждение: MultiXactMemberControlLock contention on a replica
On a whole fleet of load-balanced replicas, we saw an incident where one particular query started backing up on MultiXactMemberControlLockand multixact_member. There was no sign of this backup on the primary. Under what conditionswould there be enough multixact members on a replica (where you can't do UPDATE / SELECT FOR UPDATE / FOR SHARE)to start spilling to disk? -- -- Christophe Pettus xof@thebuild.com
On Fri, 2021-02-12 at 11:11 -0800, Christophe Pettus wrote: > On a whole fleet of load-balanced replicas, we saw an incident where one particular query > started backing up on MultiXactMemberControlLock and multixact_member. There was no sign > of this backup on the primary. Under what conditions would there be enough multixact > members on a replica (where you can't do UPDATE / SELECT FOR UPDATE / FOR SHARE) to start > spilling to disk? Multixacts are replicated, and they are only generated on the primary. So my guess would be that the difference between primary and standby is not that a different number of multixacts are created, but that you need to read them on the standby and not on the primary. Are the multixacts caused by foreign keys or by subtransactions? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
> On Feb 15, 2021, at 07:47, Laurenz Albe <laurenz.albe@cybertec.at> wrote: > So my guess would be that the difference between primary and standby is not that a > different number of multixacts are created, but that you need to read them on > the standby and not on the primary. Why does the secondary need to read them? Visibility? -- -- Christophe Pettus xof@thebuild.com
On Mon, 2021-02-15 at 08:03 -0800, Christophe Pettus wrote: > On Feb 15, 2021, at 07:47, Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > So my guess would be that the difference between primary and standby is not that a > > different number of multixacts are created, but that you need to read them on > > the standby and not on the primary. > > Why does the secondary need to read them? Visibility? 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. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
> 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 aparticular row in another table by foreign key, but they don't modify that row. A lot of sessions on the secondary allread the row in the referred-to table, so it has to get all the members of the multixact, and if the multixact structurehas spilled to disk, that gets very expensive. -- -- Christophe Pettus xof@thebuild.com
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