Sidekiq getting ActiveRecord::ConnectionTimeoutError: could not obtain a connection from the pool-CodePudding

I am getting an ActiveRecord::ConnectionTimeoutError: could not obtain a connection from the pool exception for background jobs in Sidekiq

CONFIG

I have a PUMA web process and a SIDEKIQ process running on Heroku (2 hobby dynos) [A Rails app with background jobs]
In database.yml I have pool: 40 (in default and production)
In sidekiq.yml I have :concurrency: 7
In puma.rb I have max_threads_count = ENV.fetch("PUMA_MAX_THREADS") { 5 } and have set ENV["PUMA_MAX_THREADS"] = 5
I am using a Heroku pgsql hobby instance, which allows for 20 connections

EXPECTED BEHAVIOR

When the 7 Sidekiq workers are busy running jobs they should have enough available db connections.

Because:

Needed db connections:

5 for 5 PUMA threads
12: [7 5] for SIDEKIQ threads (7 workers 5 for redis? - not sure about reasoning behind that one)
TOTAL NEEDED: 17 [12 5]
TOTAL AVAILABLE: 20

ACTUAL BEHAVIOR

When the 7 Sidekiq workers are busy running jobs, 2 jobs fail and raise the ConnectionTimeOutError (always 2 jobs, so actual max concurrency is 5)

STUFF I NOTICED (MIGHT HELP):

In SIDEKIQ dashboard, Redis connections reach a maximum 10 (never higher) [I guess 5 threads 5]
In Heroku db, when enqueueing a lot of jobs, connections are always much lower than the 20 available (so no problem from the pgsql instance)

Any help or advice would be super appreciated :))

Thanks in advance!

UPDATE: Adding my database.yml file

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("DB_POOL") { 10 } %>

development:
  <<: *default
  database: tracker_app_development

test:
  <<: *default
  database: tracker_app_test

production:
  url: <%= ENV['DATABASE_URL'] %>
  pool: <%= ENV.fetch("DB_POOL") { 10 } %>

web: DB_POOL=$PUMA_MAX_THREADS bundle exec puma -C config/puma.rb
worker: DB_POOL=14 bundle exec sidekiq -C config/sidekiq.yml
release: rake db:migrate

CodePudding user response：

This exception is being raised from the ActiveRecord::ConnectionAdapters::ConnectionPool::Queue class in Rails, specifically in the poll method of the class, which accepts a timeout period (defaults to 5s). This is how the error is being raised:

if elapsed >= timeout
  msg = "could not obtain a connection from the pool within %0.3f seconds (waited %0.3f seconds); all pooled connections were in use" %
    [timeout, elapsed]
  raise ConnectionTimeoutError, msg
end

I think this is saying that if the time elapsed since it has tried to acquire a connection is greater than the timeout provided (default 5s) then it will raise this exception. This is happening because the number of available connections from the pool is 10, while in Sidekiq you have mentioned 14 as the default pool size. Try to increase the pool size of your web dyno to more than or equal to the number of default pool connections specified in your Sidekiq dyno. Hopefully, this resolves this exception.

If this does not work, then you can try increasing the checkout_timeout from 5s to a longer duration like so:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("DB_POOL") { 10 } %>
  checkout_timeout: 10

development:
  <<: *default
  database: tracker_app_development

test:
  <<: *default
  database: tracker_app_test

production:
  url: <%= ENV['DATABASE_URL'] %>
  pool: <%= ENV.fetch("DB_POOL") { 10 } %>

This is what the API documentation for Rails has to say about ConnectionPools.

https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/ConnectionPool.html

CodePudding user response：

SOLUTION FOUND:

In my database.yml file, the production config had 1 indentation while it should have had 0...