Обсуждение: [GENERAL] GMT FATAL: remaining connection slots are reserved fornon-replication superuser connections, but I'm using pgBouncer forconnection pooling

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
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


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
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.



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
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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
> 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
> 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.
>
>
>
> 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
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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

Thank you Adrian.

Yes, I confirm that all the databases are running in one PostgreSQL
server/instance. I'm running this version:
PostgreSQL 9.3.15 on i686-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.4-2ubuntu1~14.04.3) 4.8.4, 32-bit

Let me ask: is there a way to monitor the total connections to postgresql
through time? Or should I make my own script for that? I ask because every
time the error is thrown, I check the total connections with "select
count(*) from pg_stat_activity;" but the number is always far from the
configured max_connections.


Maybe the problem is with pgBouncer, so I'll post this issue in their forum.
I'm not really a sysop, so it's hard for me to fully understand the issue.
The strange part is that the error appears in random hours, I mean, it
doesn't correspond with the hours of higher db activity. Instead, the error
appears in both scenarios: with high db activity and with very low db
activity.


For example, early this morning in postgresql log:
2017-02-28 06:26:33 GMT FATAL:  remaining connection slots are reserved for
non-replication superuser connections
2017-02-28 06:26:48 GMT FATAL:  remaining connection slots are reserved for
non-replication superuser connections
2017-02-28 06:26:50 GMT FATAL:  remaining connection slots are reserved for
non-replication superuser connections
2017-02-28 06:26:50 GMT FATAL:  remaining connection slots are reserved for
non-replication superuser connections

And in pgbouncer log:
2017-02-28 06:26:39.035 4080 LOG Stats: 7 req/s, in 322589 b/s, out 2281293
b/s,query 307926 us
2017-02-28 06:27:31.510 4080 ERROR S: login failed: FATAL: remaining
connection slots are reserved for non-replication superuser connections
2017-02-28 06:27:32.511 4080 ERROR S: login failed: FATAL: remaining
connection slots are reserved for non-replication superuser connections
2017-02-28 06:27:32.511 4080 ERROR S: login failed: FATAL: remaining
connection slots are reserved for non-replication superuser connections
2017-02-28 06:27:32.514 4080 ERROR S: login failed: FATAL: remaining
connection slots are reserved for non-replication superuser connections
2017-02-28 06:27:39.036 4080 LOG Stats: 10 req/s, in 334173 b/s, out 2187475
b/s,query 220037 us
2017-02-28 06:28:39.036 4080 LOG Stats: 7 req/s, in 335683 b/s, out 2287722
b/s,query 370778 us
2017-02-28 06:28:46.595 4080 WARNING C-0x8d56390:
radionew/medios@127.0.0.1:40910 Pooler Error: pgbouncer cannot connect to
server
2017-02-28 06:29:39.037 4080 LOG Stats: 7 req/s, in 275963 b/s, out 1976669
b/s,query 261484 us


Anyway, I'll post a thread in the pgBouncer forum.
I'll search for some tool to monitor the total number of postgresql
connections through time.

Any comment or suggestion will be appreciated.
Thank you very much for your time!

Best regards,
Lisandro.



--
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-tp5946245p5946775.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Lisandro <rostagnolisandro@gmail.com> writes:
> Let me ask: is there a way to monitor the total connections to postgresql
> through time? Or should I make my own script for that? I ask because every
> time the error is thrown, I check the total connections with "select
> count(*) from pg_stat_activity;" but the number is always far from the
> configured max_connections.

You could enable log_connections and log_disconnections.

            regards, tom lane

On 02/28/2017 06:01 AM, Lisandro wrote:
> Thank you Adrian.
>
> Yes, I confirm that all the databases are running in one PostgreSQL
> server/instance. I'm running this version:
> PostgreSQL 9.3.15 on i686-pc-linux-gnu, compiled by gcc (Ubuntu
> 4.8.4-2ubuntu1~14.04.3) 4.8.4, 32-bit
>
> Let me ask: is there a way to monitor the total connections to postgresql
> through time? Or should I make my own script for that? I ask because every
> time the error is thrown, I check the total connections with "select
> count(*) from pg_stat_activity;" but the number is always far from the
> configured max_connections.

I have not used any as my needs are fairly simple. All I can do is point
you at:

https://wiki.postgresql.org/wiki/Monitoring

Hopefully someone with more experience with this can help you out.


A question I forgot to ask previously:

Is there any app/client that uses the server that is not going through
pgBouncer?

>
>
> Maybe the problem is with pgBouncer, so I'll post this issue in their forum.
> I'm not really a sysop, so it's hard for me to fully understand the issue.
> The strange part is that the error appears in random hours, I mean, it
> doesn't correspond with the hours of higher db activity. Instead, the error
> appears in both scenarios: with high db activity and with very low db
> activity.





--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Steve, thanks for your help.
Your comment made me realise that maybe the problem is my pgBouncer
configuration, specifically default_pool_size. It took me a while to
understand pgbouncer, and I still had some doubts when I configured it.  Now
I undesrtand better.

I connect to all databases with the same user. However, I had set
default_pool_size=10. So with more than 150 databases, it was very probable
that postgresql reach max_connections=250 limit.

I didn't have set reserve_pool_timeout or max_db_connections, but docs say
their default values are reserve_pool_timeout=5 seconds,
max_db_connections=unlimited.

I've reviewed pgBouncer configuration and did some search. I've found this
thread where the first person that responds gives a great explanation abount
how pgbouncer do its maths:
https://github.com/pgbouncer/pgbouncer/issues/174

So, what I did for now was to set this in pgbouncer configuration:
default_pool_size=1
min_pool_size = 0
server_idle_timeout = 30
reserve_pool_size = 10
reserve_pool_timeout = 5
max_db_connections = 5


I'll keep monitoring with this setup, but I can already tell you that the
total number of connections in postgres has significantly reduced (from ~100
to ~60). So I guess the problem was a bad setup of pgbouncer.

Thank you all for your help!



--
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-tp5946245p5946827.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Tue, Feb 28, 2017 at 10:00 AM, Lisandro <rostagnolisandro@gmail.com> wrote:
> Hi Steve, thanks for your help.
> Your comment made me realise that maybe the problem is my pgBouncer
> configuration, specifically default_pool_size. It took me a while to
> understand pgbouncer, and I still had some doubts when I configured it.  Now
> I undesrtand better.
>
> I connect to all databases with the same user. However, I had set
> default_pool_size=10. So with more than 150 databases, it was very probable
> that postgresql reach max_connections=250 limit.
>
> I didn't have set reserve_pool_timeout or max_db_connections, but docs say
> their default values are reserve_pool_timeout=5 seconds,
> max_db_connections=unlimited.
>
> I've reviewed pgBouncer configuration and did some search. I've found this
> thread where the first person that responds gives a great explanation abount
> how pgbouncer do its maths:
> https://github.com/pgbouncer/pgbouncer/issues/174
>
> So, what I did for now was to set this in pgbouncer configuration:
> default_pool_size=1
> min_pool_size = 0
> server_idle_timeout = 30
> reserve_pool_size = 10
> reserve_pool_timeout = 5
> max_db_connections = 5
>
>
> I'll keep monitoring with this setup, but I can already tell you that the
> total number of connections in postgres has significantly reduced (from ~100
> to ~60). So I guess the problem was a bad setup of pgbouncer.

Those seem pretty reasonable. Note that if you need to you CAN set
default pool size and other settings per database etc. So if you have
a more active db that needs more connections etc you can adjust these
as needed per db and it will override the default overall settings.

As for monitoring I'd suggest setting up Nagios or Zabbix. They both
can give you some nice pretty graphs of what your system looks like
over time so you can do simple trend analysis and alerting to avoid
these problems in the future.