public inbox for [email protected]  

help / color / mirror / Atom feed

Question on session timeout
2+ messages / 2 participants
[nested] [flat]

* Question on session timeout
@ 2024-09-30 20:01 sud <[email protected]>
  2024-09-30 22:40 ` Re: Question on session timeout Adrian Klaver <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: sud @ 2024-09-30 20:01 UTC (permalink / raw)
  To: pgsql-general <[email protected]>

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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Question on session timeout
  2024-09-30 20:01 Question on session timeout sud <[email protected]>
@ 2024-09-30 22:40 ` Adrian Klaver <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Adrian Klaver @ 2024-09-30 22:40 UTC (permalink / raw)
  To: sud <[email protected]>; pgsql-general <[email protected]>

On 9/30/24 13:01, sud wrote:
> Hello,
> We are frequently seeing the total DB connection reaching ~2000+ whereas 

Where are you getting the ~2000  count from?

> 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 

What do you mean by ~120 sets, in particular what is a set?

> "max idle" and "max active" connection count and "maxage" as 7 days, so 
> does this suggest any issue at connection pool setup?

Using what pooler?

> 
> We do see keep alive queries in the DB (select 1), not sure if that is 
> making this scenario. When checking the 

How often do to keep alive queries run?

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

'"idle_in_transaction_session_timeout" it is set as 24hours' is a foot 
gun as explained here:

https://www.postgresql.org/docs/current/runtime-config-client.html

idle_in_transaction_session_timeout (integer)

[...]

"This option can be used to ensure that idle sessions do not hold locks 
for an unreasonable amount of time. Even when no significant locks are 
held, an open transaction prevents vacuuming away recently-dead tuples 
that may be visible only to this transaction; so remaining idle for a 
long time can contribute to table bloat. See Section 24.1 for more details."


With '"idle_session_timeout" set as "0"' a session without an open 
transaction is not going to timeout.

> would be the advisable value for these parameters?
> 
> Regards
> Sud

-- 
Adrian Klaver
[email protected]







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-09-30 22:40 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-30 20:01 Question on session timeout sud <[email protected]>
2024-09-30 22:40 ` Adrian Klaver <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox