MIME-Version: 1.0
References: <CAJ+5Ff6WtFCzamrRZqN3u3htvkGcmob7VaYWYBd+sRx6jKpHuA@mail.gmail.com>
 <66D17234-043D-457E-8607-5126B4FBC3DD@thebuild.com>
In-Reply-To: <66D17234-043D-457E-8607-5126B4FBC3DD@thebuild.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 9 Aug 2024 00:15:33 -0400
Message-ID: <CANzqJaDZDLnYhPEds8jMf4ArEG1MOVtiPr7g0GvPMiEpjrNqiQ@mail.gmail.com>
Subject: Re: Vacuum full connection exhaustion
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a99ed5061f386565"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDZDLnYhPEds8jMf4ArEG1MOVtiPr7g0GvPMiEpjrNqiQ%40mail.gmail.com>
Precedence: bulk

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

On Thu, Aug 8, 2024 at 10:12=E2=80=AFAM Christophe Pettus <xof@thebuild.com=
> wrote:

>
>
> > On Aug 7, 2024, at 10:34, Costa Alexoglou <costa@dbtune.com> wrote:
> >
> > Hey folks,
> >
> > I noticed something weird, and not sure if this is the expected
> behaviour or not in PostgreSQL.
> >
> > So I am running Benchbase (a benchmark framework) with 50 terminals (50
> concurrent connections).
> > There are 2-3 additional connections, one for a postgres-exporter
> container for example.
> >
> > So far so good, and with a `max_connections` at 100 there is no problem=
.
> What happens is that if I execute manually `VACUUM FULL` the connections
> are exhausted.
>
> 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 lock
> 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 see a lock, so let's cause another one!"  That's crazy.

--=20
Death to America, and butter sauce.
Iraq lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Aug 8, 2024 at 10:12=E2=80=AFAM C=
hristophe Pettus &lt;<a href=3D"mailto:xof@thebuild.com">xof@thebuild.com</=
a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"><br>
<br>
&gt; On Aug 7, 2024, at 10:34, Costa Alexoglou &lt;<a href=3D"mailto:costa@=
dbtune.com" target=3D"_blank">costa@dbtune.com</a>&gt; wrote:<br>
&gt; <br>
&gt; Hey folks,<br>
&gt; <br>
&gt; I noticed something weird, and not sure if this is the expected behavi=
our or not in PostgreSQL.<br>
&gt; <br>
&gt; So I am running Benchbase (a benchmark framework) with 50 terminals (5=
0 concurrent connections).<br>
&gt; There are 2-3 additional connections, one for a postgres-exporter cont=
ainer for example.<br>
&gt; <br>
&gt; So far so good, and with a `max_connections` at 100 there is no proble=
m. What happens is that if I execute manually `VACUUM FULL` the connections=
 are exhausted.<br>
<br>
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 exclusiv=
e lock waiting for the VACUUM FULL to finish, the application sees the conn=
ection stopped and fires up another one (this is common in container-based =
applications), that one blocks... until all of the connections are full of =
queries waiting on that VACUUM FULL.<br>
<br>
</blockquote></div><div><br></div><div>&quot;I see a lock, so let&#39;s cau=
se another one!&quot;=C2=A0 That&#39;s crazy.</div><div><br></div><span cla=
ss=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail=
_signature"><div dir=3D"ltr">Death to America, and butter sauce.<div>Iraq l=
obster!</div></div></div></div>

--000000000000a99ed5061f386565--