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 1sr1UG-00EvIP-On for pgsql-general@arkaria.postgresql.org; Wed, 18 Sep 2024 20:41:20 +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 1sr1UE-00FQN2-IF for pgsql-general@arkaria.postgresql.org; Wed, 18 Sep 2024 20:41:18 +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 1sr1UE-00FQLx-0G for pgsql-general@lists.postgresql.org; Wed, 18 Sep 2024 20:41:18 +0000 Received: from mail-lj1-x233.google.com ([2a00:1450:4864:20::233]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sr1U6-001rS8-U9 for pgsql-general@lists.postgresql.org; Wed, 18 Sep 2024 20:41:16 +0000 Received: by mail-lj1-x233.google.com with SMTP id 38308e7fff4ca-2f74e613a10so1301891fa.1 for ; Wed, 18 Sep 2024 13:41:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726692067; x=1727296867; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=kzT1RfyhbGDwkHzGiC1aNBOrHq3nB7xEqX+KHK5tAVs=; b=RNXxFSlOoXrsNJsfcKU+6HfbjajAtwWnhJNlefwW9DMLthJ6osY2ONG5/7R3u51S2V gb+vLRold+d+fauOtFrlSJ197T8erlRaDwV+XURlIw0Xiq7fLvP0NTaFUDFP13R6gAVp dcZJAnc9WUHJ7JbpgfJrbG/4NPJKGftlUbbY+XsqJx1F+9weQeBoMjfE6Ekjx/NXLoAJ WXq+fu5a7avdEGtga666v0WjP5/3Lzg7MBxfeCIoUHM90vn7iN4MsYGIGGRkKsHeFdJs 9Z4ggpHwEpR0oIRy5fFmsaIgSw4+UUjb6Zt+bmFmErEZV8gkQgz61jDA5xDeGUMdLOvX prpw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726692067; x=1727296867; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=kzT1RfyhbGDwkHzGiC1aNBOrHq3nB7xEqX+KHK5tAVs=; b=M7QEg+v0Cij0a7m9JNE35Wm/HE2MYQnz+VMWWRFWuGl8eSpKvw+U4xB6WSqXaqr2RA SjLpqwQmBV45PQPHg7pbgKDpwAhHeePkMyIjX3lZPzQMBkyoazlDBaa1i0Jo6+YljbLu +MSZlwdwVSTlRWlUEpRnfljQ3g3hz3rWS3RpehvN6B1bQmPqxuCCl/i8Fy2Qhrd1MwKU KKgYmEh2SY2pda2N/qGwfVCdLxUhyMR+WgoCUKRMok20tdyAzhDDh36kmKKYQjGnTHaO cn1f87s4adoWu/8Qcczr0xQ80bEMdMhR+WNo1a8c8n4b2t7mBwaFcEaYBVdlLSXMNBYj Zx/A== X-Forwarded-Encrypted: i=1; AJvYcCVeE8TRjhRd7kx28lsagVhceYdn+/Vg/So7rlv4ZMTiufru/raeCKL+zvVMOPlz2G6WCIStzEiMJ3Lkj3Nn@lists.postgresql.org X-Gm-Message-State: AOJu0Yx5RViudG9ECmyd+tbnm8VwfVI8wLdfhPGk3+IbcElCtKYMP8uG lwPSI3h28LbF+hppFp/vh09RVrzKV1TPFoohkqbCyKuIOg5I5OaXYzMy3jEdeWDgSQlh9/hQelm 0Ls4MAwEIXWdF/iyHv4NtTYj4U3E= X-Google-Smtp-Source: AGHT+IGuutrDv8DKeCtaOywaekTsayQyvMpWjry6GDrhJkUopWVujUSkYBIdV/XAVkGV6x/0ERzHpswGVnyFpA1RjHA= X-Received: by 2002:a2e:511a:0:b0:2f7:58a3:68ab with SMTP id 38308e7fff4ca-2f7935c340emr97150841fa.12.1726692067069; Wed, 18 Sep 2024 13:41:07 -0700 (PDT) MIME-Version: 1.0 References: <3dddea5e-52ab-4075-970d-a87b0c921ae7@aklaver.com> <225d1bc1-5117-4c72-85a1-bac6355fb659@aklaver.com> In-Reply-To: From: veem v Date: Thu, 19 Sep 2024 02:10:54 +0530 Message-ID: Subject: Re: IO related waits To: Adrian Klaver Cc: Greg Sabino Mullane , Christophe Pettus , pgsql-general Content-Type: multipart/alternative; boundary="0000000000004b5b3706226ad35d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004b5b3706226ad35d Content-Type: text/plain; charset="UTF-8" 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 >> >> >> 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" > As we are able to get hold of one session, we see "insert into " was blocked by "insert into ". And the "insert into " was experiencing a "client read" wait event. Still unable to understand why it's happening and how to fix it? --0000000000004b5b3706226ad35d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Thu, 19 Sept 2024 at 02:01, veem v= <veema0000@gmail.com> wro= te:

On Wed, 18 Sept 2024 at 05:07, Adrian Klaver <adrian.klaver@ak= laver.com> 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.ht= ml

https://docs.oracle.com/en/database/oracle/develop= er-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=3Dsql-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 t= he "auto commit" we started seeing less number=C2=A0of commits as= per the number of batches.=C2=A0

However=C2=A0we = also started seeing deadlock issues. We have foreign key relationships betw= een 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 differe= nt batches. So why do we see below error, as we ensure=C2=A0in each batch w= e first insert into parent and then into the child tables?

caused by: org.postgresql.util.PSQLException: ERROR: deadlock dete= cted
=C2=A0 Detail: Process 10443 waits for ShareLock on transaction 220= 972157; blocked by process 10454.
Process 10454 waits for ShareLock on t= ransaction 220972155; blocked by process 10443.
=C2=A0 Hint: See server = log for query details.
=C2=A0 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 &= quot;insert into <parent partition table>" was blocked by "= insert into <child partition table>". And the "insert into= =C2=A0 <child partition table> " was experiencing a "client read&q= uot; wait event. Still unable to understand why it's happening and how = to fix it?
--0000000000004b5b3706226ad35d--