Re: [GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling
On Tue, Feb 28, 2017 at 10:00 AM, Lisandro 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling
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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling
Lisandro 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling
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] >: 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 = 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 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, o
