The Ruby on Rails database connection pool

Photo by Lesly Juarez on Unsplash

Maybe as a Rails coder you have already experienced error messages such as

could not obtain a connection from the pool within 5.000 seconds (waited 5.002 seconds); all pooled connections were in use (ActiveRecord::ConnectionTimeoutError)

that left you puzzled on how to fix it…

Well it happens to every Rails coder at some point and can give headaches. So I will try to summarize here the big outlines to help you fix this.

How many connections can my database handle ?

This is the first thing to know. It is usually not a limiting factor but it’s something to keep in mind as your Puma server(s) will eat on those connections.

• in development

If you work like me with a Postgres database, this is easy to know, the answer is here : https://www.postgresql.org/docs/current/runtime-config-connection.html

“The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb)”

You can obviously tweak this number of connections if your computer is powerful enough. (not covered here)

• in production

It is probably different for your production environment. I am hosted at Heroku and my “hobby” plan offers 20 connections.

my postgres plans at Heroku offers 20 connections

The next available plan “Standard 0” offers 120 connections.

What about my rails app ? The principles:

Well if you host like me through a PAAS service like Heroku, you have to allocate the database connections to your Heroku dynos.

Well let’s start with a quick tutorial of Heroku and the Dynos.

One dyno is basically a Virtual Machine that can run a single instance of your server (Puma in my case, if you run a different server, you may need to find appropriate information for it.)

Then, Puma (in “clustered mode”) run workers in order to serve your website to users. Each worker can run multiple threads: threads are the processes that can serve your app (concurrently).

The configuration of Puma is done in the following config file:

config/puma.rb

workers Integer(ENV[‘WEB_CONCURRENCY’] || 2)
threads_count = Integer(ENV[‘RAILS_MAX_THREADS’] || 5)
threads threads_count, threads_count

Workers are set through the environment variable WEB_CONCURRENCY and threads are set through the environment variable RAILS_MAX_THREADS

For our example, let’s say we have WEB_CONCURRENCY = 2 and RAILS_MAX_THREADS = 2

This would give the following schema :

disclaimer: author of SVG dinosaur illustration is Freepik

In the above situation we have 2 Puma workers and 2 threads (per worker). Each worker is a full version of your app, like a small server on its own, then your Rails connection pool apply to every worker of your Puma server instance.

The GVL

I don’t want to add too much complexity at this stage but increasing threads count may not increase concurrency in the same fashion (or arithmetically).
One thread can serve one user, but only one thread at a time can process Ruby code inside a specific worker . Then adding a lot of threads to a Puma worker may not increase that much your application capabilities.
Adding more workers though does increase the computational capabilities of your app… but Puma Workers require more resource than threads.
Please read here for more details : https://www.speedshop.co/2020/05/11/the-ruby-gvl-and-scaling.html

We then only need to count the number of threads per Puma worker to populate database.yml

production: 
 pool: 2

Each thread can handle a single request at a time. If a thread is getting requests from multiple users, it will answer requests but serve them sequentially. Because of this, each thread is assigned a single database connection, no more..

To know the number of connections taken at database level, we have to multiply the number of Puma workers by the number of threads… then 2*2 ie 4 here. These 4 connections are counted towards the total amout of connections your database can handle (20 as seen above)

Side note about this configuration real world: The above configuration of 2 workers and 2 threads should work without issue on a small Heroku dyno (such as hobby) and easily serve multiple simultaneous users with good performance (actually 4 users at a time, but if many more users connect at the same time, serving them should still look smooth). Provided the webserver is not delegated heavy tasks such as image versionning etc ...

2 heroku blog references:

Deploying Rails Applications with the Puma Web Server

Concurrency and Database Connections in Ruby with ActiveRecord

and also this stackoverflow thread :

https://stackoverflow.com/questions/24280743/what-is-the-difference-between-workers-and-threads-in-puma

config/database.yml

Because we know that the connection pool equates the number of threads we can set database.yml like this :

production:
pool: <%= ENV[‘RAILS_MAX_THREADS’] %>

The additional stuff that can complexify the above process

1. Sidekiq

Sidekiq is a process that execute some tasks in the background of your app. Sidekiq is run on an independent Heroku dyno: a worker dyno.

It is possible to set Sidekiq to perform multiple tasks concurrently.

sidekiq.yml

development:
 :concurrency: 5
production: 
 :concurrency: 2
:queues:
 — critical
 — default

As you can see here, in production, Sidekiq is set up to run 2 threads concurrently.

These threads may or may not make request to the database. In real world, these threads are very likely to make calls to the database : statistics gathering saved in database, sending emails to users in database, cleaning old images which attachment data is stored in database etc ..

Then it is safe to allow a single database connection to each of the concurrent Sidekiq threads.

And in the former part of this thread we have set environment variable RAILS_MAX_THREADS to 2.

Because Sidekiq uses the same configuration as the webserver, it will grab the same db pool as the webserver , which actually matches perfectly our Sidekiq needs here ..

Though now let’s say running 2 Sidekiq threads is not enough and leaves the background dyno permanently busy and not doing things at the pace you desire. Also the dyno would have the technical capacity of doing more simultaneous tasks. Then you feel it is a bit stupid to not use it to the tune it can potentially achieve ...

Let’s set Sidekiq concurency to 4 :

development:
 :concurrency: 5
production: 
 :concurrency: 4
:queues:
 — critical
 — default

Now we have a problem, because the number of available database connections is still 2 and basically exogenous to Sidekiq.

One solution is to make the number of Sidekiq processes an environment variable, let’s say envSIDEKIQ_CONCURRENCY and compare this new environment variable to envRAILS_MAX_THREADS :

sidekiq.yml

development:
 :concurrency: 5
production: 
 :concurrency: <%= ENV['SIDEKIQ_CONCURRENCY'] %>
:queues:
 — critical
 — default

database.yml

production: 
 <% if ENV['SIDEKIQ_CONCURRENCY'] > ENV['RAILS_MAX_THREADS'] %>
pool: <%= ENV['SIDEKIQ_CONCURRENCY'] %>
<% else %>
pool: <%= ENV['RAILS_MAX_THREADS'] %>
<% end %>

Though now let’s say SIDEKIQ_CNCURRENY is 4 and we are still running two threads per Puma worker. The db pool will be 4 in order to serve Sidekiq gracefully. But we have 2 extra database connections that we don’t use per worker at web dyno level.

web dyno : 2*4 = 8 connections

background dyno: 4 connections

With these two dynos we eat 12 connections from the 20 total connections of the database. And 4 of these connections may never be used…

So it is always delicate to make a good setup in order to feed your Puma threads and Sidekiq instances properly but also effectively.

Here is a github thread for those interested about Sidekiq configuration: https://github.com/mperham/sidekiq/issues/2985

Also one cool thing to do is to have Sidekiq use the Rails max threads value and boot as many Sidekiq instances until you have reached the desired concurrency.

For example my Foreman Procfile looks like this at the moment:

sidekiq1: bundle exec sidekiq
sidekiq2: bundle exec sidekiq
web: rails s -p 3000
webpack: bin/webpack-dev-server

I have 2 Puma workers and 3 threads per worker. As Sidekiq uses Puma max threads value, Sidekiq instances have also a concurrency of 3. And because I have started two of them. The total concurrency is 6 and no database connection is wasted. (Also this technique is mostly good for home or bare metal servers as more Dynos would be required at Heroku in this case)

Another solution to not waste database connection is to start Sidekiq with a specific environment variable at the procfile level and then you can discriminate between app workers and async workers. Just check for the presence of this environment variable (that could be named SIDEKIQ_APP=true or something similar) and match Pool with the Sidekiq concurrency in the database.yaml file, without having to compare to the number of threads per Puma worker.
I have not tried this solution myself but it should work fine. Let me know your experience with it as it may be the best solution. Yet your app is not “idempotent”, the same app cannot be booted exactly the same for Puma and Sidekiq. If you forget the environment variable for the back end app, Sidekiq may silently start but break later.

2. Ruby threads

One more things to factor in, is the ability of a Puma thread to make more calls to the database than anticipated.

If you remember the above, a Puma thread can serve a single user at a time. So if a Puma thread receive 2 internet requests at the same time, it will serve them one after the other. The database cannot choke.

Well …. not if you use Ruby threads. Ruby threads are pieces of code that are executed on top of your application (or should I say alongside your application). A Ruby thread block is entirely removed from your app flow.

My database actually choked when I implemented the below code :

@imagetodestroy = Photo.find_by(hashed_id: params[:hashed_id])
 
 Thread.new do 
 @imagetodestroy.destroy
 end

Why using Ruby threads ?

Well in order to make my website snappier and more responsive I had delegated the destruction of an image to a Ruby thread. (my app got much snappier in the process !! )

Destroying an image in my case takes around 500ms (because, for each image, there are multiple variants stored, then multiple files to delete at S3). The view was then rendered in about 600ms.

When delegating to a ruby thread my view was rendered in about 100ms. It was a huge improvement. When making the above call AJAX, my app went even smoother !!

Though one major problem : because the Ruby thread works beside your normal app, if the user click very fast on a new image to destroy it, two threads blocks can end up making calls to the database at the same time ! And your database then choke !

This is a problem that can be easily seen if you run a single Puma thread per Worker. If you run 2 puma threads or more, then you have at least 2 database connections available, then there is a chance that the database connection dedicated to another Puma thread is idle and your app can use it while it is not in use.

One solution is to add one or two more connections to the db pool in order to give a bit of breathing to your app (if you happen to use those Ruby threads).

production:
pool: <%= ENV[‘RAILS_MAX_THREADS’].to_i + 2 %>

Though one advice regarding those threads blocks : never instantiate them in a loop ! You can choke database in milliseconds.

Also this example is not a good design. What would be better real world, is to have a column in the database that helps marking an image as live or destroyed. It is much faster to update a column than deleting remote images. Then a Sidekiq worker could later do the cleaning depending on that column value.

Conclusion

It’s not very complicate to deal with the database connections but you have to have a good snapshot of your code in mind, and also a clear vision of the design of your app, with a knowledge of what could make calls to the database. Whether code, gems …

Once you know that and have the basic knowledge of how it works, it’s easy to tweak database.yml in order to have everything working gracefully.

Best