pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedReserved connections weird issue
4+ messages / 2 participants
[nested] [flat]
* Reserved connections weird issue
@ 2016-08-04 15:32 Petr Novak <petr.novak23@gmail.com>
2016-08-04 18:02 ` Re: Reserved connections weird issue Tom Lane <tgl@sss.pgh.pa.us>
2016-08-04 19:22 ` Re: Reserved connections weird issue Petr Novak <petr.novak23@gmail.com>
2016-08-04 19:24 ` Re: Reserved connections weird issue Petr Novak <petr.novak23@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Petr Novak @ 2016-08-04 15:32 UTC (permalink / raw)
To: pgsql-admin
Hi all,
I'm having strange problem with one of our PG servers.
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
related non-default settings:
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Reserved connections weird issue
2016-08-04 15:32 Reserved connections weird issue Petr Novak <petr.novak23@gmail.com>
@ 2016-08-04 18:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2016-08-04 18:02 UTC (permalink / raw)
To: Petr Novak <petr.novak23@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Reserved connections weird issue
2016-08-04 15:32 Reserved connections weird issue Petr Novak <petr.novak23@gmail.com>
@ 2016-08-04 19:22 ` Petr Novak <petr.novak23@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Petr Novak @ 2016-08-04 19:22 UTC (permalink / raw)
To: Aislan Luiz Wendling <aislanluiz@hotmail.com>; +Cc: pgsql-admin
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
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.org
>
>
> Hi all,
>
> I'm having strange problem with one of our PG servers.
>
> 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
>
> related non-default settings:
>
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Reserved connections weird issue
2016-08-04 15:32 Reserved connections weird issue Petr Novak <petr.novak23@gmail.com>
@ 2016-08-04 19:24 ` Petr Novak <petr.novak23@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Petr Novak @ 2016-08-04 19:24 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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...
P
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:
>
>> Hi all,
>>
>> I'm having strange problem with one of our PG servers.
>>
>> 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
>>
>> related non-default settings:
>>
>> 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
>>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-08-04 19:24 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-08-04 15:32 Reserved connections weird issue Petr Novak <petr.novak23@gmail.com>
2016-08-04 18:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-08-04 19:22 ` Petr Novak <petr.novak23@gmail.com>
2016-08-04 19:24 ` Petr Novak <petr.novak23@gmail.com>
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