On 9/30/24 22:57, sud wrote:
On Tue, Oct 1, 2024 at 4:10 AM Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx
