Re: [GENERAL] Re: GMT FATAL: remaining connection slots are reservedfor non-replication superuser connections, but I'm using pgBouncer forconnection pooling - Mailing list pgsql-general

On 02/25/2017 07:29 AM, lisandro wrote:
> Thanks for the quick answer.
>
> superuser_reserved_connections is set to 3
>
> Actually, it's not set (the line is commented) but the default
> for superuser_reserved_connections is 3:
> https://www.postgresql.org/docs/9.2/static/runtime-config-connection.html#GUC-SUPERUSER-RESERVED-CONNECTIONS

So much for that idea.

See more comments inline below.

>
> 2017-02-25 12:17 GMT-03:00 Adrian Klaver-4 [via PostgreSQL] <[hidden
> email] </user/SendEmail.jtp?type=node&node=5946255&i=0>>:
>
>     On 02/25/2017 04:19 AM, lisandro wrote:
>
>     > Hi there! Please tell me if this isn't the place to post my
>     question, I'm new
>     > in the list.
>     >
>     > I'm using PostgreSQL 9.3, I have around 150 databases, and I use
>     pgBouncer

Just to be clear all 150 databases are on on one Postgres
server/instance, correct?

>     > for connection pooling.
>     > My server is a VPS with 8cpus and 24gb of RAM.
>     >
>     > My current postgreSQL configuration (resumed) is this:
>     >
>     > listen_addresses = '*'
>     > port = 6543
>     > max_connections = 250
>     > shared_buffers = 2GB
>     > effective_cache_size = 6GB
>     > work_mem = 10485kB
>     > maintenance_work_mem = 512MB
>     > checkpoint_segments = 32
>     > checkpoint_completion_target = 0.7
>     > wal_buffers = 16MB
>     > default_statistics_target = 100
>     What is superuser_reserved_connections set to?
>
>     >
>     >
>     > In the other hand, my pgBouncer configuration (resumed) is this:
>     >
>     > listen_addr = localhost
>     > listen_port = 5432
>     > pool_mode = transaction
>     > server_reset_query = DISCARD ALL
>     > max_client_conn = 10000
>     > default_pool_size = 10
>     > min_pool_size = 2
>     > server_idle_timeout = 30
>     >
>     >
>     > However, for the last couple of months (total db number has been
>     increasing)
>     > I have these sporadic errors where pgbouncer can't connect to
>     postgresql.
>     > They occurr every day with variable frequency. Every time the
>     error appears,
>     > it does in a different database. Even in those where the activity
>     is almost

Well max_connections is server wide so the connection that exceeds that
could come from trying to connect any of the databases

>     > none.
>     >
>     > Every time the error is triggered, I check the total connections
>     number and
>     > it never goes beyond ~130.
>     > This is how I check, from psql:
>     > select count(*) from pg_stat_activity;
>     >
>     > Also I check for inactive connections with this:
>     > select count(*) from pg_stat_activity where (state = 'idle in
>     transaction')
>     > and xact_start is not null;
>     > ... but this number is always low, ~8 idle connections.

The question is are you looking at a reality that is different then the
one that triggered the FATAL message?

The message is saying at some point the connections are exceeding:

max_connections(250) - superuser_reserved_connections(3) = 247

I would believe Postgres is correct on that, so it is a matter of
finding out what is triggering the message.

Have you logged into the pgBouncer Admin to see what it reports:
http://pgbouncer.github.io/usage.html
Admin console

Are the logs below following the same event?

I ask because the timestamps differ by ~1 minute.


>     >
>     >
>     >
>     > When the error triggers, I check the postgresql log and I see this:
>     >
>     > 2017-02-25 09:13:31 GMT FATAL:  remaining connection slots are
>     reserved for
>     > non-replication superuser connections
>     > 2017-02-25 09:13:31 GMT FATAL:  remaining connection slots are
>     reserved for
>     > non-replication superuser connections
>     > 2017-02-25 09:13:31 GMT FATAL:  remaining connection slots are
>     reserved for
>     > non-replication superuser connections
>     > 2017-02-25 09:13:46 GMT FATAL:  remaining connection slots are
>     reserved for
>     > non-replication superuser connections
>     > 2017-02-25 09:13:46 GMT FATAL:  remaining connection slots are
>     reserved for
>     > non-replication superuser connections
>     > 2017-02-25 09:13:46 GMT FATAL:  remaining connection slots are
>     reserved for
>     > non-replication superuser connections
>     > 2017-02-25 09:13:47 GMT FATAL:  remaining connection slots are
>     reserved for
>     > non-replication superuser connections
>     > 2017-02-25 09:13:48 GMT FATAL:  remaining connection slots are
>     reserved for
>     > non-replication superuser connections
>     > 2017-02-25 09:13:49 GMT FATAL:  remaining connection slots are
>     reserved for
>     > non-replication superuser connections
>     >
>     >
>     >
>     > And if I check the pgbouncer log I see this:
>     >
>     > 2017-02-25 09:12:37.354 4080 LOG Stats: 24 req/s, in 387979 b/s,
>     out 2657772
>     > b/s,query 146363 us
>     > 2017-02-25 09:13:37.355 4080 LOG Stats: 23 req/s, in 382191 b/s,
>     out 2594329
>     > b/s,query 144827 us
>     > 2017-02-25 09:14:29.687 4080 ERROR S: login failed: FATAL: remaining
>     > connection slots are reserved for non-replication superuser
>     connections
>     > 2017-02-25 09:14:37.355 4080 LOG Stats: 28 req/s, in 383614 b/s,
>     out 2596947
>     > b/s,query 124098 us
>     > 2017-02-25 09:14:44.985 4080 ERROR S: login failed: FATAL: remaining
>     > connection slots are reserved for non-replication superuser
>     connections
>     > 2017-02-25 09:14:46.290 4080 ERROR S: login failed: FATAL: remaining
>     > connection slots are reserved for non-replication superuser
>     connections
>     > 2017-02-25 09:15:37.355 4080 LOG Stats: 26 req/s, in 378113 b/s,
>     out 2717657
>     > b/s,query 164167 us
>     >
>     >
>     > What am I missing? I will appreciate any tip or suggestion.
>     > Thanks in advance!
>     >
>     >
>     >
>     > --
>     > View this message in context:
>
http://www.postgresql-archive.org/GMT-FATAL-remaining-connection-slots-are-reserved-for-non-replication-superuser-connections-but-I-m-g-tp5946245.html
>
<http://www.postgresql-archive.org/GMT-FATAL-remaining-connection-slots-are-reserved-for-non-replication-superuser-connections-but-I-m-g-tp5946245.html>
>     > Sent from the PostgreSQL - general mailing list archive at
>     Nabble.com.
>     >
>     >
>
>     --
>     Adrian Klaver
>     [hidden email] <http:///user/SendEmail.jtp?type=node&node=5946254&i=0>
>
>
>     --
>     Sent via pgsql-general mailing list ([hidden email]
>     <http:///user/SendEmail.jtp?type=node&node=5946254&i=1>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>     <http://www.postgresql.org/mailpref/pgsql-general>
>
>
>     ------------------------------------------------------------------------
>     If you reply to this email, your message will be added to the
>     discussion below:
>
http://www.postgresql-archive.org/GMT-FATAL-remaining-connection-slots-are-reserved-for-non-replication-superuser-connections-but-I-m-g-tp5946245p5946254.html
>
<http://www.postgresql-archive.org/GMT-FATAL-remaining-connection-slots-are-reserved-for-non-replication-superuser-connections-but-I-m-g-tp5946245p5946254.html>
>
>     To unsubscribe from GMT FATAL: remaining connection slots are
>     reserved for non-replication superuser connections, but I'm using
>     pgBouncer for connection pooling, click here.
>     NAML
>
<http://www.postgresql-archive.org/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
>
>
>
> ------------------------------------------------------------------------
> View this message in context: Re: GMT FATAL: remaining connection slots
> are reserved for non-replication superuser connections, but I'm using
> pgBouncer for connection pooling
>
<http://www.postgresql-archive.org/GMT-FATAL-remaining-connection-slots-are-reserved-for-non-replication-superuser-connections-but-I-m-g-tp5946245p5946255.html>
> Sent from the PostgreSQL - general mailing list archive
> <http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html> at
> Nabble.com.


--
Adrian Klaver
adrian.klaver@aklaver.com