[GENERAL] GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection 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 = 1 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling
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 2017-02-25 12:17 GMT-03:00 Adrian Klaver-4 [via PostgreSQL] < ml-node+s1045698n5946254...@n3.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 = 1 > > 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 &
[GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is this pgbouncer configuration suitable for a production environment with hundreds of databases?
Hi there! I'm using pgbouncer in front of a PostgreSQL 9.3 instance. I have hundreds of databases (almost 200 and counting). The clients connect to pgbouncer always with the same user (there is one only user). Currently I have *postgresql max_connections=200*. My pgbouncer configuration is this (I put only the directives regarding my question): [pgbouncer] pool_mode = transaction max_client_conn = 1 default_pool_size = 2 min_pool_size = 0 reserve_pool_size = 2 reserve_pool_timeout = 5 max_db_connections = 5 server_idle_timeout = 30 [databases] db1 = host=localhost port=6543 dbname=db1 db2... db3... Notice that I don't set a pool_size for every database, but instead I use the general default_pool_size. Our project is growing, and we are constantly adding new databases, so it's a matter of time that we reach the number of 300 or 400 databases. Most of the databases have low activity (websites with very few traffic). So, considering that we have set postgresql max_connections=200, then (correct me if I'm wrong) we would be reaching the postgresql limit of max_connections soon. The question is: can I set default_pool_size=0 to avoid reaching the postgres limit? Is that suitable? *My goal is to find a pgbouncer configuration that allows me to add databases without worring about reaching the postgresql max_connection=200 limit. How can I achieve that?* Thanks in advance! Regards, Lisandro. -- View this message in context: http://www.postgresql-archive.org/Is-this-pgbouncer-configuration-suitable-for-a-production-environment-with-hundreds-of-databases-tp5954479.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Is this pgbouncer configuration suitable for a production environment with hundreds of databases?
Thank you very much David for your quick reply, I understand better now. For now, I'll let default_pool_size=2 and I guess I will have to monitor the total number of databases and adjust configuration when needed, in order to avoid reaching the postgres max_connection limit. I think I can play a bit with some pgbouncer configuration directives, like pool_size (per database), server_idle_timeout, server_connect_timeout and idle_transaction_timeout. Thanks for the help! Regards, Lisandro. -- View this message in context: http://www.postgresql-archive.org/Is-this-pgbouncer-configuration-suitable-for-a-production-environment-with-hundreds-of-databases-tp5954479p5954499.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
