Re: IO related waits

Hello, On Sat, 2024-09-21 at 10:20 -0700, Adrian Klaver wrote: > On 9/21/24 02:36, veem v wrote: > > > > > > > > > Actually here the application is using kafka and  flink stream and > > is > > using one of existing code in which it was doing row by row commit > > which > > is now changed to

Re: IO related waits

You may be able to solve this with advisory locks. In particular, transaction-level advisory locks with the "try-pass/fail" variant. Here, "123" is a unique number used by your app, related to this particular table. You also need to force read committed mode, as the advisory locks go away after the

Re: IO related waits

On 9/21/24 02:36, veem v wrote: Actually here the application is using kafka and  flink stream and is using one of existing code in which it was doing row by row commit which is now changed to Batch commit i.e. the commit point is shifted from row by row to batch now. There are multiple

Re: IO related waits

On 2024-09-21 15:06:45 +0530, veem v wrote: > On Sat, 21 Sept 2024 at 03:47, Peter J. Holzer wrote: > > On 2024-09-20 14:11:38 -0700, Adrian Klaver wrote: > > On 9/20/24 1:01 PM, veem v wrote: > > > Able to reproduce this deadlock graph as below.  Now my > > > question is , this i

Re: IO related waits

On Sat, 21 Sept 2024 at 03:47, Peter J. Holzer wrote: > On 2024-09-20 14:11:38 -0700, Adrian Klaver wrote: > > On 9/20/24 1:01 PM, veem v wrote: > > > Able to reproduce this deadlock graph as below. Now my question is , > > > this is a legitimate scenario in which the same ID can get inserted >

Re: IO related waits

On 2024-09-20 14:11:38 -0700, Adrian Klaver wrote: > On 9/20/24 1:01 PM, veem v wrote: > > Able to reproduce this deadlock graph as below.  Now my question is , > > this is a legitimate scenario in which the same ID can get inserted from > > multiple sessions and in such cases it's expected to skip

Re: IO related waits

On 9/20/24 1:01 PM, veem v wrote: On Thu, 19 Sept, 2024, 8:40 pm Adrian Klaver, > wrote: On 9/19/24 05:24, Greg Sabino Mullane wrote: > On Thu, Sep 19, 2024 at 5:17 AM veem v mailto:veema0...@gmail.com> > This is really difficult to diagnose

Re: IO related waits

On Fri, Sep 20, 2024 at 4:47 PM Tom Lane wrote: > veem v writes: > > Able to reproduce this deadlock graph as below. Now my question is , > this > > is a legitimate scenario in which the same ID can get inserted from > > multiple sessions and in such cases it's expected to skip that (thus "On >

Re: IO related waits

veem v writes: > Able to reproduce this deadlock graph as below. Now my question is , this > is a legitimate scenario in which the same ID can get inserted from > multiple sessions and in such cases it's expected to skip that (thus "On > conflict Do nothing" is used) row. But as we see it's break

Re: IO related waits

On Thu, 19 Sept, 2024, 8:40 pm Adrian Klaver, wrote: > On 9/19/24 05:24, Greg Sabino Mullane wrote: > > On Thu, Sep 19, 2024 at 5:17 AM veem v > > This is really difficult to diagnose from afar with only snippets of > > logs and half-complete descriptions of your business logic. Pull > > everyon

Re: IO related waits

On 9/19/24 05:24, Greg Sabino Mullane wrote: On Thu, Sep 19, 2024 at 5:17 AM veem v This is really difficult to diagnose from afar with only snippets of logs and half-complete descriptions of your business logic. Pull everyone involved into a room with a whiteboard, and produce a document des

Re: IO related waits

On Thu, 19 Sept 2024 at 17:54, Greg Sabino Mullane wrote: > On Thu, Sep 19, 2024 at 5:17 AM veem v wrote: > >> 2024-09-18 17:05:56 UTC:100.72.10.66(54582):USER1@TRANDB:[14537]:DETAIL: >> Process 14537 waits for ShareLock on transaction 220975629; blocked by >> process 14548. >> > > You need to

Re: IO related waits

On Thu, Sep 19, 2024 at 5:17 AM veem v wrote: > 2024-09-18 17:05:56 UTC:100.72.10.66(54582):USER1@TRANDB:[14537]:DETAIL: > Process 14537 waits for ShareLock on transaction 220975629; blocked by > process 14548. > You need to find out exactly what commands, and in what order, all these processes

Re: IO related waits

On Thu, 19 Sept 2024 at 03:02, Adrian Klaver wrote: > > > This needs clarification. > > 1) To be clear when you refer to parent and child that is: > FK > parent_tbl.fld <--> child_tbl.fld_fk > > not parent and child tables in partitioning scheme? > > 2) What are the table schemas

Re: IO related waits

On 9/18/24 1:40 PM, veem v wrote: You were spot on. When we turned off the "auto commit" we started seeing less number of commits as per the number of batches. However we also started seeing deadlock issues. We have foreign key relationships between the tables and during t

Re: IO related waits

On Thu, 19 Sept 2024 at 02:01, veem v wrote: > > On Wed, 18 Sept 2024 at 05:07, Adrian Klaver > wrote: > >> On 9/17/24 12:34, veem v wrote: >> > >> >> It does if autocommit is set in the client, that is common to other >> databases also: >> >> https://dev.mysql.com/doc/refman/8.4/en/commit.html

Re: IO related waits

On Wed, 18 Sept 2024 at 05:07, Adrian Klaver wrote: > On 9/17/24 12:34, veem v wrote: > > > > It does if autocommit is set in the client, that is common to other > databases also: > > https://dev.mysql.com/doc/refman/8.4/en/commit.html > > > https://docs.oracle.com/en/database/oracle/developer-to

Re: IO related waits

On 9/17/24 12:34, veem v wrote: On Tue, 17 Sept 2024 at 21:24, Adrian Klaver > wrote: Which means you need to on Flink end: 1) Use Flink async I/O . 2) Find a client that supports async or fake it by using multiple synchronous clients.

Re: IO related waits

On Tue, 17 Sept 2024 at 21:24, Adrian Klaver wrote: > > Which means you need to on Flink end: > > 1) Use Flink async I/O . > > 2) Find a client that supports async or fake it by using multiple > synchronous clients. > > On Postgres end there is this: > > https://www.postgresql.org/docs/current/wa

Re: IO related waits

On 9/16/24 20:55, veem v wrote: On Tue, 17 Sept 2024 at 03:41, Adrian Klaver > wrote: Are you referring to this?: https://nightlies.apache.org/flink/flink-docs-release-1.20/docs/dev/datastream/operators/asyncio/

Re: IO related waits

On Tue, 17 Sept 2024 at 18:43, Greg Sabino Mullane wrote: > > This is a better place to optimize. Batch many rows per transaction. > Remove unused indexes. > > flushing of the WAL to the disk has to happen anyway(just that it will be >> delayed now), so can this method cause contention in the dat

Re: IO related waits

On Mon, Sep 16, 2024 at 11:56 PM veem v wrote: > So what can be the caveats in this approach, considering transactions > meant to be ACID compliant as financial transactions. > Financial transactions need to be handled with care. Only you know your business requirements, but as Christophe pointe

Re: IO related waits

On Tue, 17 Sept 2024 at 03:41, Adrian Klaver wrote: > > Are you referring to this?: > > > https://nightlies.apache.org/flink/flink-docs-release-1.20/docs/dev/datastream/operators/asyncio/ > > If not then you will need to be more specific. > > Yes, I was referring to this one. So what can be the c

Re: IO related waits

On 9/16/24 13:24, veem v wrote: Hi, One of our application using RDS postgres. In one of our streaming applications(using flink) which processes 100's of millions of transactions each day, we are using row by row transaction processing for inserting data into the postgres database and commit i

Re: IO related waits

> On Sep 16, 2024, at 13:24, veem v wrote: > Architecture team is suggesting to enable asynch io if possible, so that the > streaming client will not wait for the commit confirmation from the database. > So I want to understand , how asynch io can be enabled and if any downsides > of doing t

IO related waits

Hi, One of our application using RDS postgres. In one of our streaming applications(using flink) which processes 100's of millions of transactions each day, we are using row by row transaction processing for inserting data into the postgres database and commit is performed for each row. We are seei