public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: sud <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Question on session timeout
Date: Tue, 1 Oct 2024 07:07:25 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAD=mzVUns_nBPOOiDMxvZgnra+e9Nz7QJqr3r3X6JFMxuUGRZg@mail.gmail.com>
References: <CAD=mzVX5DYUZxt7VE-eB4=6eXTpbG7r6MiR7gD-0K9cctscnYw@mail.gmail.com>
<[email protected]>
<CAD=mzVUns_nBPOOiDMxvZgnra+e9Nz7QJqr3r3X6JFMxuUGRZg@mail.gmail.com>
On 9/30/24 22:57, sud wrote:
>
>
> On Tue, Oct 1, 2024 at 4:10 AM Adrian Klaver <[email protected]
> <mailto:[email protected]>> 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
[email protected]
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Question on session timeout
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox