MIME-Version: 1.0
References: <CAJ+5Ff6WtFCzamrRZqN3u3htvkGcmob7VaYWYBd+sRx6jKpHuA@mail.gmail.com>
 <66D17234-043D-457E-8607-5126B4FBC3DD@thebuild.com> <CAApHDvrLiwiB8o+3R2M4JbwyovhD7Btt0h=+8_d85Lt+oxoy7Q@mail.gmail.com>
In-Reply-To: <CAApHDvrLiwiB8o+3R2M4JbwyovhD7Btt0h=+8_d85Lt+oxoy7Q@mail.gmail.com>
From: Costa Alexoglou <costa@dbtune.com>
Date: Fri, 9 Aug 2024 11:47:26 +0200
Message-ID: <CAJ+5Ff4n+3SpSeDGd1UwF7JyvBkgHi7uyz0i+JtisD1m0U-PbQ@mail.gmail.com>
Subject: Re: Vacuum full connection exhaustion
To: David Rowley <dgrowleyml@gmail.com>
Cc: Christophe Pettus <xof@thebuild.com>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008cd40d061f3d0863"
Archived-At: <https://www.postgresql.org/message-id/CAJ%2B5Ff4n%2B3SpSeDGd1UwF7JyvBkgHi7uyz0i%2BJtisD1m0U-PbQ%40mail.gmail.com>
Precedence: bulk

--0000000000008cd40d061f3d0863
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, Aug 9, 2024 at 1:02=E2=80=AFAM David Rowley <dgrowleyml@gmail.com> =
wrote:

> On Fri, 9 Aug 2024 at 02:12, Christophe Pettus <xof@thebuild.com> wrote:
> > VACUUM FULL takes an exclusive lock on the table that it is operating
> on.  It's possible that a connection becomes blocked on that exclusive lo=
ck
> waiting for the VACUUM FULL to finish, the application sees the connectio=
n
> stopped and fires up another one (this is common in container-based
> applications), that one blocks... until all of the connections are full o=
f
> queries waiting on that VACUUM FULL.
>
> I also imagine this is the cause. One way to test would be to do:
> BEGIN; LOCK TABLE <name of table>; and see if the connections pile up
> in a similar way to when the VACUUM FULL command is used.
>
> David
>

Thanks folks. David really straight-forward way to test. I validated this,
when I lock the two tables involved in the benchmark the connections are
constantly growing until they reach the `max_connections`

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Aug 9, 2024 at 1:02=E2=80=AFAM Da=
vid Rowley &lt;<a href=3D"mailto:dgrowleyml@gmail.com">dgrowleyml@gmail.com=
</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex">On Fri, 9 Aug 2024 at 02:12, Christophe Pettus &=
lt;<a href=3D"mailto:xof@thebuild.com" target=3D"_blank">xof@thebuild.com</=
a>&gt; wrote:<br>
&gt; VACUUM FULL takes an exclusive lock on the table that it is operating =
on.=C2=A0 It&#39;s possible that a connection becomes blocked on that exclu=
sive lock waiting for the VACUUM FULL to finish, the application sees the c=
onnection stopped and fires up another one (this is common in container-bas=
ed applications), that one blocks... until all of the connections are full =
of queries waiting on that VACUUM FULL.<br>
<br>
I also imagine this is the cause. One way to test would be to do:<br>
BEGIN; LOCK TABLE &lt;name of table&gt;; and see if the connections pile up=
<br>
in a similar way to when the VACUUM FULL command is used.<br>
<br>
David<br></blockquote><div><br></div><div>Thanks folks. David really straig=
ht-forward way to test. I validated this, when I lock the two tables involv=
ed in the benchmark the connections are constantly growing until they reach=
 the `max_connections`=C2=A0<br></div></div></div>

--0000000000008cd40d061f3d0863--