public inbox for [email protected]
help / color / mirror / Atom feedRe: IO related waits
17+ messages / 7 participants
[nested] [flat]
* Re: IO related waits
@ 2024-09-18 20:31 veem v <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: veem v @ 2024-09-18 20:31 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Christophe Pettus <[email protected]>; pgsql-general <[email protected]>
On Wed, 18 Sept 2024 at 05:07, Adrian Klaver <[email protected]>
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-...
>
>
> https://learn.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=s...
>
> 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"
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-18 20:40 veem v <[email protected]>
parent: veem v <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: veem v @ 2024-09-18 20:40 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Christophe Pettus <[email protected]>; pgsql-general <[email protected]>
On Thu, 19 Sept 2024 at 02:01, veem v <[email protected]> wrote:
>
> On Wed, 18 Sept 2024 at 05:07, Adrian Klaver <[email protected]>
> 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-...
>>
>>
>> https://learn.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=s...
>>
>> 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"
>
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?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-18 21:32 Adrian Klaver <[email protected]>
parent: veem v <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2024-09-18 21:32 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Christophe Pettus <[email protected]>; pgsql-general <[email protected]>
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 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"
>
>
> 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?
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 protected]
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-19 09:17 veem v <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: veem v @ 2024-09-19 09:17 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Christophe Pettus <[email protected]>; pgsql-general <[email protected]>
On Thu, 19 Sept 2024 at 03:02, Adrian Klaver <[email protected]>
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?
>
> 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.
>
>
>
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-19 12:24 Greg Sabino Mullane <[email protected]>
parent: veem v <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Greg Sabino Mullane @ 2024-09-19 12:24 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: Adrian Klaver <[email protected]>; Christophe Pettus <[email protected]>; pgsql-general <[email protected]>
On Thu, Sep 19, 2024 at 5:17 AM veem v <[email protected]> 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 are doing. Deadlocks can be avoided by rearranging your
application logic.
> 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
>
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-19 13:03 veem v <[email protected]>
parent: Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: veem v @ 2024-09-19 13:03 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: Adrian Klaver <[email protected]>; Christophe Pettus <[email protected]>; pgsql-general <[email protected]>
On Thu, 19 Sept 2024 at 17:54, Greg Sabino Mullane <[email protected]>
wrote:
> On Thu, Sep 19, 2024 at 5:17 AM veem v <[email protected]> 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 are doing. Deadlocks can be avoided by rearranging your
> application logic.
>
>
>> 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
>>
>
> 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.
>
>
>
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-19 15:10 Adrian Klaver <[email protected]>
parent: Greg Sabino Mullane <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2024-09-19 15:10 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; veem v <[email protected]>; +Cc: Christophe Pettus <[email protected]>; pgsql-general <[email protected]>
On 9/19/24 05:24, Greg Sabino Mullane wrote:
> On Thu, Sep 19, 2024 at 5:17 AM veem v <[email protected]
> 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
>
> Cheers,
> Greg
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-20 20:01 veem v <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 3 replies; 17+ messages in thread
From: veem v @ 2024-09-20 20:01 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Christophe Pettus <[email protected]>; pgsql-general <[email protected]>
On Thu, 19 Sept, 2024, 8:40 pm Adrian Klaver, <[email protected]>
wrote:
> On 9/19/24 05:24, Greg Sabino Mullane wrote:
> > On Thu, Sep 19, 2024 at 5:17 AM veem v <[email protected]
>
> > 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.
>
>
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-20 20:47 Tom Lane <[email protected]>
parent: veem v <[email protected]>
2 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2024-09-20 20:47 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: Adrian Klaver <[email protected]>; Greg Sabino Mullane <[email protected]>; Christophe Pettus <[email protected]>; pgsql-general <[email protected]>
veem v <[email protected]> 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 breaking the code
> with deadlock error during race conditions where a lot of parallel threads
> 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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-20 21:04 Ron Johnson <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Ron Johnson @ 2024-09-20 21:04 UTC (permalink / raw)
To: pgsql-general <[email protected]>
On Fri, Sep 20, 2024 at 4:47 PM Tom Lane <[email protected]> wrote:
> veem v <[email protected]> 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 breaking the code
> > with deadlock error during race conditions where a lot of parallel
> threads
> > 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)?
>
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!
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-20 21:11 Adrian Klaver <[email protected]>
parent: veem v <[email protected]>
2 siblings, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2024-09-20 21:11 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Christophe Pettus <[email protected]>; pgsql-general <[email protected]>
On 9/20/24 1:01 PM, veem v wrote:
>
> On Thu, 19 Sept, 2024, 8:40 pm Adrian Klaver, <[email protected]
> <mailto:[email protected]>> wrote:
>
> On 9/19/24 05:24, Greg Sabino Mullane wrote:
> > On Thu, Sep 19, 2024 at 5:17 AM veem v <[email protected]
> <mailto:[email protected]>
>
> > 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.
>
>
> 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?
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 protected]
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-20 22:17 Peter J. Holzer <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Peter J. Holzer @ 2024-09-20 22:17 UTC (permalink / raw)
To: [email protected]
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 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?
>
> Serializable transaction?:
>
> https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE
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 protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-21 09:36 veem v <[email protected]>
parent: Peter J. Holzer <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: veem v @ 2024-09-21 09:36 UTC (permalink / raw)
To: [email protected]
On Sat, 21 Sept 2024 at 03:47, Peter J. Holzer <[email protected]> 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
> 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?
> >
> > Serializable transaction?:
> >
> >
> https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE
>
> 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.
>
>
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?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-21 13:19 Peter J. Holzer <[email protected]>
parent: veem v <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: Peter J. Holzer @ 2024-09-21 13:19 UTC (permalink / raw)
To: [email protected]
On 2024-09-21 15:06:45 +0530, veem v wrote:
> On Sat, 21 Sept 2024 at 03:47, Peter J. Holzer <[email protected]> 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 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?
[...]
> SO that leads as to another solution:
>
> Retry each batch (possibly after reducing the batch size) until it
> succeeds.
>
>
>
> 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.
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
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-21 17:20 Adrian Klaver <[email protected]>
parent: veem v <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Adrian Klaver @ 2024-09-21 17:20 UTC (permalink / raw)
To: veem v <[email protected]>; [email protected]
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 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.
>
> 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?
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-22 14:11 Greg Sabino Mullane <[email protected]>
parent: veem v <[email protected]>
2 siblings, 0 replies; 17+ messages in thread
From: Greg Sabino Mullane @ 2024-09-22 14:11 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: Adrian Klaver <[email protected]>; Christophe Pettus <[email protected]>; pgsql-general <[email protected]>; Tom Lane <[email protected]>; Ron Johnson <[email protected]>; Peter J. Holzer <[email protected]>
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: IO related waits
@ 2024-09-23 04:43 rob stone <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: rob stone @ 2024-09-23 04:43 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; veem v <[email protected]>; [email protected]
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 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.
>
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
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2024-09-23 04:43 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-18 20:31 Re: IO related waits veem v <[email protected]>
2024-09-18 20:40 ` veem v <[email protected]>
2024-09-18 21:32 ` Adrian Klaver <[email protected]>
2024-09-19 09:17 ` veem v <[email protected]>
2024-09-19 12:24 ` Greg Sabino Mullane <[email protected]>
2024-09-19 13:03 ` veem v <[email protected]>
2024-09-19 15:10 ` Adrian Klaver <[email protected]>
2024-09-20 20:01 ` veem v <[email protected]>
2024-09-20 20:47 ` Tom Lane <[email protected]>
2024-09-20 21:04 ` Ron Johnson <[email protected]>
2024-09-20 21:11 ` Adrian Klaver <[email protected]>
2024-09-20 22:17 ` Peter J. Holzer <[email protected]>
2024-09-21 09:36 ` veem v <[email protected]>
2024-09-21 13:19 ` Peter J. Holzer <[email protected]>
2024-09-21 17:20 ` Adrian Klaver <[email protected]>
2024-09-23 04:43 ` rob stone <[email protected]>
2024-09-22 14:11 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox