Rails, Puma, Sidekiq how to calculate total DB connections?

Question

I am getting ActiveRecord::ConnectionTimeoutError once or twice in a day. could someone help me in calculating how many connections my application is making to my DB? and suggestion to optimise my connections?

Here is my configuration

AWS

Database : Mysql   
Version  : 5.7.23   
Provider : AWS RDS (db.m5.large, vCPU: 2, RAM: 8GB)

3 servers with bellow configurations

# database.yml
  pool: 20

# puma.rb 
  RAILS_MAX_THREADS : 5
  WEB_CONCURRENCY   : 2

1 sidekiq server with bellow configuration

# sidekiq 
  concurrency:  25

I tried to get max number of connection my DB is able to handle

# MySQL Max connections ("show global variables like 'max_connections';")
  624

user_7654691 · Answer

The total number of connections to a database equals the number of connections per server times the number of servers.

Total DB Connections = Connections per server * server count.

Connections per server = AR Database Pool Size * Processes per server (usually set with WEB_CONCURRENCY or SIDEKIQ_COUNT)

So for the web servers you have:

AR Database Pool Size = 20

Processes per server = 2

Server Count = 3

Total DB Connections(Web Server) = 20 * 2 * 3 = 120

The for the sidekiq server:

AR Database Pool Size = 20

Processes per server = 1

Server Count = 1

Total DB Connections(Sidekiq Server) = 20 * 1 * 1 = 20

So the total expected DB connections should be 140, which is way below the limit of the RDS instance.

My guess is that you are getting the ActiveRecord::ConnectionTimeoutError because your Sidekiq concurrency setting is higher than the AR connection pool value. All of the Sidekiq threads need an ActiveRecord database connection, so setting the AR pool size to a number smaller than Sidekiq's concurrency means some Sidekiq threads will become blocked waiting for a free database connection. In your case, at some point in time you might have 25 threads trying to access the database through a database pool that can use at most 20 connections and if a thread can't get a free database connection within 5 seconds, you get a connection timeout error.

In Sidekiq the total DB connections should be

minimum(Threads That Need a Database Connection, AR Database Pool Size) * Processes per Server (WEB_CONCURRENCY or SIDEKIQ_COUNT) * Server Count.

Additionally the Sidekiq documentation states that

Starting in Rails 5, RAILS_MAX_THREADS can be used to configure Rails and Sidekiq concurrency. Note that ActiveRecord has a connection pool which needs to be properly configured in config/database.yml to work well with heavy concurrency. Set pool equal to the number of threads pool: <%= ENV['RAILS_MAX_THREADS'] || 10 %>

Most of this answer is based on the Sidekiq in Practice email series from Nate Berkopec

Rails, Puma, Sidekiq how to calculate total DB connections?

1 Answers