Обсуждение: Reserved connections weird issue
Hi all,
I'm having strange problem with one of our PG servers.Strange thing is, that the connections filled all the way up to max_connections and started to block the new connections with:
FATAL: sorry, too many clients already
FATAL: sorry, too many clients already
related non-default settings:
max_connections = 800
superuser_reserved_connections = 10
max_connections = 800
superuser_reserved_connections = 10
It surprised me, that it didn't kept the reserved connections for the superuser, as the application user is not superuser (got the Create DB though). So I couldn't connect to the server to find out, what is going on. I have verified that no superuser connections were on the server running in that time.
In the process list majority of processes was in "authentication" state and they were not shown in the numbackends of the pg_stat_database view (collectd have had connections already established, so metrics were gathered)
Any idea what I got wrong?
Thanks
Petr
Petr Novak <petr.novak23@gmail.com> writes:
> We're running legacy application (written in .NET) on several servers, it
> creates large number of connections to a PG cluster (9.3.10), running on
> CentOS 6. Lately the app team changed the deployment strategy in a way,
> that in peaks it generated almost twice as much connections as before.
> Strange thing is, that the connections filled all the way up to
> max_connections and started to block the new connections with:
> FATAL: sorry, too many clients already
You were probably hitting the point at which the postmaster refused to
handle any more child processes at all, which is something like twice
the max_connections setting. Ordinarily, this is not a problem because
child processes beyond max_connections will die quickly. However, it
sounds like your app team did something to break authentication on the
client side, allowing new postmaster children to sit until they reached
the authentication_timeout, and thereby denying service altogether to
additional incoming connections.
> It surprised me, that it didn't kept the reserved connections for the
> superuser, as the application user is not superuser (got the Create DB
> though). So I couldn't connect to the server to find out, what is going on.
> I have verified that no superuser connections were on the server running in
> that time.
> In the process list majority of processes was in "authentication" state and
> they were not shown in the numbackends of the pg_stat_database view
> (collectd have had connections already established, so metrics were
> gathered)
If an incoming connection hasn't completed authentication yet, then we do
not know if it's for a database superuser, so it can't have any special
priority over other connections.
If you're worried about this case repeating, perhaps it'd help to lower
authentication_timeout to something tighter than the default; although
there's probably not very much you can do against an app that is willing
to flood the server with arbitrarily many connections. Basically what
you got here is a self-DDOS scenario.
regards, tom lane
Hi Aislan,
I wasn't able to connect to the server to query the pg_stat_activity view, and from the output of "ps" command, majority connection lines was suffixed with "authentication", some seems to be doing work, as they had "SELECT/UPDATE/INSERT". I haven't noticed "idle in transaction". I'll create some snapshot of the processes next time, should it happen again..
Petr I wasn't able to connect to the server to query the pg_stat_activity view, and from the output of "ps" command, majority connection lines was suffixed with "authentication", some seems to be doing work, as they had "SELECT/UPDATE/INSERT". I haven't noticed "idle in transaction". I'll create some snapshot of the processes next time, should it happen again..
2016-08-04 19:33 GMT+02:00 Aislan Luiz Wendling <aislanluiz@hotmail.com>:
Hi Petr,Are all connections active or there are "idle in transaction" too?Aislan
From: petr.novak23@gmail.com
Date: Thu, 4 Aug 2016 17:32:31 +0200
Subject: [ADMIN] Reserved connections weird issue
To: pgsql-admin@postgresql.orgWe're running legacy application (written in .NET) on several servers, it creates large number of connections to a PG cluster (9.3.10), running on CentOS 6. Lately the app team changed the deployment strategy in a way, that in peaks it generated almost twice as much connections as before.Hi all,I'm having strange problem with one of our PG servers.Strange thing is, that the connections filled all the way up to max_connections and started to block the new connections with:
FATAL: sorry, too many clients alreadyrelated non-default settings:
max_connections = 800
superuser_reserved_connections = 10It surprised me, that it didn't kept the reserved connections for the superuser, as the application user is not superuser (got the Create DB though). So I couldn't connect to the server to find out, what is going on. I have verified that no superuser connections were on the server running in that time.In the process list majority of processes was in "authentication" state and they were not shown in the numbackends of the pg_stat_database view (collectd have had connections already established, so metrics were gathered)Any idea what I got wrong?ThanksPetr
Hi John,
that's the thing, I wasn't able to get in as postgres user, got bounced as there were no connections available. Will try to get more info next time...2016-08-04 19:34 GMT+02:00 John Scalia <jayknowsunix@gmail.com>:
Sounds like, maybe, your .Net connections are being duplicated without waiting for the old connection to be torn down. When this kind of thing happens, you should still be able to get in as a superuser. Once in, have you checked pg_stat_activity? Between that and process list you ought to see if you've got old, dead connections taking up valuable connection space.Jay
--On Thu, Aug 4, 2016 at 10:32 AM, Petr Novak <petr.novak23@gmail.com> wrote:We're running legacy application (written in .NET) on several servers, it creates large number of connections to a PG cluster (9.3.10), running on CentOS 6. Lately the app team changed the deployment strategy in a way, that in peaks it generated almost twice as much connections as before.Hi all,I'm having strange problem with one of our PG servers.Strange thing is, that the connections filled all the way up to max_connections and started to block the new connections with:
FATAL: sorry, too many clients alreadyrelated non-default settings:
max_connections = 800
superuser_reserved_connections = 10It surprised me, that it didn't kept the reserved connections for the superuser, as the application user is not superuser (got the Create DB though). So I couldn't connect to the server to find out, what is going on. I have verified that no superuser connections were on the server running in that time.In the process list majority of processes was in "authentication" state and they were not shown in the numbackends of the pg_stat_database view (collectd have had connections already established, so metrics were gathered)Any idea what I got wrong?ThanksPetr