Search Postgresql Archives

GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux