MIME-Version: 1.0
References: <8fab8-65d74c80-1-2f28e880@39088166>
In-Reply-To: <8fab8-65d74c80-1-2f28e880@39088166>
From: Ajin Cherian <itsajin@gmail.com>
Date: Fri, 23 Feb 2024 15:52:11 +1100
Message-ID: <CAFPTHDaf9sc3VZWZKr3-xf2jv+gF6q3ywipofc-+5GHyCJRSCQ@mail.gmail.com>
Subject: Re: Slow catchup of 2PC (twophase) transactions on replica in LR
To: =?UTF-8?B?0JTQsNCy0YvQtNC+0LIg0JLQuNGC0LDQu9C40Lk=?= <v.davydov@postgrespro.ru>
Cc: pgsql-hackers@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000584ee8061205539d"
Archived-At: <https://www.postgresql.org/message-id/CAFPTHDaf9sc3VZWZKr3-xf2jv%2BgF6q3ywipofc-%2B5GHyCJRSCQ%40mail.gmail.com>
Precedence: bulk

--000000000000584ee8061205539d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Feb 23, 2024 at 12:29=E2=80=AFAM =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>
wrote:

> 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 master
> 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 significan=
t
> difficulties in the production. The problem appears at least on
> REL_16_STABLE branch.
>
> To reproduce the problem:
>
>    - Setup logical replication from master to replica with subscription
>    parameter twophase =3D  true.
>    - Create some intermediate load on the master (use pgbench with custom
>    sql with prepare+commit)
>    - Optionally switch off the replica for some time (keep load on
>    master).
>    - Switch on 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 much
> greater time, than expected. I tried the same for regular transactions, b=
ut
> such problem doesn't appear even with a decent load.
>
>
>
I tried this setup and I do see that the logical subscriber does reach the
master in a short time. I'm not sure what I'm missing. I stopped the
logical subscriber in between while pgbench was running and then started it
again and ran the following:
postgres=3D# SELECT sent_lsn, pg_current_wal_lsn() FROM pg_stat_replication=
;
 sent_lsn  | pg_current_wal_lsn
-----------+--------------------
 0/6793FA0 | 0/6793FA0 <=3D=3D=3D caught up
(1 row)

My pgbench command:
pgbench postgres -p 6972 -c 2 -j 3 -f /home/ajin/test.sql -T 200 -P 5

my custom sql file:
cat test.sql
SELECT md5(random()::text) as mygid \gset
BEGIN;
DELETE FROM test WHERE v =3D pg_backend_pid();
INSERT INTO test(v) SELECT pg_backend_pid();
PREPARE TRANSACTION $$:mygid$$;
COMMIT PREPARED $$:mygid$$;

regards,
Ajin Cherian
Fujitsu Australia

--000000000000584ee8061205539d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Fri, Feb 23, 2024 at 12:29=E2=80=
=AFAM =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 &lt;<a href=3D"mailto:v.davydov@postgrespro.ru">v.davydo=
v@postgrespro.ru</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex"><p>Dear All,</p><p>I&#39;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 logi=
cal replication from the master 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 loa=
d on the master to allow replica to complete the catchup. Such problem may =
create significant difficulties in the production. The problem appears at l=
east on REL_16_STABLE branch.</p><p>To reproduce the problem:</p><ul><li>Se=
tup logical replication from master to replica with subscription parameter =
twophase =3D=C2=A0 true.</li><li>Create some intermediate load on the maste=
r (use pgbench with custom sql with prepare+commit)</li><li>Optionally swit=
ch off the replica for some time (keep load on master).</li><li>Switch on t=
he replica and wait until it reaches the master.</li></ul><p>The replica wi=
ll never reach the master with even some low load on the master. If to remo=
ve the load, the replica will reach the master for much greater time, than =
expected. I tried the same for regular transactions, but such problem doesn=
&#39;t appear even with a decent load.</p><br><br></blockquote><div><br></d=
iv><div>I tried this setup and I do see that the logical subscriber does re=
ach the master in a short time. I&#39;m not sure what I&#39;m missing. I st=
opped the logical subscriber in between while pgbench was running and then =
started it again and ran the following:<br></div><div>postgres=3D# SELECT s=
ent_lsn, pg_current_wal_lsn() FROM pg_stat_replication;<br>=C2=A0sent_lsn =
=C2=A0| pg_current_wal_lsn <br>-----------+--------------------<br>=C2=A00/=
6793FA0 | 0/6793FA0 &lt;=3D=3D=3D caught up<br>(1 row)<br><br></div><div>My=
 pgbench command:</div><div>pgbench postgres -p 6972 -c 2 -j 3 -f /home/aji=
n/test.sql -T 200 -P 5<br></div><div><br></div><div>my custom sql file:<br>=
</div><div>cat test.sql <br>SELECT md5(random()::text) as mygid \gset<br>BE=
GIN;<br>	DELETE FROM test WHERE v =3D pg_backend_pid();<br>	INSERT INTO tes=
t(v) SELECT pg_backend_pid();<br>	PREPARE TRANSACTION $$:mygid$$;<br>	COMMI=
T PREPARED $$:mygid$$;</div><div><br></div><div>regards,</div><div>Ajin Che=
rian</div><div>Fujitsu Australia<br></div><div><br></div></div></div>

--000000000000584ee8061205539d--