1

We recently introduce PGBouncer into our stack as we were exhausting our connections to our RDS instance. Upon doing so we started to see all sorts of connection exceptions which I posted below. The only thing of note is that we use multiple databases via Rails built in multi-db support. Only the primary/writer instance is going through PGBouncer at the moment and that is where we are seeing all of the exceptions, the reader connections seem to be fine.

I'm wondering if we need to fine tune some of the timeout or connection sizes a bit or what else could be causing this.

Exceptions

ActiveRecord::StatementInvalid: PG::ConnectionBad: PQconsumeInput() server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

ActiveRecord::ConnectionNotEstablished: connection to server at "{db server IP}", port 5432 failed: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing

ActiveRecord::StatementInvalid: PG::ConnectionBad: PQsocket() can't get socket descriptor

PGBouncer Config

We're running quite a few smaller instances of PGBouncer since it is single process and I believe single threaded as well. We plan to fine tune this a bit later.

[databases]
production = our_connection_string

[pgbouncer]
max_client_conn = 500
pool_mode = transaction
default_pool_size = 200
server_idle_timeout = 30
reserve_pool_size = 0

Rails DB Config

default: &default
  adapter: postgis
  postgis_extension: true
  encoding: unicode
  pool: <%= ENV['DB_POOL'] || ENV['RAILS_MAX_THREADS'] || 5 %>
  idle_timeout: 300
  checkout_timeout: 5
  schema_search_path: public, tiger
  prepared_statements: false
  
production:
  primary:
    <<: *default
    url: <%= ENV[DATABASE_URL] %>
  primary_replica:
    <<: *default
    url: <%= ENV[DATABASE_REPLICA_URL] %>

Update 1

We attempted going with the default value for server_idle_timeout of 600 seconds and that doesn't seem to have made a difference.

CWitty
  • 4,488
  • 3
  • 23
  • 40
  • You have shown errors from the client perspective. What does the pgbouncer log say? What does the database log say? – jjanes Feb 16 '23 at 19:21
  • Do you really need transaction pooling mode? It is much more fragile than session pooling. – jjanes Feb 16 '23 at 19:24
  • Have you tried increasing the timeout, server_idle_timeout = 30. may be the connections are taking more time to respond. what is the pool size of db connections this can support, in case all thread are occupied and busy. – Bijendra Feb 16 '23 at 20:01
  • @jjanes I'm not entirely sure if we need transaction vs session pooling. We have a lot of clients connecting to the DB and it sounds like you can scale transaction pooling much easier than session pooling as session pooling is still 1:1 for required connections. The PGBouncer logs mostly say that the client requested to close the connection. I didn't see any errors at all. – CWitty Feb 16 '23 at 20:19
  • @Bijendra we just tested going with the default value (600 seconds) and it doesn't seem to have made a difference. – CWitty Feb 16 '23 at 20:20
  • I did notice that there are quite a few server disconnects due to connection age and then login attempts followed by new server connections. – CWitty Feb 16 '23 at 20:33

0 Answers0