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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With