MIME-Version: 1.0
References: <CAJ+5Ff6WtFCzamrRZqN3u3htvkGcmob7VaYWYBd+sRx6jKpHuA@mail.gmail.com>
In-Reply-To: <CAJ+5Ff6WtFCzamrRZqN3u3htvkGcmob7VaYWYBd+sRx6jKpHuA@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 8 Aug 2024 06:22:17 -0400
Message-ID: <CANzqJaCy5n5E016FCBK+RrfHx=h0Wk+E23VGLwAZAJZ-cMzKSA@mail.gmail.com>
Subject: Re: Vacuum full connection exhaustion
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005bfa73061f296782"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCy5n5E016FCBK%2BRrfHx%3Dh0Wk%2BE23VGLwAZAJZ-cMzKSA%40mail.gmail.com>
Precedence: bulk

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

On Thu, Aug 8, 2024 at 5:18=E2=80=AFAM Costa Alexoglou <costa@dbtune.com> w=
rote:

> 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`
>

Off-topic, but... *WHY??  *It almost certainly does not do what you think
it does. Especially if it's just "VACUUM FULL;"

the connections are exhausted.
>

Connect to the relevant database and run this query.  Don't disconnect, and
keep running it over and over again as you run the "VACUUM FULL;".  That'll
tell you exactly what happens.
select pid
   ,datname as db
   ,application_name as app_name
   ,case
        when client_hostname is not null then client_hostname
        else client_addr::text
    end AS client_name
   ,usename
   ,to_char((EXTRACT(epoch FROM now() - backend_start))/60.0, '99,999.00')
as backend_min
   ,to_char(query_start, 'YYYY-MM-DD HH24:MI:SS.MS') as "Query Start"
   ,to_char((EXTRACT(epoch FROM now() - query_start))/60.0, '99,999.00') as
qry_min
   ,to_char(xact_start, 'YYYY-MM-DD HH24:MI:SS.MS') as "Txn Start"
   ,to_char((EXTRACT(epoch FROM now() - xact_start)/60.0), '999.00') as
txn_min
   ,state
   query
from pg_stat_activity
WHERE pid !=3D pg_backend_pid()
order by 6 desc;


>
> Also tried this with 150 `max_connections` to see if it just =E2=80=9Cdou=
bles=E2=80=9D the
> current connections, but as it turned out, it still exhausted all the
> connections until it reached `max_connections`.
>

Double it again?


>
> This was cross-checked, as the postgres-exporter could not connect, and I
> manually was not allowed to connect with `psql`.
>
> Is this expected or is this a bug?
>

Depends on what you set these to:
autovacuum_max_workers
max_parallel_maintenance_workers
max_parallel_workers
max_parallel_workers_per_gather
max_worker_processes

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

--0000000000005bfa73061f296782
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 5:18=E2=80=AFAM Co=
sta Alexoglou &lt;<a href=3D"mailto:costa@dbtune.com">costa@dbtune.com</a>&=
gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex"><div dir=3D"ltr">Hey folks,<br><br>I noticed somethin=
g weird, and not sure if this is the expected behaviour or not in PostgreSQ=
L.<br><br>So I am running Benchbase (a benchmark framework) with 50 termina=
ls (50 concurrent connections).<div>There are 2-3 additional connections, o=
ne for a postgres-exporter container for example.<br><br>So far so good, an=
d with a `max_connections` at 100 there is no problem. What happens is that=
 if I execute manually `VACUUM FULL` </div></div></blockquote><div><br></di=
v><div>Off-topic, but... <b>WHY??=C2=A0=C2=A0</b>It almost certainly does n=
ot do what you think it does. Especially if it&#39;s just &quot;VACUUM FULL=
;&quot;</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x"><div dir=3D"ltr"><div>the connections are exhausted.<br></div></div></bl=
ockquote><div><br></div><div>Connect to the relevant database and run this =
query.=C2=A0 Don&#39;t disconnect, and keep running it over and=C2=A0over a=
gain as you run the &quot;VACUUM FULL;&quot;.=C2=A0 That&#39;ll tell you ex=
actly what happens.</div><div><font face=3D"monospace">select pid</font></d=
iv><div><font face=3D"monospace">=C2=A0 =C2=A0,datname as db<br>=C2=A0 =C2=
=A0,application_name as app_name<br>=C2=A0 =C2=A0,case <br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 when client_hostname is not null then client_hostname<br>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 else client_addr::text <br>=C2=A0 =C2=A0 end AS cl=
ient_name<br>=C2=A0 =C2=A0,usename <br>=C2=A0 =C2=A0,to_char((EXTRACT(epoch=
 FROM now() - backend_start))/60.0, &#39;99,999.00&#39;) as backend_min<br>=
=C2=A0 =C2=A0,to_char(query_start, &#39;YYYY-MM-DD HH24:MI:<a href=3D"http:=
//SS.MS">SS.MS</a>&#39;) as &quot;Query Start&quot;<br>=C2=A0 =C2=A0,to_cha=
r((EXTRACT(epoch FROM now() - query_start))/60.0, &#39;99,999.00&#39;) as q=
ry_min<br>=C2=A0 =C2=A0,to_char(xact_start, &#39;YYYY-MM-DD HH24:MI:<a href=
=3D"http://SS.MS">SS.MS</a>&#39;) as &quot;Txn Start&quot;<br>=C2=A0 =C2=A0=
,to_char((EXTRACT(epoch FROM now() - xact_start)/60.0), &#39;999.00&#39;) a=
s txn_min<br>=C2=A0 =C2=A0,state<br>=C2=A0 =C2=A0query<br>from pg_stat_acti=
vity <br>WHERE pid !=3D pg_backend_pid()<br>order by 6 desc;<br></font></di=
v><div>=C2=A0<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><d=
iv dir=3D"ltr"><div><br>Also tried this with 150 `max_connections` to see i=
f it just =E2=80=9Cdoubles=E2=80=9D the current connections, but as it turn=
ed out, it still exhausted all the connections until it reached `max_connec=
tions`.<br></div></div></blockquote><div><br></div><div>Double it again?</d=
iv><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
dir=3D"ltr"><div><br>This was cross-checked, as the postgres-exporter could=
 not connect, and I manually was not allowed to connect with `psql`.<br><br=
>Is this expected or is this a bug?<br></div></div></blockquote><div>=C2=A0=
</div><div>Depends on what you set these to:</div><div><font face=3D"monosp=
ace">autovacuum_max_workers<br>max_parallel_maintenance_workers<br>max_para=
llel_workers<br>max_parallel_workers_per_gather<br>max_worker_processes</fo=
nt><br></div></div><div><br></div><span class=3D"gmail_signature_prefix">--=
 </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Dea=
th to America, and butter sauce!<div>Iraq lobster...</div></div></div></div=
>

--0000000000005bfa73061f296782--