Re: superuser_reserved_connections and max_connections

On Fri, Jun 24, 2016 at 9:10 AM, Travis Kirstine <
tkirstine(at)firstbasesolutions(dot)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