MIME-Version: 1.0
From: sud <suds1434@gmail.com>
Date: Tue, 1 Oct 2024 01:31:57 +0530
Message-ID: <CAD=mzVX5DYUZxt7VE-eB4=6eXTpbG7r6MiR7gD-0K9cctscnYw@mail.gmail.com>
Subject: Question on session timeout
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000006314706235baefe"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVX5DYUZxt7VE-eB4%3D6eXTpbG7r6MiR7gD-0K9cctscnYw%40mail.gmail.com>
Precedence: bulk

--00000000000006314706235baefe
Content-Type: text/plain; charset="UTF-8"

Hello,
We are frequently seeing the total DB connection reaching ~2000+ whereas
the total number of active sessions in pg_stat_activity staying <100 at any
point in time. And when we see the sessions from DB side they are showing
most of the sessions with state as 'idle' having backend_start/xact_start
showing date ~10days older. We do use application level connection pooling,
and we have ~120 sets as both the "max idle" and "max active" connection
count and "maxage" as 7 days, so does this suggest any issue at connection
pool setup?

We do see keep alive queries in the DB (select 1), not sure if that is
making this scenario. When checking the
"idle_in_transaction_session_timeout" it is set as 24hours and
"idle_session_timeout" set as "0". So my question is , should we set the
parameter to a lesser value in DB cluster level like ~5minutes or so, so as
not to keep the idle sessions lying so long in the database and what would
be the advisable value for these parameters?

Regards
Sud

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

<div dir=3D"ltr">Hello,<div>We are frequently seeing the total DB connectio=
n reaching ~2000+ whereas the total number of active sessions in pg_stat_ac=
tivity staying &lt;100 at any point in time. And when we see the sessions f=
rom DB side they are showing most of the sessions with state as &#39;idle&#=
39; having backend_start/xact_start showing date ~10days older. We do use a=
pplication level connection pooling, and we have ~120 sets as both the &quo=
t;max idle&quot; and &quot;max active&quot; connection count and &quot;maxa=
ge&quot; as 7 days, so does this suggest any issue at connection pool setup=
?</div><div><br></div><div>We do see keep alive queries in the DB (select 1=
), not sure if that is making this scenario. When checking the &quot;idle_i=
n_transaction_session_timeout&quot; it is set as 24hours and &quot;idle_ses=
sion_timeout&quot; set as &quot;0&quot;. So my question is , should we set =
the parameter to a lesser value in DB cluster level like ~5minutes or so, s=
o as not to keep the idle sessions lying so long in the database and what w=
ould be the advisable value for these parameters?</div><div><br></div><div>=
Regards</div><div>Sud</div></div>

--00000000000006314706235baefe--