From: Justin Pryzby <pryzby@telsasoft.com>
To: Sudhir Guna <sudhir.guna.sg@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: DB connection issue suggestions
Date: Tue, 10 May 2022 13:15:40 -0500
Message-ID: <20220510181540.GC19626@telsasoft.com> (raw)
In-Reply-To: <CAGedWvP=Mho1ayhmpXVQbaS3xxqu9ZgQOhKe-rF4kyxo=gYN0Q@mail.gmail.com>
References: <CAGedWvP=Mho1ayhmpXVQbaS3xxqu9ZgQOhKe-rF4kyxo=gYN0Q@mail.gmail.com>
On Wed, May 11, 2022 at 12:59:01AM +0800, Sudhir Guna wrote:
> Dear All,
>
> We have recently upgraded Postgresql 9.4 standalone server to Postgresql
> 11.2 with High Availability (2 servers : Master and Standby).
>
> While trying to test using ETL applications and reports, we observe that
> the ETL jobs fails with below error,
>
> 2022/05/06 16:27:36 - Error occurred while trying to connect to the database
> 2022/05/06 16:27:36 - Error connecting to database: (using class
> org.postgresql.Driver)
> 2022/05/06 16:27:36 - FATAL: Sorry, too many clients already
>
> We have increased the max_connections = 1000 in postgresql.conf file.
>
> It worked ok for a day and later we get the same error message.
>
> Please help to advise on any additional settings required. The prior
> Postgresql 9.4 had the default max_connections = 100 and the applications
> worked fine.
It sounds like at least one thing is still running, perhaps running very
slowly.
You should monitor the number of connections to figure out what.
If you expect to be able to run with only 100 connections, then when
connections>200, there's already over 100 connections which shouldn't still be
there.
You could query pg_stat_activity to determine what they're doing - trying to
run a slow query ? Are all/most of them stuck doing the same thing ?
You should try to provide the information here for the slow query, and for the
rest of your environment.
https://wiki.postgresql.org/wiki/Slow_Query_Questions
--
Justin
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: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, sudhir.guna.sg@gmail.com
Subject: Re: DB connection issue suggestions
In-Reply-To: <20220510181540.GC19626@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox