Subject: Re: IO related waits

From: veem v <[email-masked]>
Date: Thu, 19 Sep 2024 02:01:39 +0530

On Wed, 18 Sept 2024 at 05:07, Adrian Klaver <[email-masked]>
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-tools-for-vscode/getting-started/disabling-and-enabling-auto-commit.html
>
>
> https://learn.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-ver16
>
> You probably need to take a closer look at the client/driver you are
> using and the code that interacting with it.
>
> In fact I would say you need to review the entire data transfer process
> to see if there are performance gains that can be obtained without
> adding an entirely new async component.
>
>
>
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 the batch we do insert into the
parent first and then to the child , but this does happen from multiple
sessions for different batches. So why do we see below error, as we
ensure in each batch we first insert into parent and then into the child
tables?

caused by: org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 10443 waits for ShareLock on transaction 220972157;
blocked by process 10454.
Process 10454 waits for ShareLock on transaction 220972155; blocked by
process 10443.
  Hint: See server log for query details.
  Where: while inserting index tuple (88736,28) in relation
"TAB1_p2024_08_29"

---

From: veem v <[email-masked]>
Date: Thu, 19 Sep 2024 02:10:54 +0530

>   Where: while inserting index tuple (88736,28) in relation

As we are able to get hold of one session, we see "insert into <parent
partition table>" was blocked by "insert into <child partition table>". And
the "insert into  <child partition table> " was experiencing a "client
read" wait event. Still unable to understand why it's happening and how to
fix it?

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 18 Sep 2024 14:32:48 -0700

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.
> 
>     relationships between the tables and during the batch we do insert
>     into the parent first and then to the child , but this does happen
>     from multiple sessions for different batches. So why do we see below
>     error, as we ensure in each batch we first insert into parent and
>     then into the child tables?
> 
>        Detail: Process 10443 waits for ShareLock on transaction
>     220972157; blocked by process 10454.
>     Process 10454 waits for ShareLock on transaction 220972155; blocked
>     by process 10443.
> partition table>" was blocked by "insert into <child partition table>". 
> And the "insert into <child partition table> " was experiencing a 
> "client read" wait event. Still unable to understand why it's happening 
> and how to fix it?

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?

3) What is the code that is generating the error?

Overall it looks like this process needs a top to bottom audit to map 
out what is actually being done versus what needs to be done.

-- 
Adrian Klaver
[email-masked]

---

From: veem v <[email-masked]>
Date: Thu, 19 Sep 2024 14:47:21 +0530

> out what is actually being done versus what needs to be done.
Yes the tables were actually having parent and child table relationships,
not the child/parent table in partitioning scheme.  And the PK and FK are
on columns - (ID, part_date) .The table is the daily range partitioned on
column part_date.

*The error we are seeing is as below in logs:-*

deadlock detected
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.
Process 14548 waits for ShareLock on transaction 220975630; blocked by
process 14537.
Process 14537: INSERT INTO TRANDB.PART_TAB (ID, part_date....)  VALUES ($1,
$2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID,
part_date) DO NOTHING
Process 14548: INSERT INTO TRANDB.PART_TAB (ID, part_date, ...)  VALUES
($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT
(ID, part_date) DO NOTHING

2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:HINT:  See
server log for query details.
2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:CONTEXT:
 while inserting index tuple (88814,39) in relation "PART_TAB_p2024_08_29"

2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STATEMENT:
 INSERT INTO TRANDB.PART_TAB (ID, part_date, ...)  VALUES ($1, $2, $3, $4,
$5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID, part_date) DO
NOTHING

2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:ERROR:
 current transaction is aborted, commands ignored until end of transaction
block
2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STATEMENT:
 INSERT INTO TRANDB.EXCEP_TAB (...)
2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:ERROR:
 current transaction is aborted, commands ignored until end of transaction
block
2024-09-18 17:05:56 UTC:100.72.22.33(54582):USER1@TRANDB:[14537]:STATEMENT:

2024-09-18 17:05:56 UTC:100.72.22.33(36096):USER1@TRANDB:[14551]:ERROR:
 current transaction is aborted, commands ignored until end of transaction
block

*********

2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:ERROR:
 deadlock detected
2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:DETAIL:
 Process 17456 waits for ShareLock on transaction 220978890; blocked by
process 17458.
Process 17458 waits for ShareLock on transaction 220978889; blocked by
process 17456.
Process 17456: INSERT INTO TRANDB.PART_TAB (ID, part_date, ...)  VALUES
($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT
(ID, part_date) DO NOTHING
Process 17458: INSERT INTO TRANDB.PART_TAB (ID, part_date, ..)  VALUES ($1,
$2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID,
part_date) DO NOTHING
2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:HINT:  See
server log for query details.
2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:CONTEXT:
 while inserting index tuple (88875,13) in relation "PART_TAB_p2024_08_29"
2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:STATEMENT:
 INSERT INTO TRANDB.PART_TAB (ID, part_date,..)  VALUES ($1, $2, $3, $4,
$5, $6, $7, $8, $9, $10, $11, $12, $13, $14) ON CONFLICT (ID, part_date) DO
NOTHING
2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:LOG:
 could not receive data from client: Connection reset by peer
2024-09-18 17:41:05 UTC:100.72.39.47(58778):USER1@TRANDB:[17456]:LOG:
 disconnection: session time: 0:08:37.154 user=USER1 database=TRANDB
host=XXXXX port=58778

---

From: Greg Sabino Mullane <[email-masked]>
Date: Thu, 19 Sep 2024 08:24:19 -0400

>  Process 14537 waits for ShareLock on transaction 220975629; blocked by

You need to find out exactly what commands, and in what order, all these
processes are doing. Deadlocks can be avoided by rearranging your
application logic.

>  current transaction is aborted, commands ignored until end of transaction

Fix your application. It should be checking that each command completed and
not just blindly pushing on to the next statement while ignoring the error.

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 describing
exactly what your application does, and how it is doing it. Switch from
reactive to proactive.

Cheers,
Greg

---

From: veem v <[email-masked]>
Date: Thu, 19 Sep 2024 18:33:04 +0530

On Thu, 19 Sept 2024 at 17:54, Greg Sabino Mullane <[email-masked]>
wrote:

> Fix your application. It should be checking that each command completed
> and not just blindly pushing on to the next statement while ignoring the
> error.
>
Thank you Greg.

I was thinking there might be some oddity or difference in the behaviour
here in postgres as compared to others, because I have seen deadlock due to
UPDATES but never seen deadlocks with INSERT queries before in other
databases (though here we have "insert on conflict do nothing"). But I am
now thinking , here we have foreign keys and primary keys exist and if the
same PK gets inserted from multiple sessions then one will wait if the
other has not been committed and that might be creating a situation of
locking first and subsequently deadlock.

But also we are doing batch inserts from multiple sessions but each session
will first insert into the parent and then into the child table for those
related to PK and FK and it should not overlap across sessions. So I will
check if there is a loophole there.

Also another thing which we encountered here , if the session gets errors
out with any error(may be deadlock etc) , it's not executing any further
transactions and erroring out with "*current transaction aborted, command
ignored until end of transaction block*". And it seems it will need
explicit "rollback" and will not be the default rollback, which I was
expecting it to do.

Regards
Veem

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 19 Sep 2024 08:10:07 -0700

On 9/19/24 05:24, Greg Sabino Mullane wrote:
> On Thu, Sep 19, 2024 at 5:17 AM veem v <[email-masked] 

> 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 
> describing exactly what your application does, and how it is doing it. 
> Switch from reactive to proactive.

+1

---

From: veem v <[email-masked]>
Date: Sat, 21 Sep 2024 01:31:23 +0530

> > Switch from reactive to proactive.
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 breaking the code
with deadlock error during race conditions where a lot of parallel threads
are operating. So how should we handle this scenario? Will setting the
"lock_timeout" parameter at session level will help us anyway here?

Create table t1(id numeric primary key);

Session 1:-
Begin
Insert into table1 values(1) on conflict(id) do nothing;

Session 2:
Begin
Insert into table1 values(2) on conflict(id) do nothing;

session 1:-
Insert into table1 values (2) on conflict(id) do nothing;

It got hung as it waits for the session-2 to commit/rollback the transaction

Session-2:-
Insert into table1 values(1) on conflict(id) do nothing;

deadlock detected... and this session terminated.

Regards
Veem

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 Sep 2024 16:47:08 -0400

veem v <[email-masked]> writes:
> are operating. So how should we handle this scenario?

Do you have to batch multiple insertions into a single transaction?
If so, can you arrange to order them consistently across transactions
(eg, sort by primary key before inserting)?

			regards, tom lane

---

From: Ron Johnson <[email-masked]>
Date: Fri, 20 Sep 2024 17:04:24 -0400

> (eg, sort by primary key before inserting)?

That's exactly what I did back in the day.  Because of database buffering,
sorting the data file at the OS level made the job 3x as fast as when the
input data was random.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 20 Sep 2024 14:11:38 -0700

On 9/20/24 1:01 PM, veem v wrote:
> 
> On Thu, 19 Sept, 2024, 8:40 pm Adrian Klaver, <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 
>     <mailto:[email-masked]>
> 
> 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 

Yeah, as I see it that would not work with concurrent uncommitted 
sessions as it would be unresolved whether a conflict actually exists 
until at least one of the sessions completes.

> with deadlock error during race conditions where a lot of parallel 
> threads are operating. So how should we handle this scenario? Will 
> setting the "lock_timeout" parameter at session level will help us 
> anyway here?

Serializable transaction?:

https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE

Or change the application code to not have this:

"... legitimate scenario in which the same ID can get inserted from 
multiple sessions ..."
-- 
Adrian Klaver
[email-masked]

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sat, 21 Sep 2024 00:17:18 +0200

On 2024-09-20 14:11:38 -0700, Adrian Klaver wrote:
> Yeah, as I see it that would not work with concurrent uncommitted sessions
> as it would be unresolved whether a conflict actually exists until at least
> one of the sessions completes.
> 

Doesn't help here, at least not directly. It would help indirectly
because isolation level serializable makes it very proable that
serialization errors occur. So an application designed for serializable
would have some kind of retry logic already in place. 

SO that leads as to another solution:

Retry each batch (possibly after reducing the batch size) until it
succeeds.

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: veem v <[email-masked]>
Date: Sat, 21 Sep 2024 15:06:45 +0530

On Sat, 21 Sept 2024 at 03:47, Peter J. Holzer <[email-masked]> wrote:

> sessions
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 sessions spawning at the same time to process
the incoming messages 24/7. And also as described in another ongoing thread
and also we saw in the past we did not see much difference between "batch
commit" and "Batch insert" performance. We only realized the row by row
commit is having worse performance.

Now, in such a scenario when the messages are coming from a streaming
framework using kafka +flink and also the insert is happening using row by
row only (but just the commit is shifted to batch), i don't see any way to
sort the ID columns in this streaming process, so that they won't overlap
across session.

In such a situation , the only way seems to have the messages replayed for
which the deadlock error happens , as I think during a deadlock error, one
session gets terminated by postgres and that messages perhap we can save in
some exception table and then replay?

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sat, 21 Sep 2024 15:19:31 +0200

On 2024-09-21 15:06:45 +0530, 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 that (thus "On conflict Do nothing" is
>     > > used) row. But as we see it's breaking the code
>     >
>     > Yeah, as I see it that would not work with concurrent
>     > uncommitted sessions as it would be unresolved whether a
>     > conflict actually exists until at least one of the sessions
>     > completes.
>     >
>     > > with deadlock error during race conditions where a lot of
>     > > parallel threads are operating. So how should we handle this
>     > > scenario? Will setting the "lock_timeout" parameter at session
>     > > level will help us anyway here?
[...]
> 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 sessions spawning at
> the same time to process the incoming messages 24/7. And also as
> described in another ongoing thread and also we saw in the past we did
> not see much difference between "batch commit" and "Batch insert"
> performance. We only realized the row by row commit is having
> worse performance.
> 
> framework using kafka +flink and also the insert is happening using
> row by row only (but just the commit is shifted to batch), i don't see
> any way to sort the ID columns in this streaming process, so that they
> won't overlap across session.

I have never used Kafka or Flink, but I imagine that they can stream to
various targets, not just to PostgreSQL.

So I would write a program which can receive such a stream. This program
would then buffer rows until either a number of rows or some timeout was
exceeded. It could then preprocess those rows (e.g. by sorting them) and
then open a transaction and try to insert them. If the transaction fails
because of a deadlock, serialization error or similar, simply retry the
transaction. If everything went well, go back to receiving the next
batch of rows.

> In such a situation , the only way seems to have the messages replayed
> for which the deadlock error happens , as I think during a deadlock
> error, one session gets terminated by postgres

No, the *session* doesn't get terminated. The transaction fails. It can
be rolled back and tried again.

> and that messages perhap we can save in some exception table and then
> replay? 

Not sure what you mean by "exception table", but if you mean that you
keep all the rows from that transaction around until the commit succeeds
(so that you can try to insert them again if the transaction fails),
then yes, that's what I meant.

        hp

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 21 Sep 2024 10:20:27 -0700

On 9/21/24 02:36, veem v wrote:

> 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 sessions spawning at the same 
> time to process the incoming messages 24/7. And also as described in 
> another ongoing thread and also we saw in the past we did not see much 
> difference between "batch commit" and "Batch insert" performance. We 
> only realized the row by row commit is having worse performance.

The bottom line is that to solve this a cost is going to have to be paid 
somewhere. Previously  it was done with autocommit in the form of slow 
insert performance. You improved the speed of the inserts by wrapping 
multiple inserts in transactions and that led you to this problem, where 
open transactions across sessions is leading to deadlock issues due to 
the same id being inserted in concurrent open sessions. Per my and Greg 
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 
portions of the process without reference to the process as a whole.At 
some point the parallel duplicate ids(records) need to be straightened 
out into a serial application of data. You and the other people involved 
need to come up with a coherent view of the process as whole with a goal 
to achieving that. Then you can start planning on where that cost is 
best paid: 1) In the data before the streaming. 2) In the streaming 
process itself 3) In the database or 4) Spread out across 1-4.

> framework using kafka +flink and also the insert is happening using row 
> by row only (but just the commit is shifted to batch), i don't see any 
> way to sort the ID columns in this streaming process, so that they 
> error, one session gets terminated by postgres and that messages perhap 
> we can save in some exception table and then replay?
>

---

From: Greg Sabino Mullane <[email-masked]>
Date: Sun, 22 Sep 2024 10:11:57 -0400

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 transaction ends, so at that point, we switch from advisory
locks to the ON CONFLICT clause, which can only work smoothly if other
processes can see the new row right away.

drop table if exists t1;
create table t1(id int primary key);

-- Session 1:
begin transaction isolation level read committed;

-- inserts one row:
insert into t1 select 1 where pg_try_advisory_xact_lock(123,1)
  on conflict(id) do nothing;

-- Session 2:
begin transaction isolation level read committed;

-- inserts one row:
insert into t1 select 2 where pg_try_advisory_xact_lock(123,2)
  on conflict(id) do nothing;

-- Session 1:

-- silently 'fails' because no lock is granted, so inserts zero rows:
insert into t1 select 2 where pg_try_advisory_xact_lock(123,2)
  on conflict(id) do nothing;

-- Session 2:

-- silently 'fails' because no lock is granted, so inserts zero rows:
insert into t1 select 1 where pg_try_advisory_xact_lock(123,1)
  on conflict(id) do nothing;

-- inserts one row:
insert into t1 select 3 where pg_try_advisory_xact_lock(123,3)
  on conflict(id) do nothing;

commit; -- lock on 2 and 3 goes away

-- Session 1:

-- silently fails because of the on conflict clause
insert into t1 select 3 where pg_try_advisory_xact_lock(123,3)
  on conflict(id) do nothing;

Cheers,
Greg

---

From: rob stone <[email-masked]>
Date: Mon, 23 Sep 2024 14:43:46 +1000

Hello,

On Sat, 2024-09-21 at 10:20 -0700, Adrian Klaver wrote:
> > which 

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. 

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

Follow Adrian's advice. 

HTH,
Rob