Re: Question on session timeout

On Tue, Oct 1, 2024 at 5:45 PM Greg Sabino Mullane <htamfids(at)gmail(dot)com>
wrote:

> On Tue, Oct 1, 2024 at 1:57 AM sud <suds1434(at)gmail(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.
>>
>
> So I'm guessing this is perhaps RDS or Aurora? Stating that up front can
> be helpful.
>
>
>> As you described, a long time open transaction with a session state as
>> "idle" will be threatening as that will cause locking
>>
>
> No, idle is fine, "idle in transaction" is bad. :)
>
> 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
>>
>
> <snip rest of question>
>
> You need to look at the "state" column as your primary bit of information.
> Second most important is how long something has been in that state, which
> you can find with now() - state_change. The best way to learn all of this
> is to open a few concurrent sessions in psql and experiment.
>
>
>> We have max_connections set as 5000.
>>
>
> That's quite high. But if you never reach that high, it doesn't matter a
> whole lot.
>
> "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.
>>
>
> (What sort of errors?) 2000 is high. Clearly, you are not pooling
> connections, or not pooling them well. If you are using plain Postgres,
> look into setting up pgbouncer. If using something managed (e.g. RDS) look
> into their particular pooling solution. Or fix your application-level
> pooling.
>
>
Thanks Greg.
It's a third party app and the application team confirmed they are using
connection pooling at their side. But as you mentioned, the number of
connections *"2000 is high"* . But , isn't it possible because they may be
having a max connection pool size limit set as ~2000 which is why we see
that many connections during peak window. So in that case is it advisable
to reduce the number of Max connections, because we have a number of cores
-32 for this instance.

And yes it's RDS. The errors which we were seeing were related to the data
bit not related to connections.