Re: superuser_reserved_connections and max_connections - Mailing list pgsql-admin

From Steve Crawford
Subject Re: superuser_reserved_connections and max_connections
Date
Msg-id CAEfWYyzk=EsgneZ-bAeVryEAi3HPjSVrwo9D2qG_r-k3ESwgyg@mail.gmail.com
Whole thread Raw
In response to superuser_reserved_connections and max_connections  (Travis Kirstine <tkirstine@firstbasesolutions.com>)
List 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

pgsql-admin by date:

Previous
From: John Scalia
Date:
Subject: Re: superuser_reserved_connections and max_connections
Next
From: Anonymous Anonymous
Date:
Subject: Need older (9.6~beta1-2.pgdg16.04+1) postgres beta packages