MIME-Version: 1.0
References: <8fab8-65d74c80-1-2f28e880@39088166>
In-Reply-To: <8fab8-65d74c80-1-2f28e880@39088166>
From: Amit Kapila <amit.kapila16@gmail.com>
Date: Fri, 23 Feb 2024 08:53:49 +0530
Message-ID: <CAA4eK1KOs3s6syZqUgrd2WvjTz64SGf0ToZcRoPMCKKH+M0YFQ@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: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAA4eK1KOs3s6syZqUgrd2WvjTz64SGf0ToZcRoPMCKKH%2BM0YFQ%40mail.gmail.com>
Precedence: bulk

On Thu, Feb 22, 2024 at 6:59=E2=80=AFPM =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:
>
> I'd like to present and talk about a problem when 2PC transactions are ap=
plied quite slowly on a replica during logical replication. There is a mast=
er and a replica with established logical replication from the master to th=
e replica with twophase =3D true. With some load level on the master, the r=
eplica 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 c=
omplete the catchup. Such problem may create significant difficulties in th=
e production. The problem appears at least on REL_16_STABLE branch.
>
> To reproduce the problem:
>
> Setup logical replication from master to replica with subscription parame=
ter 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 ma=
ster. If to remove the load, the replica will reach the master for much gre=
ater time, than expected. I tried the same for regular transactions, but su=
ch problem doesn't appear even with a decent load.
>
> I think, the main proplem of 2PC catchup bad performance - the lack of as=
ynchronous commit support for 2PC. For regular transactions asynchronous co=
mmit is used on the replica by default (subscrition sycnronous_commit =3D o=
ff). It allows the replication worker process on the replica to avoid fsync=
 (XLogFLush) and to utilize 100% CPU (the background wal writer or checkpoi=
nter will do fsync). I agree, 2PC are mostly used in multimaster configurat=
ions with two or more nodes which are performed synchronously, but when the=
 node in catchup (node is not online in a multimaster cluster), asynchronou=
s commit have to be used to speedup the catchup.
>

I don't see we do anything specific for 2PC transactions to make them
behave differently than regular transactions with respect to
synchronous_commit setting. What makes you think so? Can you pin point
the code you are referring to?

> There is another thing that affects on the disbalance of the master and r=
eplica performance. When the master executes requestes from multiple client=
s, there is a fsync optimization takes place in XLogFlush. It allows to dec=
rease the number of fsync in case when a number of parallel backends write =
to the WAL simultaneously. The replica applies received transactions in one=
 thread sequentially, such optimization is not applied.
>

Right, I think for this we need to implement parallel apply.

> I see some possible solutions:
>
> Implement asyncronous commit for 2PC transactions.
> Do some hacking with enableFsync when it is possible.
>

Can you be a bit more specific about what exactly you have in mind to
achieve the above solutions?

--=20
With Regards,
Amit Kapila.