Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rexbn-00EZIQ-GW for pgsql-hackers@arkaria.postgresql.org; Tue, 27 Feb 2024 13:35:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rexbk-00343Y-T6 for pgsql-hackers@arkaria.postgresql.org; Tue, 27 Feb 2024 13:34:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rexbj-00343P-Ub for pgsql-hackers@lists.postgresql.org; Tue, 27 Feb 2024 13:34:56 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rexbe-001VPZ-Cj for pgsql-hackers@lists.postgresql.org; Tue, 27 Feb 2024 13:34:54 +0000 Received: from sogo.postgrespro.ru (unknown [192.168.2.5]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix/587) with ESMTPS id 9AC94E2107D; Tue, 27 Feb 2024 16:34:47 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1709040887; bh=7n79bXCd4CcGUyman4FceawNbR+Rh7FYMuu9EiafINg=; h=From:In-Reply-To:References:Date:Cc:To:Message-ID:Subject: User-Agent:From; b=zOHbkjYePRS13Qb4Tnh1mbdHPEtWF3GRcEw0lh4kLsC0cskaW9oAu3eF9cNN5v2Po QVfTXxMuiVSrN2m/2w0X07ZiOXb4KplzRivkkNnHSFwhsTjrbHjNnLq1GJBRx7/Ci2 a24hb11mLmzx1WtEb6qx1RdOiiPUT12e1srsSGUUplNDvbKtypkvFaFPtIDigpK4RS LWloIZV1rTGxOS5A/PuuuC7LR45s0Edw6leSx9EJrwJ+HFwVdmSgc6EMqZBAgLuUVy VaSvJ3aBDepbA/03ax/u8oHuLd7XkckjIP7/A/6fdE/vniPTyqSh+GpViVddKUQYrT KihvM7A6DoU/A== 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?= In-Reply-To: Content-Type: multipart/alternative; boundary="----=_=-_OpenGroupware_org_NGMime-765391-1709040887.498834-0------" X-Forward: 93.174.131.141 References: <8fab8-65d74c80-1-2f28e880@39088166> <99df2-65d8d200-1-63c26880@172687752> Date: Tue, 27 Feb 2024 16:34:47 +0300 Cc: pgsql-hackers@lists.postgresql.org To: "Amit Kapila" MIME-Version: 1.0 Message-ID: Subject: =?utf-8?q?Re=3A?= Slow catchup of 2PC (twophase) transactions on replica in LR User-Agent: SOGoMail 5.9.1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_=-_OpenGroupware_org_NGMime-765391-1709040887.498834-0------ Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Content-Length: 3105 Hi Amit, On Tuesday, February 27, 2024 16:00 MSK, Amit Kapila wrote: As we do XLogFlush() at the time of prepare then why it is not availabl= e? OR are you talking about this state after your idea/patch where you = are trying to make both Prepare and Commit=5Fprepared records async?Rig= ht, I'm talking about my patch where async commit is implemented. There= is no such problem with reading 2PC from not flushed WAL in the vanill= a because XLogFlush is called unconditionally, as you've described. But= an attempt to add some async stuff leads to the problem of reading not= flushed WAL. It is why I store 2pc state in the local memory in my pat= ch. It would be good if you could link those threads.Sure, I will find and = add some links to the discussions from past. Thank you! With best regards, Vitaly =C2=A0On Tue, Feb 27, 2024 at 4:49=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 wrote: > > Thank you for your interest in the discussion! > > On Monday, February 26, 2024 16:24 MSK, Amit Kapila wrote: > > > I think the reason is probably that when the WAL record for prepared = is already flushed then what will be the idea of async commit here? > > I think, the idea of async commit should be applied for both transact= ions: PREPARE and COMMIT PREPARED, which are actually two separate loca= l transactions. For both these transactions we may call XLogSetAsyncXac= tLSN on commit instead of XLogFlush when async commit is enabled. When = I use async commit, I mean to apply async commit to local transactions,= not to a twophase (prepared) transaction itself. > > > At commit prepared, it seems we read prepare's WAL record, right? If = so, it is not clear to me do you see a problem with a flush of commit=5F= prepared or reading WAL for prepared or both of these. > > The problem with reading WAL is due to async commit of PREPARE TRANSA= CTION which saves 2PC in the WAL. At the moment of COMMIT PREPARED the = WAL with PREPARE TRANSACTION 2PC state may not be XLogFlush-ed yet. > As we do XLogFlush() at the time of prepare then why it is not available? OR are you talking about this state after your idea/patch where you are trying to make both Prepare and Commit=5Fprepared records async? So, PREPARE TRANSACTION should wait until its 2PC state is flushed. > > I did some experiments with saving 2PC state in the local memory of l= ogical replication worker and, I think, it worked and demonstrated much= better performance. Logical replication worker utilized up to 100% CPU= . I'm just concerned about possible problems with async commit for twop= hase transactions. > > To be more specific, I've attached a patch to support async commit fo= r twophase. It is not the final patch but it is presented only for disc= ussion purposes. There were some attempts to save 2PC in memory in past= but it was rejected. > It would be good if you could link those threads. -- With Regards, Amit Kapila. =C2=A0 =C2=A0 ------=_=-_OpenGroupware_org_NGMime-765391-1709040887.498834-0------ Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Content-Length: 3664 Hi Amit,

On Tuesday, February 27, 2024 16:00 MSK, Amit= Kapila <amit.kapila16@gmail.com> wrote:

As we do X= LogFlush() at the time of prepare then why it is not available? OR are = you talking about this state after your idea/patch where you are trying= to make both Prepare and Commit=5Fprepared records async?

Right, I'm talking about my patch where async commit is implemented= . There is no such problem with reading 2PC from not flushed WAL in the= vanilla because XLogFlush is called unconditionally, as you've describ= ed. But an attempt to add some async stuff leads to the problem of read= ing not flushed WAL. It is why I store 2pc state in the local memory in= my patch.

It would be good if you could link those threa= ds.

Sure, I will find and add some links to the discuss= ions from past.

Thank you!

With best regards,
Vitaly
 
On Tue, Feb 27, 20= 24 at 4:49=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&= gt; wrote:
>
> Thank you for your interest in the discu= ssion!
>
> On Monday, February 26, 2024 16:24 MSK, Amit= Kapila <amit.kapila16@gmail.com> wrote:
>
>
> I think the reason is probably that when the WAL record for prepa= red is already flushed then what will be the idea of async commit here?=
>
> I think, the idea of async commit should be applie= d for both transactions: PREPARE and COMMIT PREPARED, which are actuall= y two separate local transactions. For both these transactions we may c= all XLogSetAsyncXactLSN on commit instead of XLogFlush when async commi= t is enabled. When I use async commit, I mean to apply async commit to = local transactions, not to a twophase (prepared) transaction itself.>
>
> At commit prepared, it seems we read prepar= e's WAL record, right? If so, it is not clear to me do you see a proble= m with a flush of commit=5Fprepared or reading WAL for prepared or both= of these.
>
> The problem with reading WAL is due to a= sync commit of PREPARE TRANSACTION which saves 2PC in the WAL. At the m= oment of COMMIT PREPARED the WAL with PREPARE TRANSACTION 2PC state may= not be XLogFlush-ed yet.
>

As we do XLogFlush() at = the time of prepare then why it is not
available? OR are you talki= ng about this state after your idea/patch
where you are trying to = make both Prepare and Commit=5Fprepared records
async?

= So, PREPARE TRANSACTION should wait until its 2PC state is flushed.
>
> I did some experiments with saving 2PC state in the lo= cal memory of logical replication worker and, I think, it worked and de= monstrated much better performance. Logical replication worker utilized= up to 100% CPU. I'm just concerned about possible problems with async = commit for twophase transactions.
>
> To be more specif= ic, I've attached a patch to support async commit for twophase. It is n= ot the final patch but it is presented only for discussion purposes. Th= ere were some attempts to save 2PC in memory in past but it was rejecte= d.
>

It would be good if you could link those thread= s.

--
With Regards,
Amit Kapila.

 =


  ------=_=-_OpenGroupware_org_NGMime-765391-1709040887.498834-0--------