Feedback-ID: i76984098:Fastmail
Message-ID: <531a5550-dee1-4659-a2b8-45ad52fe7e11@aklaver.com>
Date: Tue, 1 Oct 2024 07:07:25 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Question on session timeout
To: sud <suds1434@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAD=mzVX5DYUZxt7VE-eB4=6eXTpbG7r6MiR7gD-0K9cctscnYw@mail.gmail.com>
 <3133182a-35c4-4a81-be60-8ab227c0fbc0@aklaver.com>
 <CAD=mzVUns_nBPOOiDMxvZgnra+e9Nz7QJqr3r3X6JFMxuUGRZg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAD=mzVUns_nBPOOiDMxvZgnra+e9Nz7QJqr3r3X6JFMxuUGRZg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/531a5550-dee1-4659-a2b8-45ad52fe7e11%40aklaver.com>
Precedence: bulk

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