Quick Tip: Fix ActiveRecord Connection Pool Errors For Good

Adam McCrea

Adam McCrea

@adamlogic

You've almost certainly seen this error, perhaps more times than you can count.

Screenshot of ActiveRecord::ConnectionTimeoutError

In this quick tip article, we'll dissect what causes the error and what you can do to fix it.

What causes an ActiveRecord::ConnectionTimeoutError?

The key word in the error message is "pool":

could not obtain a connection from the pool within 5.000 seconds; all pooled connections were in use

Each Rails process maintains a pool of database connections. Why not just one connection? Because you're probably running multiple threads in each Rails process, and you don't want those threads contending for a single database connection.

The ConnectionTimeoutError is saying the pool doesn't have enough connections. The error has nothing to do with your database. It's a Rails configuration issue.

You generally want your connection pool equal to the number of Rails threads.

How do you ensure an optimal ActiveRecord connection pool size?

The pool size is specified in database.yml, which looks like this by default:

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # https://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

This means your connection pool is equal to the RAILS_MAX_THREADS environment variable. This is a good thing, because the default puma.rb uses the same environment variable to specify the number of threads:

max_threads_count = ENV.fetch("RAILS_MAX_THREADS") { 5 }
min_threads_count = ENV.fetch("RAILS_MIN_THREADS") { max_threads_count }
threads min_threads_count, max_threads_count

As long as you haven't changed those files, you're golden—at least for your Rails web processes. But what about worker processes?

Understanding Sidekiq concurrency

The ActiveRecord::ConnectionTimeoutError most often occurs in a worker process, and the culprit is Sidekiq concurrency. You can set Sidekiq concurrency in a few ways:

If you do none of the above, concurrency defaults to 5 threads.

So what does "concurrency" really mean here? Sidekiq concurrency is the number of threads in your Sidekiq (Rails) process. And remember from above that we need our connection pool to have enough connections for all of our Rails threads.

If you're seeing ActiveRecord::ConnectionTimeoutError, it's very likely you've specified a Sidekiq concurrency that's larger than your connection pool.

Fixing your database connection pool for Sidekiq

You probably already saw it. Since the Rails defaults already use RAILS_MAX_THREADS for both the database pool and Puma threads, you can keep everything in sync by using that environment variable for Sidekiq concurrency as well.

Don't specify concurrency in a sidekiq.yml file or via the -c command line option. Just use RAILS_MAX_THREADS, and you won't see this error.

What if you want to configure Sidekiq and Puma separately?

The RAILS_MAX_THREADS approach has a significant limitation: It assumes you always want Puma and Sidekiq running the same number of threads. This is a fine way to start out, but it's too constraining when you're trying to optimize your server resources.

The trick is to dynamically override RAILS_MAX_THREADS at runtime. Here's an example Procfile:

web: bundle exec rails s
worker: RAILS_MAX_THREADS=${WORKER_THREADS:-${RAILS_MAX_THREADS}} bundle exec sidekiq

When the worker process is started, RAILS_MAX_THREADS is set dynamically using shell parameter expansion. If WORKER_THREADS is available, that value is used, otherwise, it falls back to the current value of RAILS_MAX_THREADS.

This approach is the best of both worlds. You can start out just using RAILS_MAX_THREADS, and that value will be used for your database pool, Puma threads, and Sidekiq concurrency. When you want to tweak your Sidekiq concurrency independently, you can set WORKER_THREADS, and your database pool will be kept in sync.

If you instead decided to adjust Sidekiq concurrency via sidekiq.yml or the -c CLI flag, your database pool and concurrency would be out of sync. And that's probably why you're here. 😉