Message-ID: <dbeae9e0004fa4b5d45f15f13d7ac4b7f6db4bf7.camel@tpg.com.au>
Subject: Re: IO related waits
From: rob stone <floriparob@tpg.com.au>
To: Adrian Klaver <adrian.klaver@aklaver.com>, veem v <veema0000@gmail.com>,
 	pgsql-general@lists.postgresql.org
Date: Mon, 23 Sep 2024 14:43:46 +1000
In-Reply-To: <55697e68-ee97-4571-9b71-c91a9373d40b@aklaver.com>
References: 
	<CAB+=1TV-b8=RGKVFN0qj5Nxdgbj-JYCNh4ZqFjxxmQPpFD6=PQ@mail.gmail.com>
	 <fd6afa3b-f5db-4632-8e25-678ef66703d5@aklaver.com>
	 <CAB+=1TXn-TZFC5QAFDshpRpnNhGrZ847sdaivNJRgot-6BuerA@mail.gmail.com>
	 <CAB+=1TWhGgybtCdU7oR3oghhXG6vULUEkebG1DbT+bc1v7yp4w@mail.gmail.com>
	 <586d988d-978b-4b08-84b4-ef3ebb8736a8@aklaver.com>
	 <CAB+=1TXcLJsZoGZ2qzv5ehauOa3GX_-cHth4NvZ2mgFsJPCffA@mail.gmail.com>
	 <CAKAnmmJhsNL=f+z7R018L6NrLQuq_7+quqZkGUKbzHRctHaY3w@mail.gmail.com>
	 <bc8363b5-28d0-4218-b3d6-73c7d78717da@aklaver.com>
	 <CAB+=1TXEJTMtypsZrNhGVEZxRV6jBop4hmMaR5DDsTLOoSqsdQ@mail.gmail.com>
	 <4cfe720e-4283-43fc-8d72-8fa11e2f741a@aklaver.com>
	 <20240920221718.ctq5whqfyqlrjwr2@hjp.at>
	 <CAB+=1TU7FcET2RDV0W969B3q0UB3xwiEVdPmqLybBmfqU2VC8w@mail.gmail.com>
	 <55697e68-ee97-4571-9b71-c91a9373d40b@aklaver.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.0-1 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/dbeae9e0004fa4b5d45f15f13d7ac4b7f6db4bf7.camel%40tpg.com.au>
Precedence: bulk

Hello,

On Sat, 2024-09-21 at 10:20 -0700, Adrian Klaver wrote:
> On 9/21/24 02:36, veem v wrote:
> >=20
> >=20
>=20
> >=20
> > Actually here the application is using kafka and=C2=A0 flink stream and
> > is=20
> > using one of existing code in=C2=A0which it was doing row by row=C2=A0c=
ommit
> > which=20
> > is now changed to Batch commit i.e. the commit point is shifted
> > from row=20
> > by row to batch now. There are multiple sessions spawning=C2=A0at the
> > same=20
> > time to process the incoming messages 24/7. And also as described
> > in=20
> > another ongoing thread and also we saw in the past we did not see
> > much=20
> > difference between "batch commit" and "Batch insert" performance.
> > We=20
> > only realized the row by row commit is having worse=C2=A0performance.
>=20
> The bottom line is that to solve this a cost is going to have to be
> paid=20
> somewhere. Previously=C2=A0 it was done with autocommit in the form of
> slow=20
> insert performance. You improved the speed of the inserts by wrapping
> multiple inserts in transactions and that led you to this problem,
> where=20
> open transactions across sessions is leading to deadlock issues due
> to=20
> the same id being inserted in concurrent open sessions. Per my and
> Greg=20
> Sabino Mullane comments the solution is going to need planning. Right
> now you are playing a round of Whack-a-Mole by making ad-hoc changes
> of=20
> portions of the process without reference to the process as a
> whole.At=20
> some point the parallel duplicate ids(records) need to be
> straightened=20
> out into a serial application of data. You and the other people
> involved=20
> need to come up with a coherent view of the process as whole with a
> goal=20
> to achieving that. Then you can start planning on where that cost is=20
> best paid: 1) In the data before the streaming. 2) In the streaming=20
> process itself 3) In the database or 4) Spread out across 1-4.
>=20

You are using Apache's flink to pump data into your database. It seems
to me that you have this occurring:-

pipelineA --> sessionA
pipelineB --> sessionB
       etc.

You haven't said if the SQL code doing the inserts is coming out of
flink or if it is your own code.
If it is your own code make sure you are handling SQLException events
properly.=20

If there is a flink mailing list or user group, you'll probably find
more help with other flink users.

Follow Adrian's advice.=20

HTH,
Rob