Re: Question on session timeout

On 9/30/24 22:57, sud wrote:
>
>
> On Tue, Oct 1, 2024 at 4:10 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:

> /Where are you getting the ~2000  count from?/
> Seeing this in the "performance insights" dashboard and also its
> matching when I query the count of sessions from pg_stat_activity.

From your OP:

"... whereas the total number of active sessions in pg_stat_activity
staying <100 at any point in time."

So:

1) They don't match.

2) ""performance insights" dashboard" does not actually tell us
anything. What program and what is it actually measuring?

>
> /What do you mean by ~120 sets, in particular what is a set?
> /These are the values set as mentioned in the properties file which the
> application team uses for connection pooling

Again this does not tell us anything.

1) A set of what?

2) What properties file?

>
> /Using what pooler?
> /I need to check on this as Its Java application(jdbc driver for
> connecting to DB), so I thought it must be using standard connection
> pooling. Will double check.

Since pooling is what you are concerned with this is should be the
starting point of your investigation.

>
> /How often do to keep alive queries run?
> /Need to check. But I am not sure, in general , if these "keep alive"
> queries are used for keeping a transaction alive or a session alive?

With an idle_in_transaction_session_timeout of 24 hrs I don't see that
it makes a difference.

>
> As you described, a long time open transaction with a session state as
> "idle" will be threatening as that will cause locking and "transaction

That would be idle_in_transaction.

> ID wrap around" issues to surface whereas having "idle sessions" of a
> closed transaction may not cause any issue as they will do no harm. Does
> it mean we can have any number of idle sessions or we should also have
> some non zero "timeout" setup for the "ide_session_timeout" parameter
> too (maybe ~1hr or so)?

Other then it takes up connections.

>
> Is it correct to assume the session in pg_stat_activity with very old
> XACT_START are the one which are part of long running open
> transaction(i.e. driven by idle_in_transaction_session_timeout) whereas
> the ones with older BACKEND_START or QUERY_START are the one are just
> the idle session(driven by idle_session_timeout) but not tied to any
> open transaction?

I would read this descriptions here:

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

>
> Few observations:-
>
> I do see, "MaximumUsedTransactionIDs" staying consistently ~200M for a
> long time then coming down. And its matching to
> "autovacuum_freeze_max_age" which is set as 200M. Hope it's expected. We
> have max_connections set as 5000.
>
> "Database connection" touching ~2000 then coming down till 200. And we
> see uneven spikes in those, it seems to be matching with the pattern ,
> when we have some errors occurring during the insert queries which are
> submitted by the Java application to insert the data into the tables.
>

This would have been a good thing to lead with.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com