On Tue, Oct 1, 2024 at 5:45 PM Greg Sabino Mullane <htamf...@gmail.com> wrote:
> On Tue, Oct 1, 2024 at 1:57 AM sud <suds1...@gmail.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.
