Postgres Pro
Downloads
Home   >   mailing lists

Thread: superuser_reserved_connections and max_connections

superuser_reserved_connections and max_connections

From
Travis Kirstine
Date:


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.

Re: superuser_reserved_connections and max_connections

From
"David G. Johnston"
Date:
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.


David J.

Re: superuser_reserved_connections and max_connections

From
John Scalia
Date:
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.


Re: superuser_reserved_connections and max_connections

From
Steve Crawford
Date:


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