From: =?utf-8?q?=D0=94=D0=B0=D0=B2=D1=8B=D0=B4=D0=BE=D0=B2_=D0=92=D0=B8=D1=82=D0=B0=D0=BB=D0=B8=D0=B9?= <v.davydov@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
User-Agent: SOGoMail 5.9.1
MIME-Version: 1.0
Date: Thu, 22 Feb 2024 16:29:43 +0300
Subject: Slow catchup of 2PC (twophase) transactions on replica in LR
Message-ID: <8fab8-65d74c80-1-2f28e880@39088166>
Content-Type: multipart/alternative; boundary="----=_=-_OpenGroupware_org_NGMime-588472-1708608583.430853-0------"
Archived-At: <https://www.postgresql.org/message-id/8fab8-65d74c80-1-2f28e880%4039088166>
Precedence: bulk

------=_=-_OpenGroupware_org_NGMime-588472-1708608583.430853-0------
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Content-Length: 3558


Dear All,
I'd like to present and talk about a problem when 2PC transactions are =
applied quite slowly on a replica during logical replication. There is =
a master and a replica with established logical replication from the ma=
ster to the replica with twophase =3D true. With some load level on the=
 master, the replica starts to lag behind the master, and the lag will =
be increasing. We have to significantly decrease the load on the master=
 to allow replica to complete the catchup. Such problem may create sign=
ificant difficulties in the production. The problem appears at least on=
 REL=5F16=5FSTABLE branch.
To reproduce the problem:
 * Setup logical replication from master to replica with subscription p=
arameter twophase =3D=C2=A0 true. * Create some intermediate load on th=
e master (use pgbench with custom sql with prepare+commit) * Optionally=
 switch off the replica for some time (keep load on master). * Switch o=
n the replica and wait until it reaches the master.
The replica will never reach the master with even some low load on the =
master. If to remove the load, the replica will reach the master for mu=
ch greater time, than expected. I tried the same for regular transactio=
ns, but such problem doesn't appear even with a decent load.
I think, the main proplem of 2PC catchup bad performance - the lack of =
asynchronous commit support for 2PC. For regular transactions asynchron=
ous commit is used on the replica by default (subscrition sycnronous=5F=
commit =3D off). It allows the replication worker process on the replic=
a to avoid fsync (XLogFLush) and to utilize 100% CPU (the background wa=
l writer or checkpointer will do fsync). I agree, 2PC are mostly used i=
n multimaster configurations with two or more nodes which are performed=
 synchronously, but when the node in catchup (node is not online in a m=
ultimaster cluster), asynchronous commit have to be used to speedup the=
 catchup.
There is another thing that affects on the disbalance of the master and=
 replica performance. When the master executes requestes from multiple =
clients, there is a fsync optimization takes place in XLogFlush. It all=
ows to decrease the number of fsync in case when a number of parallel b=
ackends write to the WAL simultaneously. The replica applies received t=
ransactions in one thread sequentially, such optimization is not applie=
d.
I see some possible solutions:
 * Implement asyncronous commit for 2PC transactions. * Do some hacking=
 with enableFsync when it is possible.
I think, asynchronous commit support for 2PC transactions should signif=
icantly increase replica performance and help to solve this problem. I =
tried to implement it (like for usual transactions) but I've found anot=
her problem: 2PC state is stored in WAL on prepare, on commit we have t=
o read 2PC state from WAL but the read is delayed until WAL is flushed =
by the background wal writer (read LSN should be less than flush LSN). =
Storing 2PC state in a shared memory (as it proposed earlier) may help.

I used the following query to monitor the catchup progress on the maste=
r:SELECT sent=5Flsn, pg=5Fcurrent=5Fwal=5Flsn() FROM pg=5Fstat=5Freplic=
ation;
I used the following script for pgbench to the master:SELECT md5(random=
()::text) as mygid \gset
BEGIN;
DELETE FROM test WHERE v =3D pg=5Fbackend=5Fpid();
INSERT INTO test(v) SELECT pg=5Fbackend=5Fpid();
PREPARE TRANSACTION $$:mygid$$;
COMMIT PREPARED $$:mygid$$;
=C2=A0
What do you think?
=C2=A0
With best regards,
Vitaly Davydov

------=_=-_OpenGroupware_org_NGMime-588472-1708608583.430853-0------
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Content-Length: 3793

<html><p>Dear All,</p><p>I'd like to present and talk about a problem w=
hen 2PC transactions are applied quite slowly on a replica during logic=
al replication. There is a master and a replica with established logica=
l replication from the master to the replica with twophase =3D true. Wi=
th some load level on the master, the replica starts to lag behind the =
master, and the lag will be increasing. We have to significantly decrea=
se the load on the master to allow replica to complete the catchup. Suc=
h problem may create significant difficulties in the production. The pr=
oblem appears at least on REL=5F16=5FSTABLE branch.</p><p>To reproduce =
the problem:</p><ul><li>Setup logical replication from master to replic=
a with subscription parameter twophase =3D&nbsp; true.</li><li>Create s=
ome intermediate load on the master (use pgbench with custom sql with p=
repare+commit)</li><li>Optionally switch off the replica for some time =
(keep load on master).</li><li>Switch on the replica and wait until it =
reaches the master.</li></ul><p>The replica will never reach the master=
 with even some low load on the master. If to remove the load, the repl=
ica will reach the master for much greater time, than expected. I tried=
 the same for regular transactions, but such problem doesn't appear eve=
n with a decent load.</p><p>I think, the main proplem of 2PC catchup ba=
d performance - the lack of asynchronous commit support for 2PC. For re=
gular transactions asynchronous commit is used on the replica by defaul=
t (subscrition sycnronous=5Fcommit =3D off). It allows the replication =
worker process on the replica to avoid fsync (XLogFLush) and to utilize=
 100% CPU (the background wal writer or checkpointer will do fsync). I =
agree, 2PC are mostly used in multimaster configurations with two or mo=
re nodes which are performed synchronously, but when the node in catchu=
p (node is not online in a multimaster cluster), asynchronous commit ha=
ve to be used to speedup the catchup.</p><p>There is another thing that=
 affects on the disbalance of the master and replica performance. When =
the master executes requestes from multiple clients, there is a fsync o=
ptimization takes place in XLogFlush. It allows to decrease the number =
of fsync in case when a number of parallel backends write to the WAL si=
multaneously. The replica applies received transactions in one thread s=
equentially, such optimization is not applied.</p><p>I see some possibl=
e solutions:</p><ul><li>Implement asyncronous commit for 2PC transactio=
ns.</li><li>Do some hacking with enableFsync when it is possible.</li><=
/ul><p>I think, asynchronous commit support for 2PC transactions should=
 significantly increase replica performance and help to solve this prob=
lem. I tried to implement it (like for usual transactions) but I've fou=
nd another problem: 2PC state is stored in WAL on prepare, on commit we=
 have to read 2PC state from WAL but the read is delayed until WAL is f=
lushed by the background wal writer (read LSN should be less than flush=
 LSN). Storing 2PC state in a shared memory (as it proposed earlier) ma=
y help.</p><p><br />I used the following query to monitor the catchup p=
rogress on the master:</p><pre><code>SELECT sent=5Flsn, pg=5Fcurrent=5F=
wal=5Flsn() FROM pg=5Fstat=5Freplication;</code></pre><p>I used the fol=
lowing script for pgbench to the master:</p><pre><code>SELECT md5(rando=
m()::text) as mygid \gset
BEGIN;
DELETE FROM test WHERE v =3D pg=5Fbackend=5Fpid();
INSERT INTO test(v) SELECT pg=5Fbackend=5Fpid();
PREPARE TRANSACTION $$:mygid$$;
COMMIT PREPARED $$:mygid$$;</code></pre><p>&nbsp;</p><p>What do you thi=
nk?</p><p>&nbsp;</p><p>With best regards,</p><p>Vitaly Davydov</p></htm=
l>

------=_=-_OpenGroupware_org_NGMime-588472-1708608583.430853-0--------