MIME-Version: 1.0
References: <b7dcfa5c-846b-408c-9da3-201638aee2e1@blackbrook.org>
In-Reply-To: <b7dcfa5c-846b-408c-9da3-201638aee2e1@blackbrook.org>
From: Nikolay Samokhvalov <nik@postgres.ai>
Date: Mon, 9 Dec 2024 15:10:32 -0800
Message-ID: 
 <CAM527d_JP20hZ9bLKWUmMXx_bQms4B=W4321HApfcQ98cx_1HA@mail.gmail.com>
Subject: Re: can a blocked transaction affect the performance of one that is
 blocking it?
To: Eric Schwarzenbach <subscriber@blackbrook.org>
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000004aba2f0628de7985"
Archived-At: 
 <https://www.postgresql.org/message-id/CAM527d_JP20hZ9bLKWUmMXx_bQms4B%3DW4321HApfcQ98cx_1HA%40mail.gmail.com>
Precedence: bulk

--0000000000004aba2f0628de7985
Content-Type: text/plain; charset="UTF-8"

On Mon, Dec 9, 2024 at 13:16 Eric Schwarzenbach <subscriber@blackbrook.org>
wrote:

> Hi,
>
> Could one transaction (one that should be relatively simple and short)
> cause another complex, long running transaction (involving INSERTS, on a
> table the first transaction may be reading from) to take many orders of
> magnitude longer than it would normally? (short of competing for system
> resources, like CPU time etc, of course)
>
> I don't believe my scenario involved a deadlock but I expect my short
> transaction was probably blocked by my long one. Does it make any sense
> that this could very significantly affect the performance of the
> non-blocked transaction?
>
> Thanks,
>
> Eric


Have you tried wait event analysis (looking at wait_event_type, wait_event,
state, query samples from pg_stat_activity)?

>

--0000000000004aba2f0628de7985
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div>On Mon, Dec 9, 2024 at 13:16 Eric Schwarzenbach &lt;<a href=3D"mailto:=
subscriber@blackbrook.org">subscriber@blackbrook.org</a>&gt; wrote:<br></di=
v><div dir=3D"auto"><div class=3D"gmail_quote"><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-s=
tyle:solid;padding-left:1ex;border-left-color:rgb(204,204,204)" dir=3D"auto=
">Hi,<br>
<br>
Could one transaction (one that should be relatively simple and short) <br>
cause another complex, long running transaction (involving INSERTS, on a <b=
r>
table the first transaction may be reading from) to take many orders of <br=
>
magnitude longer than it would normally? (short of competing for system <br=
>
resources, like CPU time etc, of course)<br>
<br>
I don&#39;t believe my scenario involved a deadlock but I expect my short <=
br>
transaction was probably blocked by my long one. Does it make any sense <br=
>
that this could very significantly affect the performance of the <br>
non-blocked transaction?<br>
<br>
Thanks,<br>
<br>
Eric</blockquote><div dir=3D"auto"><br></div><div dir=3D"auto">Have you tri=
ed wait event analysis (looking at wait_event_type, wait_event, state, quer=
y samples from pg_stat_activity)?</div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:sol=
id;padding-left:1ex;border-left-color:rgb(204,204,204)" dir=3D"auto"></bloc=
kquote></div></div>

--0000000000004aba2f0628de7985--