Re: Question on session timeout

[sud <suds1434@xxxxxxxxx>]

On Tue, Oct 1, 2024 at 5:45 PM Greg Sabino Mullane <htamfids@xxxxxxxxx> wrote:

On Tue, Oct 1, 2024 at 1:57 AM sud <suds1434@xxxxxxxxx> 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.