pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedsuperuser_reserved_connections and max_connections
4+ messages / 4 participants
[nested] [flat]
* superuser_reserved_connections and max_connections
@ 2016-06-24 16:10 Travis Kirstine <tkirstine@firstbasesolutions.com>
2016-06-24 16:26 ` Re: superuser_reserved_connections and max_connections David G. Johnston <david.g.johnston@gmail.com>
2016-06-24 16:27 ` Re: superuser_reserved_connections and max_connections John Scalia <jayknowsunix@gmail.com>
2016-06-24 16:40 ` Re: superuser_reserved_connections and max_connections Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 3 replies; 4+ messages in thread
From: Travis Kirstine @ 2016-06-24 16:10 UTC (permalink / raw)
To: pgsql-admin
We were getting a lot of "max connections reached" errors and noticed that all our users are super users, after some digging I found the
superuser_reserved_connections parameter was not set (default to 3) this lead to some additional questions:
If all my users are superusers are they effectively limited to the number of connections defined in the
superuser_reserved_connections parameter?
Is there any significant difference between a superuser and non-superuser connection?
Any additional insight would be great.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: superuser_reserved_connections and max_connections
2016-06-24 16:10 superuser_reserved_connections and max_connections Travis Kirstine <tkirstine@firstbasesolutions.com>
@ 2016-06-24 16:26 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2016-06-24 16:26 UTC (permalink / raw)
To: Travis Kirstine <tkirstine@firstbasesolutions.com>; +Cc: pgsql-admin
On Friday, June 24, 2016, Travis Kirstine <tkirstine@firstbasesolutions.com>
wrote:
>
> We were getting a lot of "max connections reached" errors and noticed that
> all our users are super users, after some digging I found the
>
> superuser_reserved_connections parameter was not set (default to 3) this
> lead to some additional questions:
>
>
> If all my users are superusers are they effectively limited to the number
> of connections defined in the
>
> superuser_reserved_connections parameter?
>
>
> Is there any significant difference between a superuser and non-superuser
> connection?
>
>
> Any additional insight would be great.
>
If max connections is 100 you get 97 concurrent connections. If all 97 are
in use you then get three more concurrent connections that only
accept superuser credentials.
https://www.postgresql.org/docs/current/static/runtime-config-connection.html
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: superuser_reserved_connections and max_connections
2016-06-24 16:10 superuser_reserved_connections and max_connections Travis Kirstine <tkirstine@firstbasesolutions.com>
@ 2016-06-24 16:27 ` John Scalia <jayknowsunix@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: John Scalia @ 2016-06-24 16:27 UTC (permalink / raw)
To: Travis Kirstine <tkirstine@firstbasesolutions.com>; +Cc: pgsql-admin
Ummm.... YES, there are great (potential) differences between non-Superuser
and Superuser connections and their abilities. Apparently when your users
were created, someone added superuser abilities to each one. That is almost
certainly an error. You may have users which should have read-only ability
others who should have read-write, and far fewer with superuser. Check the
web pages for GRANTS as to what each user can have. If you have lots of
users and don't want to assign each individually, then make a group role,
give it the abilities you want, then assign your appropriate users to that
group. Sounds like you need to read up on database security.
--
Jay
On Fri, Jun 24, 2016 at 11:10 AM, Travis Kirstine <
tkirstine@firstbasesolutions.com> wrote:
>
> We were getting a lot of "max connections reached" errors and noticed that
> all our users are super users, after some digging I found the
>
> superuser_reserved_connections parameter was not set (default to 3) this
> lead to some additional questions:
>
>
> If all my users are superusers are they effectively limited to the number
> of connections defined in the
>
> superuser_reserved_connections parameter?
>
>
> Is there any significant difference between a superuser and non-superuser
> connection?
>
>
> Any additional insight would be great.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: superuser_reserved_connections and max_connections
2016-06-24 16:10 superuser_reserved_connections and max_connections Travis Kirstine <tkirstine@firstbasesolutions.com>
@ 2016-06-24 16:40 ` Steve Crawford <scrawford@pinpointresearch.com>
2 siblings, 0 replies; 4+ messages in thread
From: Steve Crawford @ 2016-06-24 16:40 UTC (permalink / raw)
To: Travis Kirstine <tkirstine@firstbasesolutions.com>; +Cc: pgsql-admin
On Fri, Jun 24, 2016 at 9:10 AM, Travis Kirstine <
tkirstine@firstbasesolutions.com> wrote:
>
> We were getting a lot of "max connections reached" errors and noticed that
> all our users are super users, after some digging I found the
>
> superuser_reserved_connections parameter was not set (default to 3) this
> lead to some additional questions:
>
>
> If all my users are superusers are they effectively limited to the number
> of connections defined in the
>
> superuser_reserved_connections parameter?
>
>
> Is there any significant difference between a superuser and non-superuser
> connection?
>
>
> Any additional insight would be great.
>
1. Superusers can use any connection - they are not limited to the three
connections reserved for them.
2. Having all users set to be superusers is generally bad (tm) for a myriad
of reasons only one of which is that when all connections slots are taken
you have no reserved slots to allow a superuser to connect and determine
what is running, kill queries, etc.
3. Max connections reached does not necessarily mean your database is
overloaded. We experienced a "thundering herd" issue. When new work arrives
in our system hundreds of workers can simultaneously start and generate a
handful of queries. Even though the queries themselves were each handled in
millisecond or even sub-millisecond time, connection-setup overhead
combined with X*max-connections clients trying to access the database
simultaneously occasionally caused between a few and nearly a hundred to
hit the max-connections limit. This condition typically occurred then
cleared within one second or so and was cured by the addition of pooling.
Cheers,
Steve
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-06-24 16:40 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-06-24 16:10 superuser_reserved_connections and max_connections Travis Kirstine <tkirstine@firstbasesolutions.com>
2016-06-24 16:26 ` David G. Johnston <david.g.johnston@gmail.com>
2016-06-24 16:27 ` John Scalia <jayknowsunix@gmail.com>
2016-06-24 16:40 ` Steve Crawford <scrawford@pinpointresearch.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